Lately, I’ve been forgetting to call my friends on their birthdays. It’s not on purpose, it’s just that living half a world away, makes it difficult. But hey I work at a Communications API company, I can tech my way out of this problem!
Sure enough, I came up with the perfect solution! I created a Google Sheets spreadsheet with my beloved contacts, their birthdays, and heartfelt messages. And now my friends get silly robot birthday calls. Combining Google’s Apps Scripts and Vonage’s AI Studio to automatically send a robotic birthday message from Google Sheets. And it’s all with a low-code, drag-and-drop interface!
This integration is perfect for anyone who wants to make phone calls from a spreadsheet or make phone calls from a CSV but doesn't want to build a full-code solution.
In this tutorial, you’ll learn to send phone calls from Google Sheets and never miss another birthday again!
Vonage Developer Account
Vonage Virtual Number: Rent a number for your virtual agent
Google Sheets Account: Sign up for Google
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.
To create your agent, follow the instructions found in the AI Studio documentation. There are three important options for our agent, select:
Type: Telephony
Template: Start From Scratch
Event: Outbound
The functionality of your agent will be quite simple; a single SMS with your birthday well wishes. You will use a single Send Message Node and an End Conversation Node.
Overview of Bot Architecture
Inside your Send Message Node, you’ll need 2 parameters. So open the Properties panel on the left-hand side and choose parameters. Under Custom Parameterscreate the following entries:
name - @sys.any
message - @sys.any
Save the parameters and open your Speak Node. Inside the node, add the following message. Use your parameters by typing the $ followed by the parameter name. For instance, to use your name value you will type $name.
<speak><break time='1s' /> <p> Dear $name</p> <p>Your loving friend YOUR_NAME has the following birthday message for you: $message </p> <p>Have a wondeful birthday!</p></speak>You’ll notice that you are using Speech Synthesis Markup Language in order to be able to add a one-second pause and create different paragraphs for the agent to speak more naturally.
Click Save & Exit.
The final step is to connect your agent to a virtual number. This guide explains how to publish your agent.
Open Google Sheets and click Blank Spreadsheet. Give your spreadsheet a nice title like Birthday Messages. Also, name your sheet birthdays.
Simple Birthday Database in Google Sheets
Your sheet will have 4 column headers:
name
phone_number
birthday
message
Add 10 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 “Plain text” or you will have problems later with the data.
Ensure that birthday is formatted to be of Date type. E.g. 9/26/2008.
Select Plain Text
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.
First, give your new project a nice title like Birthday Messages API. Clear the code in code.gs and replace it with the following:
function sendBirthdayCall() {
// 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 sendBirthdayCall 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 which contains the birthday boy or girl'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 sendBirthdayCall() 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/telephony/make-call
For US agents --> https://studio-api-us.ai.vonage.com/telephony/make-call
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".
agent_id
Add the id of your Vonage AI Studio agent. This can be found under agent details.
function sendRequest(payload) {
// Replace with either the US or EU URL
var url = '';
// Define headers for the POST request
var headers = {
'Content-Type': 'application/json',
'X-Vgai-Key': '' // Replace with your key
};
// Define the body for the POST request
var body = {
"to": payload.phone_number,
"agent_id": "", // Replace with your agent ID
"session_parameters": [
{
"name": "name",
"value": payload.name
},
{
"name": "message",
"value": payload.message
}
]
};
// Make sure to set proper options for your API request (e.g., headers, authentication)
var options = {
'method': 'post',
'headers': headers,
'payload': JSON.stringify(body)
};
// Send the POST request
var response = UrlFetchApp.fetch(url, 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. And you pass along the values for 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 3 of the contacts have a birthday that is 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:sendBirthdayCall
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
Click “Save”
Now that you’ve learned how to call phone numbers from Google Sheets, what’s next?
Maybe you can send a birthday email with AI Studio’s Email Node. Maybe your friends have “telephobia”, so you could create an SMS agent failover. 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.
