It is a simple yet powerful concept: retrieve data from your Google Sheet and send it via WhatsApp. And then get some data from a WhatsApp conversation and automatically enter it in a Google Sheet. Is your company looking for a quick and easy integration between WhatsApp and Google Sheets to improve customer experience? Look no further, this tutorial will show you how to do that.

In this tutorial, you will learn to use Vonage’s no-code/low-code platform, AI Studio, to build a WhatsApp chatbot that handles user product feedback.

Preview of WhatsApp Feedback Flow

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.

Ready to start building?

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

Prerequisites

• Vonage Developer Account

• Vonage Virtual Number - Rent a number for your virtual agent

• Google Sheets Account - Sign up for Google

How to Create a Google Sheets Database

Open Google Sheets and click Blank Spreadsheet. Give your spreadsheet a nice title like WhatsApp Product Feedback. Next, name your first sheet users. 

The users sheet will have 3 column headers:

1. phone_number

2. first_name

3. last_name

Add one user for testing with all three data fields complete.

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

Now add a second sheet called responses. The responses sheet will have 4 column headers:

1. phone_number

2. type

3. feature

4. feedback

How to Use Google Sheets as a REST API

Now you will turn your Google Spreadsheet into a REST API. 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 WhatsApp Product Feedback, and clear the code in the tab under code.gs.

How to Create a GET Request Endpoint for Google Sheets

You can add the following code in code.gs:

function json(sheetName, phoneNumber) {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = spreadsheet.getSheetByName(sheetName);
 const data = sheet.getDataRange().getValues();
  // Find the row corresponding to the given phone number
 const headers = data[0];
 const rowIndex = data.findIndex(row => row[headers.indexOf('phone_number')] === phoneNumber);
  // If the phone number is found, return the corresponding entry, otherwise return null
 if (rowIndex !== -1) {
   const rowData = data[rowIndex];
   const jsonData = convertToJson([headers, rowData]);
   return ContentService
          .createTextOutput(JSON.stringify(jsonData))
          .setMimeType(ContentService.MimeType.JSON);
 } else {
   return ContentService
          .createTextOutput(JSON.stringify({error: "Phone number not found."}))
          .setMimeType(ContentService.MimeType.JSON);
 }
}


function doGet(e) {
 const path = e.parameter.path;
 const phoneNumber = e.parameter.phone_number; // Extract phone number from URL parameter
 return json(path, phoneNumber);
}

But what does this code do? The first function creates our logic that will handle a sheet name, like users, and a phone_number, like your WhatsApp number. Then it will search the active spreadsheet to find this particular sheet. Inside that sheet, it finds the column called phone_number and then searches each row against the phone_number that is provided. If it finds a match, it then takes the data in that row and converts it to a JSON. 

The second function creates a GET endpoint and extracts the query parameter values of path and phone_number. Then it sends those values back to the first function and returns the JSON that it receives.

How to Create a POST Request Endpoint for Google Sheets

Below your GET request logic, you can now add the following:

function doPost(e) {
 // Parse incoming POST data
 const postData = JSON.parse(e.postData.contents);

 // Extract data from the POST request
 const phoneNumber = postData.phone_number;
 const type = postData.type;
 const feature = postData.feature;
 const feedback = postData.feedback;

 // Get the "responses" sheet
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = spreadsheet.getSheetByName("responses");

 // Append a new row with the data
 const newRow = [phoneNumber, type, feature, feedback];
 sheet.appendRow(newRow);

 // Return success response
 return ContentService.createTextOutput("Row added successfully.");
}

This creates a POST endpoint, which again extracts the information sent in the request. However, here the data is sent in the body so e.postData.contents handles that nicely. Once all the pieces of data have been extracted and the responses sheet loaded, a new row is created in the array newRow. It is then added with the appendRow method. Finally, a nice response message is sent so that we know in AI Studio our operation has succeeded. 

Now, you can deploy your web app and make it publicly exposed. Click on Deploy.

This will open a panel that asks you to “Please select a deployment type”. Click on the settings icon and select Web app. For “Execute as”, select yourself. And for “Who has access”, select anyone. Continue and click Authorize access, and continue to allow Google to authorize access to the project.

Finally, you will see a New deployment panel that includes a Web App URL. Save this URL for later. Finally, click Done.

How to Create an Inbound WhatsApp Chatbot

To create your no-code/low-code agent, follow the instructions found in the AI Studio documentation. There are three important options for our agent, to select:

• Type: WhatsApp

• Template: Start From Scratch

• Event: Inbound

Our agent will be fairly simple, using just 4 different Node Types:

1. Send Message Node: a single text message sent from the virtual agent to the user.

2. Collect Input Node: the virtual agent will prompt a question to the user. The user’s input will be captured and stored to a parameter value.

