How to populate answer options dynamically from the Google Sheets (direct integration)

In this solution, we’ve described the steps of how to pull the step options dynamically from the Google Sheet using Integromat.

In this solution, we’ll show how it’s possible directly!

In our case, we’ll use such a text spreadsheet:

Step 1. Make the spreadsheet available to everyone, by the link:

Step 2. Copy the spreadsheet ID and grid ID from the address bar:

Step 3. On the options-aware step, enable dynamic option population (needs developer mode enabled):

In the case of the autocomplete step or image autocomplete step, so the one with the search capability, put such a code:

function (input, getVariableValue, callback) {
    var q = new quriobot.URL("https://docs.google.com/a/google.com/spreadsheets/d/1hrq2l7fT9ui8JctdjEiAtHKDdggDGARYUFXVc44rD0k/gviz/tq", true)
    q.query = {
          tqx: "out:csv",
          gid: "0",
          tq: "select A where lower(A) like '%" + input.toLowerCase() + "%'"}
        quriobot.ajax(
            q.toString(),
            function(responseText) {
                var response = responseText.split("\n").filter(Boolean).map(JSON.parse)
                var options = response.map(function(item, index) {
                    return {value: index + 1, label: item}
                }).filter(function(option){return option.label})
                callback(options)
            }
    )
}

In this code, you’ll need to replace those values:

1hrq2l7fT9ui8JctdjEiAtHKDdggDGARYUFXVc44rD0k - with your previously copied spreadsheet ID

“0” - with your previously copied grid ID

tq: "select A where lower(A) like '%" + input.toLowerCase() + "%'"}

this part can be changed if you have a different structure of the spreadsheet and/or need to select a different column than the column you’re looking up for, like:

tq: "select B where lower(A) like '%" + input.toLowerCase() + "%'"}

In the case if the options-aware step doesn’t have a lookup capability (multiple-choice or multiple-image), the code will not need the part with the lookup:

function (input, getVariableValue, callback) {
    var q = new quriobot.URL("https://docs.google.com/a/google.com/spreadsheets/d/1hrq2l7fT9ui8JctdjEiAtHKDdggDGARYUFXVc44rD0k/gviz/tq", true)
    q.query = {
          tqx: "out:csv",
          gid: "0",
          tq: "select A"}
        quriobot.ajax(
            q.toString(),
            function(responseText) {
                var response = responseText.split("\n").filter(Boolean).map(JSON.parse)
                var options = response.map(function(item, index) {
                    return {value: index + 1, label: item}
                }).filter(function(option){return option.label})
                callback(options)
            }
    )
}

where the spreadsheet ID and grid ID replacements will need to be applied the same way as before.

You’re done with the integration!

How how do I provide optional subtext and images for the dynamic options?#

To do so, the code for the dynamic options will be slightly different:

function (input, getVariableValue, callback) { var q = new quriobot.URL(“https://docs.google.com/a/google.com/spreadsheets/d/1hrq2l7fT9ui8JctdjEiAtHKDdggDGARYUFXVc44rD0k/gviz/tq”, true) q.query = { tqx: “out:csv”, gid: “0”, tq: “select A, C”} quriobot.ajax( q.toString(), function(responseText) { var response = responseText.split("\n").slice(1).filter(Boolean).map(function(item){return JSON.parse("["+item + “]”)}) var options = response.map(function(item, index) { return {value: index + 1, label: item[0], image: item[1]} }).filter(function(option){return option.label}) callback(options) } ) }

Note that we select both columns A and C and use them for the label and image of the returned options. To provide subtext, you can use description attribute of the option.

Edit this page