Twilio Google Sheets Macro

receiveTwilio.js

// Constants
const TWILIO_ACCOUNT_SID = 'AC94f51c8f89430a6...'; // change me
const TWILIO_ACCOUNT_TOKEN = '426cee8c3...'; // change me
const TWILIO_PHONE = '+18005551234'; // change me
const NUMBER_TO_RETRIEVE = 1000;
const HOURS_OFFSET = 0;
const START_COLUMN = 2;
const START_ROW = 3;

/**
 * @return string
 */
function createAuthHeader() {
	const token = Utilities.base64Encode(`${TWILIO_ACCOUNT_SID}:${TWILIO_ACCOUNT_TOKEN}`);
	return `Basic ${token}`;
}

/**
 * @return array
 */
function fetchTwilioMessages() {
	const url = `https://api.twilio.com/2010-04-01/Accounts/${TWILIO_ACCOUNT_SID}/Messages.json?To=${TWILIO_PHONE}&PageSize=${NUMBER_TO_RETRIEVE}`;
	const options = {
		headers: { Authorization: createAuthHeader() },
		method: 'get',
	};

	try {
		const response = UrlFetchApp.fetch(url, options);
		return JSON.parse(response.getContentText()).messages;
	}
	catch (error) {
		console.error('Error fetching Twilio messages:', error);
		return [];
	}
}

/**
 * @param array messages
 * @return void
 */
function addMessagesToSpreadsheet(messages) {
	const sheet = SpreadsheetApp.getActiveSheet();
	let row = START_ROW;

	messages.forEach((message) => {
		let column = START_COLUMN;

		// Date and Time Processing
		const theDate = new Date(message.date_sent);

		// Skip invalid date
		if (isNaN(theDate.valueOf())) {
			column += 2;
		}
		else {
			theDate.setHours(theDate.getHours() + HOURS_OFFSET);

			// Set date
			sheet.getRange(row, column).setValue(theDate);
			column++;

			// Set time
			sheet.getRange(row, column).setValue(theDate);
			column++;
		}

		// Set sender's phone number
		sheet.getRange(row, column).setValue(message.from);
		column++;

		// Set message text
		sheet.getRange(row, column).setValue(message.body);
		row++;
	});
}

/**
 * @return void
 */
function receiveTwilio() {
	const messages = fetchTwilioMessages();

	if (messages.length > 0) {
		addMessagesToSpreadsheet(messages);
	}
}