{"id":691,"date":"2022-09-06T17:09:14","date_gmt":"2022-09-06T15:09:14","guid":{"rendered":"https:\/\/support.botnation.ai\/import-and-export-google-sheets-data-into-your-chatbot\/"},"modified":"2026-01-02T16:34:44","modified_gmt":"2026-01-02T15:34:44","slug":"import-and-export-google-sheets-data-into-your-chatbot","status":"publish","type":"support","link":"https:\/\/botnation.ai\/en\/support\/import-and-export-google-sheets-data-into-your-chatbot\/","title":{"rendered":"Import and Export Google Sheets data into your chatbot"},"content":{"rendered":"<p>Retrieve and insert data to Google Spreadsheet from your chatbot<\/p>\n\n\n\n<p>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!<\/p>\n\n\n\n<p>Botnation AI offers you 3 elements to integrate in your sequences to access your Google Sheets file:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Saving data to Google Sheets<\/li><li>Retrieving data from Google Sheets<\/li><li>Generating a carousel from your Google Sheets data<\/li><\/ul>\n\n\n\n\n\n<h2 class=\"wp-block-heading\">1. General information<\/h2>\n\n\n\n<p>The access of <a href=\"https:\/\/botnation.ai\/votre-chatbot-en-plein-ecran-chatbot-web\/\">your Chatbot<\/a> to your Google Sheets file requires to respect some rules:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Your table must contain one and <strong>only one header line<\/strong> in the <strong>1st line<\/strong><\/li><li>The <strong>first column<\/strong> of your table should <strong>never be empty<\/strong><\/li><li>Do not use the value <strong>undefined<\/strong> in a cell. This keyword is reserved for formulas that do not return a result.<\/li><\/ul>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/image-105.png.webp\" alt=\"\" class=\"wp-image-695\"><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\">2. Configuration<\/h2>\n\n\n\n<p>First of all, you need to link your Google account to your Chatbot. This happens in the <strong>Plugins<\/strong> tab of your chatbot settings.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/image-106.png.webp\" alt=\"\" class=\"wp-image-697\"><\/figure>\n\n\n\n<p>Click the <strong>Link Google Account<\/strong> button and then select the account that has access to your Google Sheets file from the page that appears.<\/p>\n\n\n\n<p>Once the permissions are accepted, you are back on your Chatbot and the account you just linked is now selected.<\/p>\n\n\n\n<p><strong>Expert mode<\/strong><br>Expert mode allows you to enable advanced options for Google Sheets elements. Refer to the item\u2019s section for more information.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">3. Data backup<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/googlesheet_00_UK-1024x604.png.webp\" alt=\"\" class=\"wp-image-699\"><\/figure>\n\n\n\n<p>The <strong>Data Backup<\/strong> feature allows you to insert or update data in your Google Sheets.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/image-107.png.webp\" alt=\"\" class=\"wp-image-702\"><\/figure>\n<\/div>\n\n\n<p><strong>Spreadsheet file<\/strong>: Select the Google Sheets file to use.<\/p>\n\n\n\n<p><strong>Tab<\/strong>: Select the tab to use<\/p>\n\n\n\n<p><strong>Force creation of a line (option)<\/strong>: If checked, a new line will be created<\/p>\n\n\n\n<p><strong>Update the rows corresponding to the following conditions<\/strong>: Select the conditions to identify the row(s) to be updated.<\/p>\n\n\n\n<p><strong>Create a row if no row matches (option)<\/strong>: If no row in your table matches the specified conditions, a new row will be created.<\/p>\n\n\n\n<p>Update <strong>the following columns<\/strong>: Specify the columns to be updated.<\/p>\n\n\n\n<p>N.B.: the <strong>expert mode<\/strong> allows you to specify several fields in the Update <em>rows<\/em> block <em>corresponding to the following conditions<\/em><strong>. <\/strong>You can thus create AND \/ OR conditions.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">4. Data recovery<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/googlesheet_01_UK-1024x604.png.webp\" alt=\"\" class=\"wp-image-704\"><\/figure>\n\n\n\n<p>The <strong>Data Retrieval<\/strong> feature allows you to update Chatbot variables with information from your Google Sheets.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/image-108.png.webp\" alt=\"\" class=\"wp-image-706\"><\/figure>\n<\/div>\n\n\n<p><strong>Spreadsheet file<\/strong>: Select the Google Sheets file to use.<\/p>\n\n\n\n<p><strong>Tab<\/strong>: Select the tab to use<\/p>\n\n\n\n<p><strong>Select the 1st line corresponding to the following conditions<\/strong>: Select the conditions to identify the line to be recovered.<\/p>\n\n\n\n<p><strong>Save data to variables<\/strong>: Assign data from your Google Sheets to variables in your Chatbot.<\/p>\n\n\n\n<p><strong>Redirect if no result<\/strong>: Redirect your user if none of the rows in your table match the previous conditions.<\/p>\n\n\n\n<p>N.B.: the <strong>expert mode<\/strong> allows you to :<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>specify several fields in the <em>Select 1st row<\/em> block <em>corresponding to the following conditions<\/em><strong>. <\/strong>You can thus create AND \/ OR conditions.<\/li><li>assign your variables in a more precise way. The values of your columns will be retrievable via variables<em> [[GOOGLE_SHEET_XXX]]<\/em> (where <em>XXX<\/em> corresponds to the column).<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">5. Carousel<\/h2>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/googlesheet_02_UK-1024x604.png.webp\" alt=\"\" class=\"wp-image-708\"><\/figure>\n\n\n\n<p>The <strong>Carousel<\/strong> feature allows you to generate a carousel from information in your Google Sheets.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/image-110-1024x261.png.webp\" alt=\"\" class=\"wp-image-712\"><\/figure>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large is-resized\"><img fetchpriority=\"high\" decoding=\"async\" src=\"https:\/\/botnation.ai\/wp-content\/uploads\/2022\/09\/image-109-566x1024.png.webp\" alt=\"\" class=\"wp-image-710\" width=\"716\" height=\"1295\"><\/figure>\n<\/div>\n\n\n<p><strong>Spreadsheet file<\/strong>: Select the Google Sheets file to use.<\/p>\n\n\n\n<p><strong>Tab<\/strong>: Select the tab to use<\/p>\n\n\n\n<p><strong>Select the rows corresponding to the following conditions<\/strong>: Select the conditions to identify the rows to be recovered.<\/p>\n\n\n\n<p><strong>Fill the carousel with information from your Google Sheet file<\/strong>: Create your carousel pages with information from your Google Sheet.<\/p>\n\n\n\n<p><strong>Redirect if no result<\/strong>: Redirect your user if none of the rows in your table match the previous conditions.<\/p>\n\n\n\n<p>N.B.: the <strong>expert mode<\/strong> allows you to :<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>specify several fields in the <em>Select 1st row<\/em> block <em>corresponding to the following conditions<\/em><strong>. <\/strong>You can thus create AND \/ OR conditions.<\/li><li>sort the results by column in your Google Sheet<\/li><li>limit the number of pages to be displayed in the carousel<\/li><li>assign your variables in a more precise way. The values of your columns will be retrievable via variables<em> [[GOOGLE_SHEET_XXX]]<\/em> (where <em>XXX<\/em> corresponds to the column).<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">6. Frequently Asked Questions (FAQ)<\/h2>\n\n\n\n<p><strong>The date saved in my Google Sheets file is not or incorrectly formatted.<\/strong><br>Check the display format of your cell. Choose the Date format or delete the formatting.<\/p>\n\n\n\n<p><strong>How long does it take to write to Google Sheets?<\/strong><br>This variable delay can take from 1 to a few seconds depending on the \u201cgood mood\u201d 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\u2026<\/p>\n\n\n\n<p>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\u2019s why we recommend you not to depend too much on the entries.<\/p>\n\n\n\n<p>You can read data from a Google Sheets document, manipulate it in your bot, then write a result to Google Sheets but we don\u2019t 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.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>For a more \u201cdynamic\u201d use we recommend the use of the <a href=\"https:\/\/botnation.ai\/en\/support\/botnation-api-import-and-export-data-via-webhook\/\">Botnation API<\/a>.<\/p>\n\n\n\n<p><strong>What does the \u201cmatch\u201d condition for line selection mean?<\/strong><br>The \u201cmatch\u201d 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 \u201cmatch\u201d with the value \u201cnation\u201d will return true for the cells containing the text \u201cBot created by Botnation\u201d or \u201d <strong>nations<\/strong>championship\u201d.<br>For more information on regular expressions, see this documentation: <a href=\"https:\/\/developer.mozilla.org\/fr\/docs\/Web\/JavaScript\/Reference\/Objets_globaux\/RegExp\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/developer.mozilla.org\/fr\/docs\/Web\/JavaScript\/Reference\/Objets_globaux\/RegExp<\/a><\/p>\n\n\n","protected":false},"featured_media":0,"template":"","meta":{"_acf_changed":false},"categorie-support":[183,193,194,185,171,221],"class_list":["post-691","support","type-support","status-publish","hentry","categorie-support-chatbot-web","categorie-support-facebook-messenger-en","categorie-support-features","categorie-support-fonctionnalites","categorie-support-variables","categorie-support-web-chatbot-en"],"acf":[],"_links":{"self":[{"href":"https:\/\/botnation.ai\/en\/wp-json\/wp\/v2\/support\/691","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/botnation.ai\/en\/wp-json\/wp\/v2\/support"}],"about":[{"href":"https:\/\/botnation.ai\/en\/wp-json\/wp\/v2\/types\/support"}],"wp:attachment":[{"href":"https:\/\/botnation.ai\/en\/wp-json\/wp\/v2\/media?parent=691"}],"wp:term":[{"taxonomy":"categorie-support","embeddable":true,"href":"https:\/\/botnation.ai\/en\/wp-json\/wp\/v2\/categorie-support?post=691"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}