How to Send SMS from Google Sheets
Published on August 1, 2024

I often forget to wish my beloved friends and family a Happy Birthday. It’s not that I mean to, but with a busy schedule and long distances separating us, it seems I forget to send a message on time more and more often.

Well, I just came up with the perfect solution! I created a Google Sheets spreadsheet with my beloved contacts, their birthdays, and heartfelt messages. Using Google Apps Scripts and Vonage’s AI Studio, I built an integration to automatically send SMS from Google Sheets. And it’s all low-code!

In this tutorial, you’ll learn to send SMS from Google Sheets and never miss another birthday again!

Example of Outbound SMSExample of Outbound SMS

DT API Account

To complete this tutorial, you will need a DT API account. If you don’t have one already, you can sign up today and start building with free credit. Once you have an account, you can find your API Key and API Secret at the top of the DT API Dashboard.

Prerequisites

How to Create An Outbound SMS Chatbot

To create your agent, follow the instructions found in the AI Studio documentation here. There are three important options for our agent:

  • Type: SMS

  • Template: Start From Scratch

  • Event: Outbound

Your agent's functionality will be quite simple: send a single SMS with your birthday well wishes. You will use a single Send Message Node and an End Conversation Node. You can find the message content in the next step.

How to Create Custom Parameters in AI Studio

Inside your Send Message Node, you’ll need two parameters. So open the Properties panel on the left-hand side and choose parameters. Under Custom Parameters, create the following entries:

  • name - @sys.any

  • message - @sys.any

Save the parameters and open your Send Message Node. Inside the node, add the following message. To use your parameters, type $ followed by the parameter name. For instance, to use your name value, type $name.

Dear $name,

$message

Click Save & Exit.

How to Connect Your Virtual Agent to a Virtual Number

The final step is to connect your agent to a virtual number and publish the application. This guide explains how to deploy your agent to be accessible on the public internet.

How to Create a Google Sheets Database

Open Google Sheets and click Blank Spreadsheet. Give your spreadsheet a nice title like Birthday Messages. Also, name your sheet birthdays as shown below.

Example of Google Sheet DataExample of Google Sheet DataYour sheet will have 4 column headers:

  1. name

  2. phone_number

  3. birthday

  4. message

Add ten friends' worth of data.

  • For phone_number, add your number or another phone number you can access to receive SMS for testing. Phone numbers should be in international format without any + or 00. For example, a US number would be 15552345678

  • Ensure your phone_number column is formatted to “Plan text” or you will have problems with the data later.

  • Ensure that birthday is formatted to be of Date type. E.g. 9/26/2008.

Plain Text Formatting in Google SheetsPlain Text Formatting in Google Sheets

How to Convert Your Google Sheet to JSON

Now, you will turn your Google Spreadsheet into a programmable app! Open the Extensions tab and select Apps Scripts. Apps Scripts allows you to write code on top of Google Workspace applications like Google Sheets or Google Docs to automate and extend their functionality.

Apps Script Tab in Google SheetsApps Script Tab in Google SheetsFirst, give your new project a nice title like Birthday Messages API. Clear the code in code.gs and replace it with the following:

function sendBirthdaySMS() {
 // Open the spreadsheet by name
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("birthdays");
  // Get today's date
 var today = new Date();
 var todayDay = today.getDate();
 var todayMonth = today.getMonth() + 1;
  // Get all the data from the sheet
 var data = sheet.getDataRange().getValues();
  // Iterate through each row to check for birthdays
 for (var i = 1; i < data.length; i++) {
   var name = data[i][0];
   var phoneNumber = data[i][1];
   var birthday = new Date(data[i][2]);
   var birthdayDay = birthday.getDate();
   var birthdayMonth = birthday.getMonth() + 1;
   var message = data[i][3];
  
   // Check if today is the person's birthday
   if (birthdayDay === todayDay && birthdayMonth === todayMonth) {
     // Prepare the JSON payload
     var payload = {
       "name": name,
       "phone_number": phoneNumber,
       "birthday": birthday.toISOString(),
       "message": message
     };
     const payload_string = JSON.stringify(payload)
 
     // Send POST request to AI Studio
     sendRequest(payload);
   }
 }
}