3. Conditional Node: a logical operator that allows your flow to differentiate between different values for a parameter. E.g. if/else. 

4. Webhook Node: This node enables you to send and request data to and from third-party services, like your Google Sheets API!

How to Create Custom Parameters in AI Studio

Our low-code agent will use 5 custom parameters. Create the parameters: feature, feedback, feedback_type, first_name, and last_name. All 5 should be of the @sys.any entity.

Custom Parameters in AI Studio

How to Create a WhatsApp User Feedback Flow in AI Studio

Our virtual agent flow will consist of greeting our user by name and then collecting the product feedback from our user before sending the data to our Google spreadsheet. You can add the following nodes, starting from the Start Node.

1. Retrieve User Name a. Node Type: Webhook Node b. Method: GET c. URL:  Your Web App URL from the previous section d. Body: select body type to be Text e. Query Parameters:

   • Query Parameter: path

   • Value: users

   f. Response Mapping

   • Object path: [0][“first_name”], Parameter: $first_name

   • Object path: [0][“last_name”], Parameter: $last_name

GET Request with Response Mapping2. Welcome Message

1. Node Type: Send Message Node

2. Output Type: Text

3. Agent Says: “Hey $first_name $last_name, welcome back! Thanks for giving us some feedback ”

3. Collect Feedback Type

1. Node Type: Collect Input Node

2. Parameter: feedback_type

3. Message: Reply Buttons

4. Body: “Please select the type of feedback” 

Buttons:

1. Button Title: “ Report a bug”, Button Value: bug

2. Button Title: “ Feature request”, Button Value: request

3. Button Title: “️ Other”, Button Value: other

4. Collect Feature

1. Node Type: Collect Input Node

2. Parameter: feature

3. Message: Reply Buttons

4. Body: “Which feature does this concern?”

5. Buttons:

6. Button Title: “ Feature One”, Button Value: one

   • Button Title: “ Feature Two”, Button Value: two

   • Button Title: “ Feature Three”, Button Value: three

5. If feedback = bug

1. Node Type: Condition

2. Condition: if type = bug

3. Parameter: feedback_type

   • Operation: is equal to

   • Value: bug

Here, your flow will diverge for a single step. This is because we want to allow users to send us photos of their bugs, but for all other feedback, we expect them to type out their descriptions. So from the if type = bug exit point in the previous node, connect it to the Collect Bug Report Node. For the default exit point in the previous node, connect it to the Collect Feedback Node. The two nodes are as follows:

6. Collect Bug Report

1. Node Type: Collect Input Node

2. Parameter: feedback

3. Message: Text

4. Body: “ Please describe the bug. You can also send a photo of the bug.”

Expected Input: Text & Image

7. Collect Feedback

1. Node Type: Collect Input Node

2. Parameter: feedback

3. Message: Text

4. Body: “ Please describe your feedback”

5. Expected Input: Only Text

8. Send to Google Sheets

1. Node Type: Webhook Node

2. Method: POST

3. URL:  Your Web App URL from the previous section

4. Body: {"phone_number": $SENDER_PHONE_NUMBER, "type": "$feedback_type", "feature": "$feature", "feedback": "$feedback"}

POST Request in AI Studio9. Thank You Message

1. Node Type: Send Message Node

2. Output Type: Text

3. Agent Says: “Thank you for the valuable feedback Have a great day!”

10. End Conversation Node

Now, your agent is ready to send WhatsApp messages from Google automatically!

How to Test Your WhatsApp Chatbot

Now that your virtual agent is all hooked up, you can open the Tester. Ensure to first add a testing phone_number value by clicking the settings icon in the top right corner. Find the SENDER_PHONE_NUMBER system parameter and add the value from your users sheet in Google. To close the Initial Parameters panel, click again on the settings icon in the Tester. Now you can click the refresh icon and start your test conversation.

How cool is your new product feedback agent?! Did you test out adding photos for bug reports? Go check out the Google Sheet and see how it adds a URL. When you try to open the URL, you should automatically download the image that was sent to your virtual agent, awesome!

Conclusion

In this tutorial, you built a WhatsApp and Google Sheets integration. You can now send WhatsApp messages from Google Sheets automatically, and also enter new data into Google Sheets from WhatsApp. And you were able to do it all in no-code/low-code!

How will you extend your agent? You can add authentication to your Google Sheets API. Or you can make your agent more user-friendly with our Generative AI Node. You could also connect a user directly to a member of your team with the Live Agent Routing Node.

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

• Endless FAQs on WhatsApp With Generative AI and AI Studio

• Low Code Leverage: AI Studio with Airtable

• Build a Farewell App Using Vonage AI Studio and Notion as a Database

• How to Create a Conversational AI Mockup