Dialogflow 16 – Connecting your agent to Google Sheets

Dialogflow 16 – Connecting your agent to Google Sheets

Hi,

Sometimes having an external database can be overwhelming in a small setup. For an education project I was asked to connect a Dialogflow virtual agent with a spreadsheet, so it contains information that we will provide to the users.

If you use Google Sheets, there is an API that allows you to access data in spreadsheets, and we can access it from our agent’s fulfilment.

The first step is to create or identify an existing spreadsheet using its URL. The ID is the bunch of characters after the  /d/ :

To access this document you need to authorize the API … there are 2 methods that are documented here. In this example I’m going to use the API key option, which is the easiest way, and also the less secure, as you have to write down that key in the fulfillment code.

To create the API key you have to go to the GCP console in the project that you use to run the Dialogflow agent, looking for the APIs and then Credentials menu. You can create a new Service Account or use the one that should be already there for “Dialogflow Integrations”:

Selecting that account you can create a new key using the JSON format, and it will be downloaded to your local device:

You also need to enable the Sheets API in the GCP project. Look for it on the top search bar and enable it:

The next step is granting permissions to the service account, so it can access the spreadsheet:

We are done with the setup, now let’s go for the code:

You need to add a new library to your dependencies, as we are going to use the Google APIs to access Sheets. Edit your package.json and add the last line you can see here:

In the cloud function code (index.js) you also need to add the matching “require”:

     const {google} = require(‘googleapis’);

Now you can build a new function that will perform the reading of a cell given a row and column:

In this snippet SheetDocument is the ID of the document that we found in its URL, and SheetName is the name of the Sheet, that defaults to Sheet1, but can be anything. You need to add a trailing “!”.

The next section contains the fields of the API key that we have downloaded in the JSON file, and we only need 2 of them, the client_email and private_key, that you need to add to the code like this:

Next step is to initialize the authentication and connect to Sheets:

And finally the code to perform the read, where we need to manage promises:

You can see that the read is done accessing a Range, defined in this case as SheetName+Column+Row … but you could also access a bigger range.

To call this function, in a very simple example, you can do:

In most cases the row and column will be parameters, or based on parameters that you captured in the intent, might be with a case clause to do the translation, but this is up to you. With this, access to the spreadsheet should work, and I recommend that you test before making it more complex.

Remember that logs are your friends … in this case they were warming me that the API was not enable, or that it takes some minutes to start working, and I can tell you that it does take some minutes 😀

Regards

Jamarmu

============================================================

I work for Google Cloud, but this post are personal ideas and opinions

Leave a Reply