Make Phone Calls from Google Sheets
Published on August 8, 2024

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!

Prerequisites

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.

How to Create An Outbound Voice Chatbot

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 ArchitectureOverview of Bot Architecture

How to Create Custom Parameters in AI Studio

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.

How to Connect Your Virtual Agent to a Virtual Number

The final step is to connect your agent to a virtual number. This guide explains how to publish your agent.

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.

Simple Birthday Database in Google SheetsSimple Birthday Database in Google Sheets

Your sheet will have 4 column headers:

  1. name

  2. phone_number

  3. birthday

  4. 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 TextSelect Plain Text

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. 

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.

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 sendBirthdayCall() 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/telephony/make-call

For US agents --> https://studio-api-us.ai.vonage.com/telephony/make-call

  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) {
// 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! 

How to Create a Cron Job in Apps Script

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:

    1. Choose which function to run:sendBirthdayCall

    2. Choose which deployment should run:HEAD

    3. Select event source:Time-driven

    4. Select type of time based trigger:Day timer

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

  4. Click “Save”

Conclusion

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.

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.