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 definition in the Integromat scenario in JSON module, then use response[0].b in the code snipper above. 

This example that translates to (A,2) as "entry[0]" is the second row and the column with 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 lookup certain row 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 the column A for the first found row as a response



Need more help or looking for inspiration? Join the Quriobot community!

Quriobot
Friendly & Fun
Interact & Convert