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.

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


Step 0: Enable your Google sheet to allow for external sources by Publishing your sheet to the web (open your Google Sheet > File > publish to the web)

Step 1: Enable developer mode

Step 2: create a script variable called example:


function(callback, variables) {
    quriobot.ajax(
        "https://spreadsheets.google.com/feeds/list/1H21kAictlHcU4CA-pn2P75WYw4-FwxRnQ7UmqDW4Vwg/od6/public/values?alt=json",
        function(responseText) {
            var response = JSON.parse(responseText);
            callback(response.feed.entry[0].gsx$test.$t)
        }
    )
}

Important: the URL format you should use is like the URL in the code snippet above the only thing you need to change is the google_sheet_id: 

https: //spreadsheets.google.com/feeds/list/google_sheet_id/od6/public/values?alt=json


Meaning that in the example above the google_sheet_id is thus: "1H21kAictlHcU4CA-pn2P75WYw4-FwxRnQ7UmqDW4Vwg")




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 (header title, entry[X]) which translates to response.feed.entry[0].gsx$test.$t 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 response.feed.entry[1].gsx$column2.$t 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:

https://docs.google.com/a/google.com/spreadsheets/d/1H21kAictlHcU4CA-pn2P75WYw4-FwxRnQ7UmqDW4Vwg/gviz/tq?tqx=out:csv&tq=select A where A = 'Test data 2'

which selects column A if A == 'Test data 2'

function(callback, variables) {
    var q = new quriobot.URL("https://docs.google.com/a/google.com/spreadsheets/d/1H21kAictlHcU4CA-pn2P75WYw4-FwxRnQ7UmqDW4Vwg/gviz/tq", true)
    q.query = {tqx: "out:csv", tq: "select A where A = 'Test data 2'"}
    quriobot.ajax(
        q.toString(),
        function(responseText) {
            var response = JSON.parse(responseText);
            callback(response)
        }
    )
}


this will give the column A as a response



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

Quriobot
Friendly & Fun
Interact & Convert