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.

SendPulse also offers a direct integration with Google Sheets. Learn more.

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.

Last Updated: 17.06.2024