How to Transfer Data from Google Sheets to Chatbots

If you use Google Sheets as a database for contacts, orders, or information on product availability, you may need to feed these data points to chatbots so that users get relevant messages.

In this article, we will talk about how to transfer data from Google Sheets to the “API request” element in a chatbot flow using integration with the Google Sheets API and SendPulse.

Set Up Your Google Account, Keys, and Files

To use Google Sheets to send data, create a project in the Google Cloud Console, and grant access to your database spreadsheet.

Select a Project and Set Up

Log in to the Google Cloud Console with your Google Account. Create a new project, or select an existing one from the list. Specify your project name and parent organization if necessary.

Enable the Google Sheets API

In “APIs & Services,” select “Enabled APIs & services.” Click Enable APIs & services, and go to the integration library.

Find "Google Sheets API" in the library, and click Enable.

Create an API Key

Go to “APIs & Services” > “Credentials,” and click Create credentials. Select "API key" from the drop-down menu.

You will get a message that a key has been created. You can copy it immediately or later.

We recommend limiting the use of your API key to the integration with Google Sheets right away. To do this, click the three dots icon next to your API key name, select “Edit,” and, under “API restrictions,” specify the Google Sheets API.

Grant Access to the Spreadsheet

Make the spreadsheet available for viewing via a link, or add your Google Cloud project service account to the list of users who can view the file.

Set Up a Chatbot Flow

Add the "API request" Element

To use the values from your spreadsheet, form a URL of the following type:

https://sheets.googleapis.com/v4/spreadsheets/{sheet_id}/values/{sheet_name}!{range}?key={api_key}

Parameters used in the URL:

Add the “API request” element to your chatbot flow, select the GET method for the request, and insert the resulting URL in the link field.

You can test the received data by clicking Test request. Then, you can use the resulting data in "Mapping settings."

Click Add, and, in "Response Field," select a value from the received request response — it will be output as a {{$['somekey']['nestedkey']}} JSONPath link.

In the next field, select a variable from the chatbot audience where you want to import the values to use them in other elements.

Save the Result, and Configure the Subsequent Elements

You can use the retrieved data as a JSONPath link in your messages. Copy the "Response Field" link without saving data to variables.

However, note that you can only transfer a value in a JSONPath link in an element right after "API Request," so if you want to use certain data points multiple times, save them.

You can use saved values in the "Filter" and "Message" elements in the same way as other variables.

Read also: How to Transfer Data from a Chatbot to Google Apps (using the integration with Make as an example).

Last Updated: 28.02.2023