Tracking code installation
Tracking page visits
Intents and Entities
Building Dialogflow agent and connecting to Activechat
Sending user input to NLP engine
Triggering bot skills by intents and events
Working with entities
NLP best practices
Sending order updates
One of the most requested features for every chatbot platform is the ability to interact directly with Google Sheets. This can be helpful for a whole bunch of different use cases – sending lead data, making appointments, fetching discount coupons, making polls etc.
In almost every other chatbot builder you have to use third-party tools like Zapier or Integromat to achieve this (and this can be quite costly if you have lots of users in your chatbot). Activechat has direct Google Sheets chatbot integration as part of the platform that is powerful and easy to use.
Go to “Settings – Integrations” and select “Google Sheets” on the left. Click “Connect” to give Activechat permission to access your Google account and you’ll see the complete list of Sheets in the table. This is for reference only (you will be able to select specific Sheets from this list later in block settings).
Now when you have your Google Sheets account connected to the chatbot, you can use GS-SEARCH and GS-UPDATE blocks to perform searches and update data in your spreadsheets.
Use GS-SEARCH block to perform data search. It can be used to search for specific data in table columns and also to find the number of row that does not contain any data (to append new rows to your spreadsheet).
Selecting spreadsheet and worksheet should be pretty straightforward. Next, select the column to search for data (you can use constants like “A”, “B”, “AA” etc here or bot variables) and value to search for (again, this can contain both constants and variables).
To find empty row (this is required if you want to add new row to your spreadsheet) just keep “value” field empty. Please note that this will find the first row where cell in the selected column is empty (contains no value).
When this block is executed, you will get two system variables with search result:
will contain “Ok” if the value is found and “This value was not found in __ column” otherwise
will contain row number on successful search
If the row was found, Activechat will fetch values from every cell in this row to chatbot variables with names that correspond to column indices:
will contain cell value from “A” column in this row (row number $_gs_row)
will contain cell value from “B” column in this row (row number $_gs_row)
. . .
will contain cell value from “XX” column in this row (row number $_gs_row)
Now when you have $_gs_row value, it can be used to send data to this row in your spreadsheet. Use GS-UPDATE block to achieve this. With Activechat’s Google Sheets chatbot integration you can send multiple columns at once, filling the whole row with data from your chatbot.
GS-UPDATE can work in two modes. “Insert” mode will shift existing rows (starting from “row” parameter) down one row, and then populate new row with cell values. When “Insert” is turned off, current cell values will be replaced with new ones.
After GS-UPDATE block is executed you can check resulting status with $_gs_result variable (it should contain “Ok” if data was sent successfully). Use SWITCH block to check this if necessary.
Do not forget to connect TEXT block (displaying $_last_error system variable) to the ERROR exit (both in GS-SEARCH and GS-UPDATE) so that your bot will inform you if anything goes wrong while executing these blocks. If you do not have anything connected, the blocks will fail silently and you will spend lots of time trying to figure out what’s going on.