Recently I discovered the service Sheetsu which turns your Google Sheets into an API. (Note: I am not affiliated with Sheetsu. I am writing this article because I have received quite a few requests asking this same question).
It is a SaaS service, meaning you pay $39/month if you want to use it to save data. However, they do have a free plan which you can use to "test the waters".
How it works
1 Take your existing Google Sheets URL
2 Paste it into the Sheetsu service's API generator
3 You now have an API endpoint where you can do the usual CRUD operations - create, read, update and delete data
4 Now call the Sheetsu API from your Dialogflow webhook to do the appropriate data operations
The alternative: coding it yourself
You will need to do a few things to be able to use your Google Sheets as a DB for your agent if you were to build out the integration by yourself.
1 Code the integration
Suppose you take the easiest option and choose to code the integration in Dialogflow's inline editor. The inline editor is still not a real IDE, meaning maintaining the code will not be easy if your code gets complex. (Generally speaking, doing data access from your code is somewhat complex)
2 Muck around with service account credentials
You will likely use a service account to be able to access your Google Sheets, which means you need to go into the Google Cloud console and download the secret key JSON file. With Sheetsu, you can skip that step. Once you download the secret key JSON file, you must treat it like any other password, and make sure you actually keep the JSON file a secret.
3 You need to write your own data access methods
That is, Sheetsu provides a nice API which makes it quite easy to do the usual database operations. When you build the code yourself, you need to write (and thus maintain) this data access code yourself.
Is it worth the monthly price?
While you are the best judge of whether the service is worth the monthly price, there are a few factors to consider
1 Developing the same capabilities in-house will definitely require a fairly good investment of your time/money like I have explained
2 If you already use Google Sheets heavily in your business, and would like to get a prototype chatbot up and running, Sheetsu can help you do that quickly
On the flip side, once you start using it, you do have a sort of lock-in and may not be able to detach from their service that easily. It can be done, but it will take a fair amount of effort to implement the rewrite.
What would be a better option?
The option doesn't exist, to the best of my knowledge.
If Google were to
- create a service similar to Sheetsu (i.e. paste Google Sheets URL -> get API access)
- provide a simple client library which you can add as a dependency into the package.json of your inline editor
- and the client library has an easy to use REST API similar to Sheetsu
that would make life much simpler for folks who need this functionality. 🙂
An alternative to Google Sheets
June 2019: Recently, I discovered an alternative to Google Sheets which is much easier to work with as a database, because it was built from the ground up to be a combination of a spreadsheet and a database: Airtable.
I have written a detailed tutorial which explains end-to-end how to use Airtable as a backend for your Dialogflow bot. (And as a bonus, it can also be used as a reference guide for non-programmers to learn the basics of Dialogflow).