This guide shows you how to quickly set up a Google Sheet to use to send scheduled SMS messages.
Twilio Account SID, Auth Token and phone number. You can find your Account SID and Auth Token when you first log-in to the Twilio Console here: https://www.twilio.com/console. You can purchase, or find your existing Twilio phone number here: https://www.twilio.com/console/phone-numbers/incoming.
A Google Apps account, so you can create a Google Sheet.
-
Create a new Google Sheet and Give it 4 Columns.
- "To Phone Number"
- "Message Body"
- "Status"
- "When"
Make sure the format of the cells is set to "Plain text", except for the "When" column. Make sure those cells are formatted using the format circled in the following image:
-
In the sheet menu select "Tools" then "< > Script editor". This will open a new browser tab for writing your script.
-
In the new browser tab select File -> New -> Script file. Name the script "app" and click the Ok button.
-
You now have a new script called
app.gs
on the left side menu. Click onapp.gs
if you are not automatically brought into that file. -
Copy the code here and past it into your
app.gs
file and save yourapp.gs
file. -
Add the Moments library to your script by going to Resources -> Libraries like shown here:
Copy this code: Mcun7NPepfBJFDW-iuQnbdo147xIduJpS
Paste the code into Add a Library and click the add, then Save button like in the following image:
The Moments library allows your script to access the timzone data in your Google Sheet's settings. You can change the Spreadsheet timezone by going back to the Spreadsheet, click "File" then "Spreadsheet settings..."
-
Time to add the necessary properties, so your code will work. Back on the browser tab where you've been writing your code click the "File" menu, then "Project properties", then click the "Script properties" tab in the window that pops up. This is where you will need your Twilio Account SID, Auth Token, and phone number. Plus you will be setting several other properties. Using the image below as your guide. Add the same exact properties you see in the image. Replace the blurred out parts with your specific information.
The
DateFormat
you should keep the same value that is in the image.The
spreadsheetUrl
is the URL back over in your spreadsheet browser tab. Copy the full URL except for anything after the word "edit". NOTE!: When you first run this script your spreadsheet should request edit access for the script, as if you're sharing editing rights with another user. You should allow this or the script won't work. -
Finally our last piece of configuration is to set a Trigger on your spreadsheet's code. This Trigger will tell your code when to run. Click "Edit", then "Current project's triggers" and a small window will open so you can confingure a time based Trigger. Following is an example image of a Trigger configuration:
Make sure you set "Run" to the
runApp
function, then configure the times to the settings of your choice.
In your spreadsheet use the "When" column to designate dates and times of when you would like a message to be sent. When your Trigger runs it will activate the code in our app.gs
script. The code will look at the current time of day in the timezone you set in your spreadsheet. It will compare the current time of day with the date and time in the "When" column. If the value of the "When" column is in the past, or equal to the current time then the message will be sent. Adjust the timing of your trigger to coincide with the frequency of the date and times in the "When" column.