Chatbot education

👉 Chatbot videos

👩🏼‍🎓 Bot Academy

Activechat Essentials

E-commerce Chatbots

Advanced Chatbot Tools

Tracking code installation

Tracking page visits

Natural Language Chatbots

Intents and Entities

NLP contexts

Building Dialogflow agent and connecting to Activechat

Sending user input to NLP engine

Triggering bot skills by intents and events

Working with entities

Slot filling

Using contexts

NLP best practices 

Google Sheets chatbot integration

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.

1. Connecting your Google Sheets account to the chatbot

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).

Google sheets chatbot integration

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.

2. Searching for data in Google Sheets with the chatbot

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).

Google sheets chatbot integration - search block

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:

➡️ $_gs_result

will contain “Ok” if the value is found and “This value was not found in __ column” otherwise

➡️ $_gs_row

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:

➡️ $_gs_a

will contain cell value from “A” column in this row (row number $_gs_row)

➡️ $_gs_b

will contain cell value from “B” column in this row (row number $_gs_row)

. . .

➡️ $_gs_xx

will contain cell value from “XX” column in this row (row number $_gs_row)

3. Updating data in Google Sheets from the chatbot

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. 

Google Sheets chatbot video walk-through