Schedule Sending SMS from Google Sheet

Published in

46 thoughts

4 min readFeb 6, 2018

Previously we have written about how to receive SMS and store them in a Google Sheet as well as how to send SMS from a Google Spreadsheet. In this third part of the SMS & Google Sheets mini series we will cover how to schedule sending SMS from Google Spreadsheets.

Preparations

Set everything up according to the send SMS from Google Spreadsheet tutorial. What you will do there is create a 46elks account, create a Google Spreadsheet and copy/paste some code to send SMS. Once that is done you are ready to start scheduling SMS. To do that you need to define when each SMS should be sent and set up the schedule.

Let’s get into the details!

Define when each SMS should be sent

To be able to define when an SMS should be sent you first need to specify how you prefer to write dates. Do this by going into File > Project Properties in your script. Go to the Script properties tab and add a Property named DateFormat (written exactly like that, not dateformat or Dateformat). Set the value to your preferred date format, for example:

YYYY-MM-DD HH:mm (if you prefer 2017–12–25 13:37)
MM/DD/YYYY HH:mm (12/25/2017 13:37)
DD/MM/YYYY HH:mm (25/12/2017 13:37)

Save the Project Properties and return to your Google Sheet. Add a new column called When after the Info column. For each row write the date and time you want to send the message, use the date format you specified earlier.

Add Time Zone support

The time zone of your Google Sheet that will be used for all the dates and times. You can check your spreadsheets time zone in the Spreadsheet Settings (available via the File menu).

To be able to deal with time zones in the correct you need to use a library called Moment. Go back to the script and go to Resources > Libraries. Enter this key into “Add a library”:

Mcun7NPepfBJFDW-iuQnbdo147xIduJpS

Click add, pick version 2 and save.

Time for some code

Replace the code you have in your script with the following and save it:

This code gets the date format you specified, the spreadsheets time zone and when to send each SMS. It then sends the SMS if the date and time to send the message has already passed.

Try it yourself by changing the script to sendElksSMS and click Run.. If you have any rows in the spreadsheet where When is set to the past you should be receiving an SMS.

Schedule sending messages

Finally it is time to set it up so that the script runs on a schedule. Go to Edit > Current project’s triggers

Select “Add Trigger”

Ensure the following settings are selected:

Choose which function to run : sendElksSMS
Choose which deployment should run : Head
Select event source : Time-driven
Select type of time based trigger : Minutes timer
Select minute interval : Every 15 minutes (or as you wish 🚀)

Finally click Save. Every 15 minutes your script will run and check for SMS to send. To schedule new SMS just add a new row in your Spreadsheet and set the time you want it to be sent.

Let us know (help@46elks.com) if we can help you, if perhaps there is anything unclear in this tutorial, or if you’d like for us to write another one.

Please be advised that Google has a limitation on how long a script is allowed to run: “maximum execution time”. It depends on which kind of Google account you use, the lowest time allows a script to run for 6 minutes. If you plan to send or receive a large amount of SMS at the same time, please contact 46elks support on +46766861004 or help@46elks.com.

Also check out the previous parts of this a mini series on Google Spreadsheets and SMS:
Part 1: Receive SMS from Google Spreadsheet
Part 2: Send SMS from Google Spreadsheet

// 46elks team