How to use external data (from Google Sheets) in your bot chat-texts and responses

As described here, It’s possible to use custom Function results in your bot chat texts and responses. This solution describes how to enrich your QB with data from a Google sheets file.

We will make use of the Script variables and Integromat.

To show data from your Google Sheets file into your bot, just follow the steps below:

Step 1. Create an Integromat scenario:

Custom webhook:

For the test request, use this command (you’ll need this tool installed: HTTPie - a CLI, cURL-like tool for humans. http://httpie.org)

http --json POST https://hook.integromat.com/ab62jj126nlvvah6cvksdyv8eli4awdk search='Test data 1'

Google sheets - search rows:

Aggregate JSON:

Webhook response:

Step 1: Enable developer mode

Step 2: Create a script variable called example:

function(callback, variables) {
    quriobot.ajax(
        "https://hook.integromat.com/ab62jj126nlvvah6cvksdyv8eli4awdk",
        function(responseText) {
            var response = JSON.parse(responseText);
            callback(response[0].a)
        }
    )
}

Step 2: How to get the data from the right ( column, row)?To point to the right field in your Google sheet you need to point to add that column to the data structure defined in the Integromat scenario in the JSON module, then use response[0].b in the code snippet above.

This example that translates to (A,2) as " entry[0]" is the second row and the column with the title " test"

Example data: used here, so, for example, using respons [1].a will lead to the bot showing “Test column data 2”

Step 3: You can now use {{example}} in any chat text, response message, response action

The Front end then starts like:

Note: Depending on the external service you want to get data from from the JS function might be different, so we made it easier for you to call external HTTP endpoints from QB without relying on any external JavaScript libraries.

How do I look up certain rows instead of getting all the values?#

Using google visualization query language to filter the data:

which selects column A if A == ‘Test data 2’

function(callback, variables) {
    var q = new quriobot.URL("https://hook.integromat.com/ab62jj126nlvvah6cvksdyv8eli4awdk", true)
    quriobot.ajax(
        q.toString(),
        function(responseText) {
            var response = JSON.parse(responseText);
            callback(response[0].a)
        },
       {search: 'Test data 2'},
       {'Content-Type': 'application/json'}
    )
}

this will give column A for the first found row as a response.

Edit this page