17 How to use Google Sheets with your Dialogflow ES bot

Byaravindmc

Dialogflow ES Quickstart Templates Part 2

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.

Download agent ZIP file

About this website

I created this website to provide training and tools for non-programmers who are building Dialogflow chatbots. 

I have now changed my focus to Vertex AI Search, which I think is a natural evolution from chatbots. 

Note 

BotFlo was previously called MiningBusinessData. That is why you see that watermark in many of my previous videos.

Leave a Reply