How to get data from Google Spreadsheet (as JSON) and filter it

Updated 1 week ago by Fran

We are used to work with Google Spreadsheet for many reasons, and in that case we want to use it as a "live" database, where we get data from, to feed our bot. Meanwhile, this spreadsheet can updated, adding more rows or editing cells.

For this example we have the following scenario:

A. We have 3 sets of filters, always the same (not dynamic):

1st filter: User chooses one option among: table, chair or window

2nd filter: All three options (table, chair and window) can be of three materials: glass, wood or steel

3rd filter: All prior options combined, can be: red or blue

B. Every search is going to bring only 1 result (price)

Based on this scenario we have created a Google Spreadsheet:

As you can see, the name of the columns in the first row are input1, input2, input3 and input4. It's important that we keep the same pattern, as the code, is prepare for this, be aware that if is any different the code won't work.

Once we have the Spreadsheet ready, we need to publish it. Here is how to do it: Link

Now we can build our bot: DEMO LINK

After the Welcome Message we are going to create the filters with Ask A Question (Button) blocks:

As you can see we store the answer in the variable @input1, and we will do the same in the other filters but changing the number (@input2, @input3). We then connect to the next filter (block) from the "Any option (default)" exit/green dot.

After the filters, we need to trigger the function that will fetch the data and filter it. For that we are going to use another Ask A Question (Buttons) Block. As you see below, a part from the text, we will add an html snippet, to call the function:

The snippet is the following:

Let's check the price{html}<script>
callSpreadSheetData ("input1","@input1","input2","@input2","input3","@input3")

As you see, we are telling the function the names of the columns and the names of the filters we used in Landbot.

After we add a block to display the information we retrieved and filtered. In this case we where looking for the input4 (the price) and we have stored it with the function in the Landbot variable @input4

Optionally we created a question, in case the user wants to repeat the search or just leave.

To finish, we need to insert the bit of Javascript that will do the heavy work:

1. Fetch the JSON from the public Google Spreadsheet

2. Clean the default JSON google offers us

3. Filter the data, based on user's selections

4. Send the input4 to a Landbot variable (@input4) so that we can display it in the bot

The following code will be added in the Design / Advanced / Add JS section

To work with your Spreadsheet, you just need to change the ID in the code,

Your Spreadsheet ID comes from the URL:

is this part of the URL: 1ak-vbzXhXOlENzlg7NZ9gxlql_526qnYfgqIBrNONc8, and you will change it only in this part of the code, on line 5 (do not delete the ' ):

var spreadSheetId = '1ak-vbzXhXOlENzlg7NZ9gxlql_526qnYfgqIBrNONc8' /*Change it to your spreadsheet ID*/

These scripts and how to's are not native functionalities. Landbot won't be able to support, help or guarantee these scripts and how to's. These Workarounds and How to's are for developers, as a learning and example material on how to extend or modify some of the platform limitations.  Due to platform updates, some scripts might stop working in the future.Please, note that in case of Scripts and Workaround the Custom Success Team can deliver limited support.

How did we do?