Retrieve and insert data to Google Spreadsheet from your chatbot
You have data in a Google Sheets file and you want your chatbot to access it? Would you like to save information about your users in a Google Sheets file? This article is here to help you!
Botnation AI offers you 3 elements to integrate in your sequences to access your Google Sheets file:
- Saving data to Google Sheets
- Retrieving data from Google Sheets
- Generating a carousel from your Google Sheets data
1. General information
The access of your Chatbot to your Google Sheets file requires to respect some rules:
- Your table must contain one and only one header line in the 1st line
- The first column of your table should never be empty
- Do not use the value undefined in a cell. This keyword is reserved for formulas that do not return a result.
2. Configuration
First of all, you need to link your Google account to your Chatbot. This happens in the Plugins tab of your chatbot settings.
Click the Link Google Account button and then select the account that has access to your Google Sheets file from the page that appears.
Once the permissions are accepted, you are back on your Chatbot and the account you just linked is now selected.
Expert mode
Expert mode allows you to enable advanced options for Google Sheets elements. Refer to the item’s section for more information.
3. Data backup
The Data Backup feature allows you to insert or update data in your Google Sheets.
Spreadsheet file: Select the Google Sheets file to use.
Tab: Select the tab to use
Force creation of a line (option): If checked, a new line will be created
Update the rows corresponding to the following conditions: Select the conditions to identify the row(s) to be updated.
Create a row if no row matches (option): If no row in your table matches the specified conditions, a new row will be created.
Update the following columns: Specify the columns to be updated.
N.B.: the expert mode allows you to specify several fields in the Update rows block corresponding to the following conditions. You can thus create AND / OR conditions.
4. Data recovery
The Data Retrieval feature allows you to update Chatbot variables with information from your Google Sheets.
Spreadsheet file: Select the Google Sheets file to use.
Tab: Select the tab to use
Select the 1st line corresponding to the following conditions: Select the conditions to identify the line to be recovered.
Save data to variables: Assign data from your Google Sheets to variables in your Chatbot.
Redirect if no result: Redirect your user if none of the rows in your table match the previous conditions.
N.B.: the expert mode allows you to :
- specify several fields in the Select 1st row block corresponding to the following conditions. You can thus create AND / OR conditions.
- assign your variables in a more precise way. The values of your columns will be retrievable via variables [[GOOGLE_SHEET_XXX]] (where XXX corresponds to the column).
5. Carousel
The Carousel feature allows you to generate a carousel from information in your Google Sheets.
Spreadsheet file: Select the Google Sheets file to use.
Tab: Select the tab to use
Select the rows corresponding to the following conditions: Select the conditions to identify the rows to be recovered.
Fill the carousel with information from your Google Sheet file: Create your carousel pages with information from your Google Sheet.
Redirect if no result: Redirect your user if none of the rows in your table match the previous conditions.
N.B.: the expert mode allows you to :
- specify several fields in the Select 1st row block corresponding to the following conditions. You can thus create AND / OR conditions.
- sort the results by column in your Google Sheet
- limit the number of pages to be displayed in the carousel
- assign your variables in a more precise way. The values of your columns will be retrievable via variables [[GOOGLE_SHEET_XXX]] (where XXX corresponds to the column).
6. Frequently Asked Questions (FAQ)
The date saved in my Google Sheets file is not or incorrectly formatted.
Check the display format of your cell. Choose the Date format or delete the formatting.
How long does it take to write to Google Sheets?
This variable delay can take from 1 to a few seconds depending on the “good mood” of Google which will depend on: the API load (number of operations in the processing queue), the replication of your data on their many servers, possible limitations of use of your Google account etc…
Google Sheets is not a professional CRM or database solution designed to store large volumes of data very quickly, it is an online spreadsheet. That’s why we recommend you not to depend too much on the entries.
You can read data from a Google Sheets document, manipulate it in your bot, then write a result to Google Sheets but we don’t recommend the opposite: writing data to Google Sheets and then immediately reading this data because it might not be up to date at Google and disrupt the behavior of your chatbot.
It is better to perform these operations in 2 steps with a human action in between in your bot for example, like a click on a button, which will leave a few seconds for Google to write and update your data.
For a more “dynamic” use we recommend the use of the Botnation API.
What does the “match” condition for line selection mean?
The “match” condition allows to fill a regular expression which will be tested on the value of the column. This can be used, for example, to test whether the value contains a substring. For example the condition “match” with the value “nation” will return true for the cells containing the text “Bot created by Botnation” or ” nationschampionship”.
For more information on regular expressions, see this documentation: https://developer.mozilla.org/fr/docs/Web/JavaScript/Reference/Objets_globaux/RegExp