Do you have data in a Google Sheets file and would like your chatbot to access it?
Would you like to save your users' information in a Google Sheets file? This article is
here to help you!
Botnation AI offers 3 elements to integrate into your sequences to access your
Google Sheets file:
Backing up data to Google Sheets
Recovering data from Google Sheets
Generating a carousel from your Google Sheets data
General Information (READ)
Your Chatbot's access to your Google Sheets file requires you to follow a few rules:
Your table must contain one and only one line of header in the 1st line
The first column of your table should never be empty
Do not use the undefined value in a cell. This keyword is reserved for formulas that do not return results.
Configuration
First and foremost, you need to link your Google Account to your Chatbot. This
happens in the Plugins tab of your chatbot's settings.
Click the Link a Google Account button and select the account that has access to
your Google Sheets file on the page that appears.
Once the permissions are accepted, you are back on your Chatbot and the account
you have just linked is now selected.
Expert mode
Expert mode allows you to enable advanced options for Google Sheets. Refer to the
section of the item for more information.
Data backup
The Data Backup item lets you insert or update data from 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 update.
Create a row if no line matches (option): If no row in your table matches the specified conditions, a new line will be created.
Update the following columns: Specify the columns to update.
N.B: The expert mode allows you to specify several fields in the Update rowscorresponding to the following conditions block. You can create AND / OR
conditions.
Data recovery
The Data Recovery element lets you update Chatbot variables from 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 retrieve.
Back up data in variables: Assign your Google Sheets data to your Chatbot's variables.
Redirect if no result: Redirect your user if no row in your table matches the previous conditions.
N.B: the expert mode allows you to:
Specify several fields in the block Select the 1st line corresponding to the following conditions. You can create AND / OR conditions.
Assign your variables more precisely. The values in your columns will be retrievable via [[GOOGLE_SHEET_XXX]] variables (where XXX is thecolumn).
Carousel
The Carousel item allows you to generate a carousel from information from your
Google Sheets.
Spreadsheet file: Select the Google Sheets file to use.
Tab: Select the tab to use
Select the lines corresponding to the following conditions: Select the conditions to
identify the rows to retrieve.
Fill the carousel from the information in your Google Sheet: Create your carousel
pages from your Google Sheet information.
Redirect if no result: Redirect your user if no row in your table matches the previous
conditions.
N.B: the expert mode allows you to:
Specify several fields in the block Select the 1st line corresponding to the following conditions. You can create AND / OR conditions.
Sort results by column of your Google Sheet
Limit the number of pages to display in the carousel
Assign your variables more precisely. The values in your columns will be retrievable via [[GOOGLE_SHEET_XXX]] variables (where XXX is thecolumn).
Frequently Asked Questions (FAQ)
The date saved in my Google Sheets file is not formatted correctly.
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 delay is variable and can take from 1 to a few seconds depending on Google's "good mood" which will depend on: the API load (number of operations in the thread, the replication of your data on all their servers, possible limitations in the 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 that you don't be dependent on "writes".
You can read data from a Google Sheets document, manipulate it in your bot and then write a result to Google Sheets but we do not recommend the opposite: write data to Google Sheets and then read this data immediately because it might not be up to date in Google and disturb 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.
What is the "match" condition for line selection?
The condition "match" allows to enter a regular expression that will be tested on the
value of the column. This can for example be used to test whether the value contains
a substring of characters. For example, the condition "match" with the value "nation"
will return true for cells containing the text "Bot created by Botnation" or
"championship of nations".
For more information on regular expressions, refer to this documentation:
https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/RegExp
Learn more: