Home / DialogFlow ES / How to use Google Sheets with your Dialogflow ES bot
DialogFlow ES

How to use Google Sheets with your Dialogflow ES bot

First published: Feb 2022 | Last updated: May 2022

It isn’t easy to use Google Sheets as the database for your Dialogflow bot.

Personally, I don't think it is a good idea to use a spreadsheet software like Google Sheets or Microsoft Excel as the database for your Dialogflow bot. A tool like Airtable is better. 

But it is a lot easier if you use a tool like sheet.best to convert your Google Sheet into a REST API.

The rest of this article is based on my Dialogflow ES beginner tutorial. You will need to go through it first to be able to follow this tutorial.

First create a Google Sheet and populate it with data.

Notice that the first row has the column headers for the table.

This is necessary to convert your Google Sheet into an API

Also notice that the Value column does not have real values – I just populated them with fake values where the first digit is the planet number (in order) and the second digit represents the attribute (1=mass, 2=color, 3=volume).

Now log in to your Google account from the sheet.best website.

It is a good idea to use a different Google account which is dedicated to your Dialogflow bot. 

While I believe the third party companies know what they are doing, if they do access sheets which you did not add to their API even by accident, then at least it will not spill over to your other Google Sheets you might want to keep private. 

Click on the Add new connection button

Paste the URL of the Google Sheet into the Connection URL textbox

After you click on Connect button, you will see a page with some details about your connection. Click on the Details link.

You will see your API connection URL

In the Advanced Settings section, you can see the API Key

So now you will be able to use the sheet.best API to use your Google Sheet like a database.

You can use the programming language of your choice to now use this API for your Dialogflow bot.

In our case, we will start with the PlanetsBot Entity version 2.

Now import the above agent ZIP file into your Dialogflow ES console.

Update the attribute.of.planet as below

Create an intent called set.planet.attribute as below

This is the code which handles getting the attribute of the planet

def get_planet_attribute(req):
    query_result = req.get('queryResult')
    planet = query_result.get('parameters').get('planet')
    attribute = query_result.get('parameters').get('attribute')
    url = f'https://sheet.best/api/sheets/{SHEET_BEST_ID}/search?Planet={planet}&Attribute={attribute}'
    result = requests.get(
        url,
        headers={
            'X-Api-Key': SHEET_BEST_API_KEY
        }
    )

    json_result = result.json()
    value = json_result[0].get('Value')
    result = {
        "fulfillmentText": f'The {attribute} of {planet} is {value}'
    }
    return result

This is the code which handles setting the attribute of the planet. To simplify things, I am just assigning a numerical value for all attributes even though that doesn’t make sense for the Color attribute.

def set_planet_attribute(req):
    query_result = req.get('queryResult')
    planet = query_result.get('parameters').get('planet')
    attribute = query_result.get('parameters').get('attribute')
    value = query_result.get('parameters').get('value')
    url = f'https://sheet.best/api/sheets/{SHEET_BEST_ID}/search?Planet={planet}&Attribute={attribute}'
    requests.patch(
        url,
        json={
            'Value': value,
        },
        headers={
            'X-Api-Key': SHEET_BEST_API_KEY
        }
    )
    result = {
        "fulfillmentText": f'The {attribute} of {planet} has been updated to {value}'
    }
    return result

Demo

We can check the Google Sheet to verify that the value has indeed been updated

Why not use the Google Sheets API directly?

In case you are wondering why you need to use a third party paid tool like sheet.best instead of using the Google Sheets API directly, the simple answer is that Google Sheets API gives you an API for accessing random spreadsheet content, while sheet.best gives you an API for accessing tabular (relational) data. This makes it much easier for you to write your webhook code.

Also, my recommendation is to use Airtable instead of Google Sheets if it makes sense for your use case.

<— End of article —>


This website contains affiliate links. See the disclosure page for more details. 
"The magic key I needed as a non-programmer"

The custom payload generator was the magic key I needed (as a non-programmer) to build a good demo with rich responses in DialogFlow Messenger. I've only used it for 30 minutes and am thrilled. I've spent hours trying to figure out some of the intricacies of DialogFlow on my own. Over and over, I kept coming back to Aravind's tutorials available on-line. I trust the other functionalities I learn to use in the app will save me additional time and heartburn.

- Kathleen R
Cofounder, gathrHealth
"Much clearer than the official documentation to be honest"

Thanks a lot for the advice (of buying and following your videos)! They helped a lot indeed. Everything is very clear when you explain, much clearer than the official documentation to be honest 🙂

Neuraz T
Review for Learn Dialogflow CX
"I will strongly recommend this course because even I can learn how to design chatbot (no programming background)"

I think Aravind really did a great job to introduce dialogflow to people like me, without programming background. He organizes his course in very clear manner since I have been a college professor for 20 years. It is very easy for me to recognize how great Aravind’s course is! Very use-friend and very easy to follow. He doesn’t have any strong accent when he gives the lectures. It is so easy for me to understand. Really appreciate it.

Yes, I will strongly recommend this course because even I can learn how to design chatbot (no programming background) after studying Avarind’s course, you definitely can!

Ann Cai
Review for Learn Dialogflow ES

Similar Posts