How to bring Google Sheets data in your Tars Chatbots

Every so often, we like to experiment with the TARS builder to create new uses, hacks and improvements that you can use in your bots. Frequently these experiments fail miserably. We build something and we think to ourselves “this works, but why would anyone use this.”

Other times however, we find a gem that is worth sharing with the bot maker community. This article is one such example.

As you likely know, TARS bots have the capability of interacting with a variety of other applications through the API integration feature. Generally, the feature gets used in one of two ways :

1. First, people like pull data from third party apis, for example weather data in from the Weather Channel, GIFs from Giphy, videos from Youtube etc.
2. Second, a lot of bot makers like to send information they collect from users (e.g. email, phone number name) to their service of choice. So this could be sending service queries to a slack channel from your team or maybe collecting leads in a Google Sheet.

Today, however, we are going to do something different. We will pull information from a Google Sheet to show each user something unique.

The Example

To make things a little more concrete, consider the following spreadsheet. It contains user data that we have collected: user’s name, birth year, age and a unique id number.

We will create a bot, that takes an id number and using this fetches the corresponding birth year and age to show to the user:

You can check out the sheet over here, and the bot over here.

Google Sheet Setup

First up, let’s setup the google sheet. Open it up and click the share button in the top right:

Set link sharing on and make sure that anyone with the link can view the sheet.

Bot Setup

Next we will set up the bot. My bot consists of just 4 gambits:

In the first gambit I ask the user what their id number is. We will use the user response in this gambit to find the correct data in the sheet:

Be sure to name the gambit something that is easy to remember and refer to later. It will be required when checking the sheet in the next gambit. In this example, I have named the gambit ID because we are going to be identifying what data to pull from the sheet based on the ID:

Next we will need to create a second gambit, where we actually make the api call to the google sheet. Rename it something recognisable (I called mine fetch, because we will be fetching the data from the google sheet in this gambit), and turn on API Config:

Connect the two gambits together

Setting up the API call

Now we will need to setup the API call where we actually fetch the data. For this, you will first need to copy the share URL of the google sheet. Copy the url from the sheet.

It will look like this:

https://docs.google.com/spreadsheets/d/1oYSXgwArpfhC9klUj6jOz2vMsRHdtQJj5I65_Oq0WPQ/edit?usp=sharing

Remove the “edit?usp=sharing” part of the url from the end so that it looks like this:

https://docs.google.com/spreadsheets/d/1oYSXgwArpfhC9klUj6jOz2vMsRHdtQJj5I65_Oq0WPQ/

Add gviz/tq to the end of the url so it looks like this:

https://docs.google.com/spreadsheets/d/1oYSXgwArpfhC9klUj6jOz2vMsRHdtQJj5I65_Oq0WPQ/gviz/tq

Paste this url into the api config of the gambit we created above and change the mode from POST to GET:

Then add tq to the first key:

In the value feed, we will have to type in the command to tell Google Sheets what data we want to select. This will look familiar to anyone who has worked with SQL before.

In this example the command looks like this:

SELECT A,B,C,D WHERE A=<specified value>

Essentially what we are saying is we want to pull the values in columns A,B,C D from the row in the spreadsheet where A is a specified value. The A column contains the IDs, so we are essentially selecting which row to pull from the sheet given a specified ID.

So if we replaced <specified value> with 123 to make this command:

SELECT A,B,C,D WHERE A=123

We would select this row:

If we replace <specified value> with 789 to make this command:

SELECT A,B,C,D WHERE A=789

We would select this row:

Similarly, you can change the columns selected.

