Activechat Manual
  • What is Activechat?
  • New? Start here
    • The basics
    • Set up your first project
    • Install the chat widget
    • Upload the knowledge
    • Explore the CRM
    • AI-assisted live chat
      • How to set context for AI hints
    • Live chat mobile app
    • Build your first automation
  • Conversational AI
    • For Customer Service Teams
    • For Product Managers
    • For Innovation Teams
    • For Marketers
    • For e-commerce
    • For developers
  • Help Guides
    • Setting up your team
    • Managing conversations
      • Customer attributes
      • User tags and segments
      • Searching for specific users
      • Agent tags (live chat groups and queues)
      • Triggering live chat sessions from the bot
      • Notifications with the TRIGGER block
    • Managing knowledge
      • Uploading business data
      • Question answering and live chat hints
      • Fine-tuning the large language model
    • Natural language automation
    • Building automations visually
      • Customizing your welcome message
      • Adding new skills
      • Navigating skills
      • Copying skills and blocks
      • Handling errors
    • Improving your virtual agent
    • Using live chat AI hints
    • Customizing automatic website page messages
    • Tracking website actions
    • Facebook Ads automation
      • How to set up a Facebook ads bot
      • How to use buttons and quick replies in a Facebook ads chatbot
    • Lead generation
    • Zapier integrations
    • Customizing your project
      • How to customize the chat widget
      • How to customize the Facebook chat widget
      • How to change bot settings
    • Pricing guide
  • Fundamentals
    • Terminology
      • Intents and entities
      • Contexts
      • Skills and events
        • Built-in system skills
          • /start
          • /default
          • /_default_fallback
          • /_start_live_chat
          • /_page_visit
          • /_error
      • Conversation elements
        • Messages
        • Buttons
        • Quick replies
        • Galleries / carousels
    • Messaging channels
      • Website chat widget
        • Installation
        • Customization
        • Voice input
      • Chat widget landing page
      • Facebook Messenger
        • Connect your page
        • 24 hour rule
        • Message tags
        • Persistent menu
      • Telegram
      • Email
      • Twilio SMS automation
    • Intents and bot skills
    • Conversation insights
    • Grow tools
      • Landing pages
      • Messenger links and QR codes
    • Broadcasting
  • Visual builder reference
    • Sending messages
      • TEXT
      • LISTEN
      • IMAGE
      • MEDIA
      • GALLERY
      • FILE
      • EMAIL
      • SMS
      • LEAD
    • Triggering events
      • SEND
      • CATCH
      • TRIGGER
      • LIVE CHAT
    • Manipulating data
      • DATA
      • ADD TAG
      • REMOVE TAG
      • JSON
      • STATUS
      • VALIDATION
    • Conditional logic
      • SWITCH
    • Timers and delays
      • TIMER
      • WAITFOR
      • WAITUNTIL
    • E-commerce blocks
      • CATEGORY
      • PRODUCT
      • VARIATIONS
      • SIMILAR
      • UPSELLS
      • CROSSSELS
      • Shopping carts
        • ADD TO CART
        • UPDATE CART
        • SHOW CART
        • CLEAR CART
        • CREATE ORDER
    • Natural Language
      • NLP
    • System attributes
    • System events
  • Integrations
    • Google services
      • Connect your Google account
      • Google Sheets
        • Searching and updating Google Sheets data
        • Building galleries with Google Sheets data
      • Google Calendar
        • Searching for events
        • Creating and updating events
    • Shopify
    • WooCommerce
    • Dialogflow
      • Building an agent
      • Using entities
      • Slot filling
      • Context management
      • E-commerce NLP
Powered by GitBook
On this page
  • Searching for data in Google Sheets with GS-SEARCH
  • Updating data in Google spreadsheet with GS-UPDATE
  • Example of a real-life Google Sheets chatbot integration

Was this helpful?

  1. Integrations
  2. Google services
  3. Google Sheets

Searching and updating Google Sheets data

PreviousGoogle SheetsNextBuilding galleries with Google Sheets data

Last updated 3 years ago

Was this helpful?

You can easily search and update data from the chatbot with the GS-SEARCH and GS-UPDATE visual chatbot building blocks. Do not forget to first!

Searching for data in Google Sheets with GS-SEARCH

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

Using GS-SEARCH to search for data in the Google spreadsheet

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:

  • $_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.

Updating data in Google spreadsheet with GS-UPDATE

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.

Example of a real-life Google Sheets chatbot integration

Searching for an empty value in Google Sheets row

When GS-SEARCH block is executed, two will be set to let you know about the search result:

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 .

Using Google Sheets data in the chatbot conversation
Updating Google Sheets data from the chatbot

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 block to check this if necessary.

Do not forget to connect the 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 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 –

chatbot attributes
data processing blocks
SWITCH
TEXT
handling of chatbot errors
Building an advanced restaurant delivery chatbot with Google Sheets integration
Google Sheets
connect your Google account
user attributes