Hi there, lets start with integrating a Google Sheet into a Tars Chatbot.

Things you need to have before you can do this exercise:

  1. A Chatbot
  2. A Google Sheet with some data

You can find a video explaining the same here:

Google Sheet

  1. Create a google sheet with some sample data or make a copy of the below sheet https://docs.google.com/spreadsheets/d/1U7dypgSUA91Aey0q3Tu8imofTzCBtCJKFOZ8_U3nkxw/edit?usp=sharing
  2. Open the sheet and click on the share button. And choose "Anyone with the link: Can View" Copy the link.

That's it. Now switch to your chatbot.

Chatbot

Step 1: Lets start by creating a gambit with no display text and no input UI and lets name it as "intro".

Step 2: Create another gambit and this will be of type API CONFIG and let's name it googlesheets_call

  1. Remove the display text "You just write the words, I'll do all the talking :D" and set the Input type to No Input.
  2. Set the Method: GET
  3. Endpoint URL: Paste the link that you copied from your google sheet. And you will have to make one change
    1. The URL Copied may look like this: https://docs.google.com/spreadsheets/d/1U7dypgSUA91Aey0q3Tu8imofTzCBtCJKFOZ8_U3nkxw/edit?usp=sharing
    2. Replace the last part of that, that is, /edit?usp=sharing to /gviz/tq, the copied URL must look like the below after replacing the last part. **https://docs.google.com/spreadsheets/d/1U7dypgSUA91Aey0q3Tu8imofTzCBtCJKFOZ8_U3nkxw**/gviz/tq
  4. Parameters (name-value pair)
    1. sheet: <Name of the sheet, you can find this at the bottom of the sheet> if you are using the sample sheet provided, the name of the sheet is "Class Data"

    2. tq: <this is where you can pass a query>, if you do not know what a query is just copy this "select A". NOTE: A is case-sensitive (it has to be uppercase)

Your setup should look exactly like the below image.

Once you are confident that you have set it up as suggested, hit the send button. You should see a response similar to the below image.

Voila, you have received the data from the google sheet into your chatbot, next step will assist you to read this data and display it.

Step 3: Create a new gambit, and name it "output", we now have called the API in a different gambit and we intend to show the results from "googlesheets_call" in gambit "output".

  1. Replace the existing "You just write the words, I'll do all the talking :D" to "The below are the names from the google sheet." and instead of Text Input as Input type, click on text input (dropdown) and select Buttons.

2. Hover your mouse next to the button and you will find "Add more" click on it and select "Data Iteration" and you should see a new box created on top of "Option Text" (like the below image)

3. In the new box add this line "pre.googlesheets_api.table.rows" What it does is basically accesses the "googlesheets_api" gambit's "response" (aka the google sheet records) and then navigates to the rows part which contains the data we seek.

4. And replace "Option Text" with "{{c[0].v}}", we are trying to access the information at the first column (counting in programming starts at 0(zero), so '0' here means the first column) and we want to access the value "v".

5. programmatically we are saying pre.googlesheets_api access the googlesheets_api gambit's API response, .table.rows run a loop for the key "rows" inside "table" and {{c[0].v}} display the value(v) of the first column(c[0]). (your gambit must look like the below image)

and If you have strictly followed this walk-through and created your chatbot and added gambits from scratch, your chatbot would look like the below image (ignore it if you just integrating in your pre-existing chatbot)

Hit the preview button and you should see the below result.

And that concludes the Google Sheet Integration. Cheers.

Did this answer your question?