LoadData

As the name suggests, this command can be used to access data in a sheet in the same spreadsheet where your chatbot exists or a different spreadsheet altogether. A use case could be to host a calculator within the spreadsheet and let chatbot return the values, or there are gazillions of plugins that feed data into Google Spreadsheet, that you can access easily and create a chatbot that uses that data.

IDTypeTextGoto
LoadData{“from”: {“type”: “spreadsheet“, “config”: {“url”: “url_of_spreadsheet“, “sheet_name”: “sheet_name_comes_here“}, “range”: “A1:G5.0.1“, “headers”: 1 or 0, “format”: “row_or_column“}, “save_to_var”: “variable_to_load_data-in“}

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.

IDTypeTextGoto
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:

  1. 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. More formats are coming soon
      1. 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.
      2. 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).
      3. query: Specify the SQL query which should be run in the given sheet. Here is the reference documentation for the SQL query.
      4. 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).
      5. 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:

  1. from clause is not supported. The sheet name you specify is used to run the query.
  2. 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.

Here is a sample Chatbot spreadsheet that uses LoadData