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 SMS
To complete this tutorial, you will need a Vonage 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 Vonage API Dashboard.
Vonage Developer Account
Vonage Virtual Number - Rent a number for your virtual agent
Google Account to access Google Sheets
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.
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.anymessage -
@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,
$messageClick Save & Exit.
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.
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 DataYour sheet will have 4 column headers:
namephone_numberbirthdaymessage
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 be15552345678Ensure your
phone_numbercolumn is formatted to “Plan text” or you will have problems with the data later.Ensure that
birthdayis formatted to be ofDatetype. E.g. 9/26/2008.
Plain Text Formatting in Google Sheets
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 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.
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:
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
X-Vgai-Key
Add your AI Studio API Key. You can find the
X-Vgai-Keyat the top right of your AI Studio canvas. Click on the "user" icon, and then "Generate API Key".
agent_id
Add the
idof 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!
The application should run daily to check if someone has a birthday. You can do this quite easily in Apps Script by:
In the left-hand panel, select "Triggers."
Click “Add Trigger”.
Choose the following settings:
Choose which function to run:
sendBirthdaySMSChoose which deployment should run:
HEADSelect event source:
Time-drivenSelect type of time-based trigger:
Day timerSelect time of day: decide when you want your messages to be sent
Click “Save.”
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.
){kind=small}
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.
