We see a lot of sales and business teams using spreadsheets as a database. And why not? Spreadsheets are easy to use, edit and quickly collaborate on with not much learning curve. Hybrid.Chat already allows you to use Google spreadsheet as a CMS (content management system) for your chatbot. Chatbots can also be connected directly to any SQL Database to perform any query. But now, you can use a simple spreadsheet as a database within your chatbot that both Sales team and Chatbot can interact with easily, to enable perfect co-bot synergy (where humans and bots work together) 

Meet Zippy:

Here is an example chatbot created that remembers all 33,000 Zip codes in the United States and can fetch details about it using Database loaded in the Chatbot spreadsheet itself. Zippy bot Gif Test Drive this chatbot here: https://mvhbn.hybrid.chat/chat.html Get the Chatbot Template for Zippy (Spreadsheet as Database Chatbot Demo)

How to use Google Spreadsheet as Database for Chatbot:

Technically, you can use the same LoadData chatbot tag to do it.  This command can also be used to run SQL queries within sheets to get the required result. The SQL query is powered by the Google Charts Query Language. Simply replace the range parameter with a query parameter and its value with a SQL query.

  LoadData {“from”: {“type”: “spreadsheet“, “config”: {“url”: “url_of_spreadsheet“, “sheet_name”: “sheet_name_comes_here“}, “query”: “select * where B = 100“, “headers”: 1 or 0, “format”: “row_or_column“}, “save_to_var”: “variable_to_load_data-in“}

Parameters:

  • from: Lets you get data from multiple sources, shall there be a requirement. So, suppose you can get some data from a spreadsheet and some from a remote database
    1. type: Lets you define the type of data source it is. Supported formats are spreadsheet (Supports any Google spreadsheet) for now. 
      • config: Helps you to specify the path of the spreadsheet and name of the sheet that you need to access
        1. url: You can specify url of another Google spreadsheet here. If the sheet already exists within your chatbot spreadsheet, just say “this”
        2. sheet_name: Specify the name of the sheet from where data should be sourced
      • range: Specify the range of cells (in the spreadsheet above) from where data needs to be sourced. A range is defined by the reference of the upper-left cell (minimum value) of the range and the reference of the lower right cell (maximum value) of the range. The notation for this range is (A1:C6).
      • query: Specify the SQL query which should be run in the given sheet. Here is the reference documentation for the SQL query.
      • headers: Specify if data has a header row or not. Accepted values are 0 (for No headers) and 1 (for headers in the first row).
      • format: Specifies if the data needs to be read in a row or column format. Generally, spreadsheets exist in row format, which is what you would be using most of the time.
    2. save_to_var: Name of the variable in which you wish to load the data. Use these variables to call in any part of the chatbot conversation.

A few points to note about SQL queries:

  • from clause is not supported. The sheet name you specify is used to run the query.
  • User-specified column names are not supported. For eg. select * where zipcode = 100000 is an invalid query. Assuming that the zipcode column is the column A in the sheet, then the correct query would be select * where A = 100000.

Use cases:

Ecommerce Order Management: You can have a webhook pushing data from all orders into a spreadsheet for all your orders and provide support to any customer coming in, asking for order update. Quote Calculators: Chatbot can perform calculations based upon user input, letting it calculate quote for a product, which is far more intuitive that form based calculators, while also collecting lead data using Facebook Chat campaigns or normal web chatbot embeds.