This code creates a sendBirthdaySMS function, which searches our spreadsheet for the birthdays sheet. It then iterates through each row, checking if today is the person’s birthday by comparing the value for day and month. If so, it creates the payload object that contains the person’s information. It then passes this information to the sendRequest function, which will send the birthday information to AI Studio.

How to Send an Apps Script POST Request

You will now create your sendRequest function to trigger your Outbound Virtual Agent. You can paste the following code below the sendBirthdaySMS() section.

To make this code work, you’ll need to update 3 values:

  1. aiStudioUrl 

  • The endpoint depends on the region you selected for your agent:

For EU agents → https://studio-api-eu.ai.vonage.com/messaging/conversation For US agents --> https://studio-api-us.ai.vonage.com/messaging/conversation

  1. X-Vgai-Key

  • Add your AI Studio API Key.  You can find the X-Vgai-Key at the top right of your AI Studio canvas. Click on the "user" icon, and then "Generate API Key".

  1. agent_id

  • Add the id of your Vonage AI Studio agent. This can be found under agent details.

function sendRequest(payload) {
 // Add your URL depending if using EU or US agent
 // check point #2 above for URL to add in the ''
 var aiStudioUrl = ''

  // Define headers for the POST request
 var headers = {
'Content-Type': 'application/json',
 	'X-Vgai-Key': '' // Replace with your AI Studio key
 };
  // Define the body for the POST request
 var body = {
   "to": payload.phone_number,
   "agent_id": "", // Replace with your agent ID
   "channel": "sms",
   "session_parameters": [
     {
       "name": "name",
       "value": payload.name
     },
     {
       "name": "message",
       "value": payload.message
     }
   ]
 };
  // Define the options for the POST request (e.g., headers, authentication)
 var options = {
   'method': 'post',
   'headers': headers,
   'payload': JSON.stringify(body)
 };

  // Send the POST request
 var response = UrlFetchApp.fetch(aiStudioUrl, options);

 // Add a two-second delay before sending the next request
 Utilities.sleep(2000);

  // Log the response (you can do more error handling here)
 Logger.log(response.getContentText());
}

So, what does this code do? It creates a POST request by preparing the URL, headers, and body. In the headers, the X-Vgai-Key ensures this agent belongs to you. In the body, you tell AI Studio which agent to trigger and to whom to send the messages. You pass along the values for the name and message in AI Studio, which you prepared previously. All this is bundled up in options, and using UrlFetchApp you make the request.

Recently, AI Studio added rate limits on requests. To accommodate, a 2-second delay is added between requests using Utilities.sleep(2000);

And that’s it! You can now test that your code works by clicking Run.

For testing, make sure that at least three of the contacts have a birthday on the current day and a phone number you can access.

How cool was that!

How to Create a Cron Job in Apps Script to Run Daily

The application should run daily to check if someone has a birthday. You can do this quite easily in Apps Script by:

  1. In the left-hand panel, select "Triggers."

  2. Click “Add Trigger”.

  3. Choose the following settings:

  4. Choose which function to run: sendBirthdaySMS

    • Choose which deployment should run: HEAD

    • Select event source: Time-driven

    • Select type of time-based trigger: Day timer

    • Select time of day: decide when you want your messages to be sent

  5. Click “Save.”

Conclusion

Now that you’ve connected your AI Studio SMS Agent with Google Sheets, what’s next?

Maybe you can send a birthday email using AI Studio’s Email Node. You could create a Voice agent to sing your friends a birthday song. Or you could use the Generative AI integration to send your friends a fun birthday pic.

Whatever you decide to do, we want to hear about it! Please reach out in our Developer Community Slack or on X, formerly known as Twitter.

Additional Resources

Benjamin AronovDeveloper Advocate

Benjamin Aronov is a developer advocate at Vonage. He is a proven community builder with a background in Ruby on Rails. Benjamin enjoys the beaches of Tel Aviv which he calls home. His Tel Aviv base allows him to meet and learn from some of the world's best startup founders. Outside of tech, Benjamin loves traveling the world in search of the perfect pain au chocolat.

Ready to start building?

Experience seamless connectivity, real-time messaging, and crystal-clear voice and video calls-all at your fingertips.