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
where can we get this sample code for calling trix and reading cell values and checking matching the use utternace
You have the code snippets in the images of the post
I tried running the function but function is getting crashed and not even hitting the api or cloud function getting triggered Please suggest what might be the issue.
Hi,
Go and check the logs … if it is being triggered check the Cloud Functions logs. If it is not even triggered check the Dialogflow logs for this conversation:
– History, pick the conversation, find the interaction calling the webhook and with the 3 dots menu open the “raw interaction logs”:
The most common issue is a permission problem … check this article for the options to authenticate against your Cloud Function:
https://cloud.google.com/dialogflow/es/docs/fulfillment-webhook#gcf
Regards
Jamarmu
Thanks a lot for the prompt response.
I see the error as in the history is
“webhookStatus”: {
“webhookStatus”: {
“code”: 14,
“message”: “Webhook call failed. Error: UNAVAILABLE.”
},
“webhookUsed”: true
and in the cloud logs
Unhandled rejection
Error: Unable to parse range: Unique domains summary!A1:A4 at Gaxios._request (/workspace/node_modules/googleapis/node_modules/gaxios/build/src/gaxios.js:85:23) at process._tickCallback (internal/process/next_tick.js:68:7)
Error: Unable to parse range: Unique domains summary!A1:A4 at Gaxios._request (/workspace/node_modules/googleapis/node_modules/gaxios/build/src/gaxios.js:85:23) at process._tickCallback (internal/process/next_tick.js:68:7)
As i am new to this fulfillment code struggling out
i deployed the function as webhook.
Hi, thanks for the guide on how to connect dialogflow and google sheet. This is very useful
Would like to ask a question,
I want the chatbot to retrieve a cell value from Google Sheet and reply to a user when the intent is triggered.
But sometime, the function will take more than 4500ms to run and this causes the reply not able to send out to user.
It will happen especially after a period (roughly 20-30 mins ) the function has run. Means, once the function run one time, then within 20-30mins, chatbot able to get data from Google sheet and send to user.
But after 20-30 mins, it looks like need to re-connect and it will take more time to run. Then, result in not able to send reply to user.
Could you advice me what should i do to overcome this situation?
Thank you very much!
-Jiun
Hi Jiun,
This can happen due to the warming up time of the Cloud Functions, and depending on the code it would need to execute.
I suggest to options 2 solve this:
1- Call the fulfilment at an earlier moment of the conversation, so the functions warms up before actually doing the integration. It can be in the welcome message or any interaction that is close to the failing intent. If you just deliver a pre-written answer from that fulfilment branch it will work fine.
2- Move your code to another service, like Cloud-Run, where you can configure to have some instances running (scale down) … the drawback here is the increases cost to run your service.
Regards
Javier