So, if you were to adjust the command to SELECT B,C,D WHERE A=123 (Note: we have changed the columns from A,B,C,D we would only retrieve the following data as we have adjusted the command to select only columns B,C,D:

Copy and paste SELECT A,B,C,D WHERE A=123 into the value field next to tq in the builder:

You can send the request now to test and see if it is working. This is what it should look like:

PLEASE NOTE!

If you are using one of the other fields that is a string (for non-programmers this just means a word) and is not a number you need to add single quotes around the specified value.

For example, imagine if we were choosing the rows we want by name instead of ID. Our command would look like this:

SELECT A,B,C,D WHERE B=<specified value>

(If you can’t see the difference, we have changed WHERE A=<specified value> to WHERE B=<specified value> so that we are checking name instead of id)

In this case, we would put the <specified value> in single quotes. So if we wanted to select the row with John in it your command would look like this:

SELECT A,B,C,D WHERE B=‘John’

NOT

SELECT A,B,C,D WHERE B=John

Now if the command is working, we want to set up the rest of the bot so that it can process the API response. Go ahead and copy the API response from the API call gambit somewhere. You can use a site like http://jsbeautifier.org/ to make it look pretty and easy to understand like this:

Create another gambit and name it something apt. Over here I have named it process_and_show because we are processing and showing data from our API call:

Connect it with the fetch gambit where we make the API call.

Now let us look at the data we fetched from the sheet and say something like:

Hi John!

You are 21 years old

For this we will need to use the data referencing feature of Tars bots. In case you do not know what this is, data referencing means we can access and use information collected elsewhere in a bot to do interesting things.

In this example, we want to access the information in the API response that we got from the fetch gambit and show it to the user in the messaging flow. In other words, we want to be able to show information from this stuff we copied earlier. As you can see, this is all the information we require (the name and the age):

So how do we access this information? To do this we will have to construct a statement that looks like this:

{{pre.fetch.table.rows[0].c[1].v}}

And put it into the messaging flow:

We know it looks super complicated, but don’t worry, it’s pretty easy once you use it for the first time. The statement above, by the way, refers to the name John from the API response we copied earlier.

Constructing the data reference statement

First of all, any data referencing happens in double braces so we start by putting the double braces in the message bubble:

Now we will add pre.fetch within these double braces:

pre.fetch tells our bot that we will be looking at the API response from the fetch gambit. The pre indicates that we are looking at the part of the gambit before the messages (i.e. pre-messages) and fetch indicates the name of the gambit.

This is an important milestone. The statement we have so far {{pre.fetch}} is pointing to the information that we copied before:

So now all we need to do is point to the correct part of this mess. To do this, you need to understand a little bit about JSONs. A JSON is just a fancy word for the mess of curly braces in the picture above. It is basically made of keys and values. Check out the following example:

The keys in this example are a and b and the values are 6 and 7.

Of course values do not have to be numbers. They can be words:

Lists:

And even other JSONs:

To access point to specific values within a JSON you simply use dots.

So in the picture above, if the JSON is called x:

if we want to point to hello (the value of a) you would use x.a.

if you wanted to point to the 8 (value of d) you would use x.c.d.

What about lists? Like what if we want to point to the 9 in the list at e. To point to specific values in a list, you use square brackets and the index of the value you want. So to point at 9 you would first point to the list:

x.c.e

And then add the index in square brackets:

x.c.e[0]

NOTE: For lists, the first element has index 0, second element has index 1 etc.

The data we got from the api call is a giant messy JSON:

We want to access the name and age:

For this, we will first add the term table:

This will tell the bot to look at the value of table in the JSON, which is another JSON containing the rows and columns:

Within this we notice that the necessary information can be found in the rows value so we add rows to the statement:

Now we are pointing to this JSON over here:

Zooming into the value of rows we can see that it is a list. We can tell because of the square brackets surrounding the value:

This is a list of all the rows that met the conditions we set in our command, SELECT A,B,C,D WHERE A=123. So basically up here the list contains JSONs representing all rows row which had the id 123. Since there was only one such row, the list has only one element, a JSON that looks like this:

As such we will add index 0 to be pointing to this row:

This JSON has just one key called c which contains all of the values as a list. As such we will add the c to our statement:

Now within the list c we can access all of the values. Each value is, you guessed it, another JSON. At index 0 we have the id, JSON:

At index 1 we have the name:

At index 2, the birth year:

And at index 2, the age:

We want the name and age, so let’s create two statements one referring to index 1 and one to index 3:

Within each of these, you will notice a key called v that contains the necessary value:

So we add v to each of our statements:

Congratulations! We have successfully constructed our data referencing statements! The first one refers to the name and the second one to age.

Now we can add some boilerplate text so that these statements make sense:

And now it is time to finish the bot up. After the bot tells the user this information, lets have it end the conversation:

And your bot is done! Right?

Wrong.

The statement we put into the API config earlier, refers to a specific value of ID:

This means that no matter what id the user inputs, the bot will spit out the name John and the age 21. For example, in the demo below, we give the ID 789 which should give us Abdullah and 22, but it gives us John and 21:

To fix this we must change the command from:

SELECT A,B,C,D WHERE A=123

TO

SELECT A,B,C,D WHERE A={{ursp.ID}}

By changing 123 to {{ursp.ID}} we are telling the bot to NOT select the row with id 123 every time but rather to select a row with the id that a user asks for in the ID gambit. Just like when we used data from the API response, we are using the data referencing feature. Instead of pre, we use ursp meaning user response and we add the gambit name in the same way, .gambit_name in this case .ID

Now we are done right?

NO!!

If the user gives an id that does not exist, then the bot fails:

Fixing this Problem

To fix this problem we need to understand first what happens when you query google sheets with an invalid id. Test it out in the builder. I changed the statement from SELECT A,B,C,D WHERE A={{ursp.ID}} to SELECT A,B,C,D WHERE A=234234 and then hit Send

In the results you can see that the rows list is empty unlike before:

NOTE: If you tested out this error like I did, be sure to change your test value in the SELECT statement back to {{ursp.ID}}

We can use the Conditional Jump feature to check for this. If you do not know what that is, check out our help doc over here.

Open up the gambit where you make the API call and turn on conditional jump at the bottom:

Add a branch where we can check if the rows list is empty:

So how do we figure out if the rows list is empty in this conditional branch? My solution is that we check to see whether the 0 index of the rows list exists. If it doesn’t exist, then we know the list is empty and can give the user an error message and ask them for the id again. To do this, copy {{pre.fetch.table.rows[0]}} into the field:

And change the condition to Is Unknown in the dropdown:

Now your bot flow should look something like this:

The first socket on the left fetch gambit refers to branch we just made. So if the first branch does get activated, we want to show the user an error message and ask them for the id again. Go ahead and create another gambit and fill it up with an error message:

Rename the gambit ID. IT IS VERY IMPORTANT that you name this gambit the same name as the original gambit in which you ask for the ID. This is because in our SELECT statement, we refer to {{ursp.ID}}:

And connect the first one to the first socket on the fetch gambit:

When a user gives an ID in this new gambit, we want to send that id to the google sheet again. So go ahead and connect this new gambit to the top of the fetch gambit as well:

The second socket on the fetch gambit represents the default branch. This means that if the bot checks the conditional branch and sees that the rows list we got from the Google Sheet is not empty it will send the user down the default branch. In other words, this is the case where the user has given a valid ID. As such, we can connect this socket to the “process and show” gambit where we can now be sure that the bot will display correct looking messages.

NOW, YOUR BOT IS WORKING AS IT SHOULD.

You can check out my working example here.

Why is this significant?

Now you must be wondering why anyone would want to go through all of this effort to use this feature.

Essentially, this feature allows you to use Google Sheets as a database for your business using Tars bots.

Think of a service request bot, which takes user queries adding them to a Google Sheet.

• Users can then use their service ticket number to come back and check the status of their query.
• Or think of a lead generation bot that can fetch user information to autofill a lead generation bot. The use cases are limitless.

Check out this bot we created, for a fictitious content creation company!

https://upscri.be/973f3c/