Under the strange circumstances of the coronavirus we have seen some wonderful stories of communities getting together virtually to support each other. One way to support a local community is to set up a message group so that neighbours can easily offer or ask for help. Doing this with a regular group message could get messy as it's a pain to add or remove people from a group message.

In this post we'll see how to use Google Sheets, Twilio Functions, and Programmable SMS to administer a community SMS broadcast group. We will create a Google Sheet that lists the people in our group and a phone number that can be used to contact all of them by SMS. When you send an SMS to the number it will forward that message on to everyone else in the group.

If you want to skip straight to the end, check out the code and setup instructions in the community SMS broadcast repository on GitHub.

What you will need

If you want to build a community SMS group with Google Sheets and Twilio you will need the following:

Node.js
A Twilio account (if you don't have one, sign up for a new Twilio account here and receive $10 credit when you upgrade)
A Twilio number that can send and receive SMS messages
A Google developer account
A Google Sheet to work with

Setting up a service account for the Google Sheets API

To programmatically access Google Sheets, you’ll need to create a service account and credentials in the Google API Console. To do so, follow these steps:

Go to the Google APIs Console
Create a new project
Click Enable API. Search for and enable the Google Drive API
Create credentials for a Web Server to access Application Data
Name the service account and grant it a Project Role of Editor
Download the JSON file

We'll need this JSON file soon, so keep it handy.

Setting up the sheet

Your Google Sheet will store the numbers of everyone in the group along with their name and some other identifying information (like their house number so everyone knows who is who). Create a sheet with the headings "Number", "Name" and "House":

Fill in your own details under the headings. You should enter your number in e.164 format prefixed with an apostrophe ' so that Sheets doesn't try to treat it as a formula.

We need to give access to our service account to this sheet so we can use it through the API. Click the Share button and enter the client_email from the JSON file you downloaded earlier. We also need the sheet ID so that we can access it from the API. The ID is available in the URL of the sheet, the URL looks like this:

https://docs.google.com/spreadsheets/d/{GOOGLE_SPREADSHEET_KEY}/edit

Grab the GOOGLE_SPREADSHEET_KEY from your URL and keep it safe too.

Programming the phone number

When someone sends an SMS message to a Twilio phone number, Twilio makes a webhook request with all the details of the message to find out what to do next with it. In this case we need to look up all the numbers in our Google Sheet, and forward the message on to all of them. To perform this part we will use a Twilio Function. We'll build this next part using the Twilio Serverless Toolkit.

Let's start a new project. On the command line, run:

npm init twilio-function community-sms-broadcast --template forward-message

When prompted, enter your Twilio Account Sid and Auth Token from your console.

This uses the create-twilio-function project to scaffold a project using one of the premade function templates to give us a head start. The forward-message template has a function that forwards an incoming message to another number. We need to extend this to forward to multiple numbers from our Google Sheet.

Setting up our credentials

We collected our credentials for the Google Sheets API and our Sheet ID earlier and now we need to include them in our project. Starting with environment variables, open up .env and you should see your Twilio credentials. You'll also see a placeholder for MY_PHONE_NUMBER which we no longer need, so delete it.

Add one more variable to this; the Sheet ID.

GOOGLE_SPREADSHEET_KEY="YOUR_GOOGLE_SPREADSHEET_KEY"

Our credentials for connecting to the Google Sheets API came as a JSON file. We don't want to store JSON in the environment, so instead we can store it as a private asset. Grab the JSON file you downloaded earlier and move it to the assets directory. Rename it to credentials.private.json. Adding .private into the filename is a convention in the serverless toolkit that will ensure the file is a private asset when we deploy it to Twilio.

If you are planning to commit this project to version control, this is the time to add assets/credentials.private.json to your .gitignore file.

That's our credentials sorted, let's get on to building the function.

Setting up dependencies

We need one dependency to make accessing the Google Sheet API easy. Install it with:

npm install google-spreadsheet

Reading the sheet and forwarding on messages

Open up functions/forward-message.js, it should currently look like this:

exports.handler = function(context, event, callback) {
  let twiml = new Twilio.twiml.MessagingResponse();
  twiml.message(`From: ${event.From}. Body: ${event.Body}`, {
    to: context.MY_PHONE_NUMBER
  });
  callback(null, twiml);
};

This currently forwards an incoming message onto just one number, but we want to send it to our list of numbers from the sheet.

We'll start by requiring the google-spreadsheet dependency and loading the credentials from the private asset. At the top of the file add the following:

const { GoogleSpreadsheet } = require("google-spreadsheet");
const fs = require("fs");
const credentials = JSON.parse(
  fs.readFileSync(Runtime.getAssets()["/credentials.json"].path, "utf8")
);

Runtime.getAssets() loads all the assets from the project and that gives you access to the path of the JSON file with our credentials. We can then load the file using the Node.js fs module.

Moving on to the function, we'll start the same way by instantiating a new messaging response and finish by calling the callback function with that response. We just need to load the sheet and find all the numbers to forward the message to in between:

exports.handler = function(context, event, callback) {
  let twiml = new Twilio.twiml.MessagingResponse();
  // Load the sheet, etc
  callback(null, twiml);
};

We'll start by creating a Google Sheet document using the sheet ID we stored in the environment earlier. We get access to the environment variables through the context argument to our function.

exports.handler = function(context, event, callback) {
  let twiml = new Twilio.twiml.MessagingResponse();
  const doc = new GoogleSpreadsheet(context.GOOGLE_SPREADSHEET_KEY);

  callback(null, twiml);
};

Next we need to give this document access to the API using the credentials we loaded earlier and then load the basic properties of the document. These are both asynchronous calls, implemented as promises, so we can use async/await to simplify the flow here. To do so, we define our handler function as an async function and then use await in front of each asynchronous call.

exports.handler = async function(context, event, callback) {
  let twiml = new Twilio.twiml.MessagingResponse();
  const doc = new GoogleSpreadsheet(context.GOOGLE_SPREADSHEET_KEY);
  await doc.useServiceAccountAuth(credentials);
  await doc.loadInfo();

  callback(null, twiml);
};

With the whole document loaded, we can select the first worksheet and then load the rows of that sheet. Loading the rows is asynchronous as well, so we'll use await again.

exports.handler = async function(context, event, callback) {
  let twiml = new Twilio.twiml.MessagingResponse();
  const doc = new GoogleSpreadsheet(context.GOOGLE_SPREADSHEET_KEY);
  await doc.useServiceAccountAuth(credentials);
  await doc.loadInfo();
  const sheet = doc.sheetsByIndex[0];
  const rows = await sheet.getRows();

  callback(null, twiml);
};

With the rows loaded we can find out if the phone number that sent the incoming message is on our list. If it is, we'll forward to the other numbers on the list by filtering out the number that sent the message and building a TwiML <Message> for each remaining number. If the number isn't on the list we can reply with a message:

exports.handler = async function(context, event, callback) {
  let twiml = new Twilio.twiml.MessagingResponse();
  const doc = new GoogleSpreadsheet(context.GOOGLE_SPREADSHEET_KEY);
  await doc.useServiceAccountAuth(credentials);
  await doc.loadInfo();
  const sheet = doc.sheetsByIndex[0];
  const rows = await sheet.getRows();
  const from = rows.find((row) => row.Number === event.From);
  if (from) {
    rows
      .filter((row) => row.Number !== event.From)
      .forEach((row) => {
        twiml.message(
          `From: ${row.Name} (${row.House}).\nBody: ${event.Body}`,
          {
            to: row.Number,
          }
        );
      });
  } else {
        twiml.message("You're not currently on this community list. Please contact the community organiser to be added.");
  }
  callback(null, twiml);
};

Note how you can refer to the columns in the rows by the header of the row. We can use row.Number and row.Name instead of column numbers. Be careful though, if you change the headers in the sheet you will need to change them in the code too.

This is all the code we need, all that remains is to deploy the Function and hook it up to a phone number.

Deploying the function

Since we created the Function using the Twilio Serverless Toolkit, deployment is just one command in your terminal:

npm run deploy

When the script completes your deployment details will be logged to your terminal. Grab the Functions URL and head to the Twilio console to apply it to your phone number. Open the numbers console and edit the number you want to use for this (or buy one if you haven't yet). Scroll down to the Messaging section and enter the Function URL in the field marked "A message comes in".

Save the number and send it a message. Nothing should happen because your number is the only one on the list right now. Remove your number from the Google Sheet and send a message, you will receive a reply to say you're not on the list.

Ready to broadcast

Add the numbers and names for everyone you want to join this community broadcast system to your Google Sheet. Now when you send a message to your number it will broadcast out to everyone else on the list. If someone replies, you and everyone else will receive that reply too.

Group messaging is in your control

In this post we've seen how a Google Sheet, a Twilio phone number and a few lines of JavaScript could help to keep a community connected and informed. If you want to check out all of the code, take a look at the community-sms-broadcast repo here on GitHub.

If you're looking for other ways that communications can help during this pandemic, check out this pre-trained coronavirus Autopilot chat bot template, this tutorial on keeping up to date with the current statistics for the virus over SMS, and, if you need extra support for your contact centre, this program to support businesses with a Flex Boost.

Stay safe out there and feel free to hit me up on Twitter or over email if you have any questions.

Build a community SMS group chat with Twilio Functions and Google Sheets