Searching and updating Google Sheets data
Last updated
Last updated
You can easily search and update Google Sheets data from the chatbot with the GS-SEARCH and GS-UPDATE visual chatbot building blocks. Do not forget to connect your Google account first!
Use the GS-SEARCH block to search for the row with specific column values. It can be used to search for specific data in table columns and also to find the number of the row that does not contain any data (to append new rows to your spreadsheet).
Please note that on each execution the GS-SEARCH block will return the number of a single row (and also – values of all cells in that row as user attributes). If you need to find multiple rows, use the “Start row” parameter to start the new search from the previously found row.
In the example below chatbot is searching Google Sheets to find a row where column J does not contain the “done” text and column A is equal to the Messenger ID of the current chatbot user (use case: find an order from that customer that is not fulfilled yet).
Selecting a spreadsheet and worksheet should be pretty straightforward. Next, build the search criteria with up to 3 conditions combined with “AND” or “OR” (again, you can use attributes here!).
To find an empty row (you’ll need it’s number if you want to add a new row at the bottom of your spreadsheet) just use an empty value in your search criteria. Please note that this will find the first row where the cell in the selected column is empty (contains no value). An example below demonstrates searching for the first row where column A is empty:
When GS-SEARCH block is executed, two chatbot attributes will be set to let you know about the search result:
$_gs_result – will contain “Ok” if the value was found and “This value was not found in __ column” otherwise
$_gs_row – will contain row number if the search was successful
If the row was found, Activechat will fetch values from every cell in this row to chatbot attributes with names that correspond to column names:
$_gs_a will contain cell value from the “A” column in this row (row number is in $_gs_row, remember!)
$_gs_b will contain cell value from the “B” column in this row etc
. . .
$_gs_xx will contain cell value from the “XX” column in the row that was found in your spreadsheet
If some columns are empty for that row, respective attributes will also be empty so that you do not have to worry about old values from previous searches still sitting in some of them.
You can use these attributes to display data from Google Sheets to the user of your chatbot or use the values in your operations with any of the data processing blocks.
Once you know the row number ($_gs_row), 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 the row specified in the “Row” block parameter) down one row, and then populate a new row with specified cell values. When “Insert” is turned off, current cell values will be replaced with new ones.
After the GS-UPDATE block is executed you can check its status with $_gs_result attribute (it will contain “Ok” if data was sent successfully). Use the SWITCH block to check this if necessary.
Do not forget to connect the TEXT block (displaying $_last_error system attribute) 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. Remember that the correct handling of chatbot errors is extremely important for the quality of your conversation. If you do not have anything connected, the default error handling skill will be triggered automatically.
We have a post in our blog, describing the complete use case to search and update Google Sheets data from the chatbot. Please check it here – Building an advanced restaurant delivery chatbot with Google Sheets integration
