While I am using Google Sheets as the primary example here, nearly everything I mention is equally applicable to Microsoft Office/Microsoft Excel REST APIs. Since people are often using Google Sheets to store their data, it would be very convenient to use Google Sheets itself as the backend for their Dialogflow bot.
But I would recommend people use Airtable over a spreadsheet app.
Spreadsheets can support non-tabular data
Data which can be organized perfectly into rows and columns can be referred to as tabular data. At first glance, a spreadsheet looks like simple tabular data. (And it often is).
But you can combine multiple cells into a single cell in Sheets/Excel. This means spreadsheets are designed to inherently support non-tabular information.
While this is obviously a useful feature, it can be a fairly problematic issue when you try to use a spreadsheet as a database.
Column specific data types
While you can have data type validation in Google Sheets, it isn’t column based but rather range based. In practice, this means it is more complex to specify, and more complex to manage over time.
Airtable on the other hand will force you to specify the data type for each column. From a no code perspective, this is just much easier to manage.
What’s the big deal, you ask? Why not just go with spreadsheet software? And why is specifying the data type so important? Why should the software help you enforce this data type, and preferably reject it when it is in the wrong format?
After all, what is the worst possible thing that could happen?
Spreadsheet APIs are more complex
Since the spreadsheet must support non-tabular data, creating a native API for a spreadsheet is actually much harder. And they are inherently more complex to use.
That is why there are paid tools which can turn your Google Sheets into a REST API. While that is certainly an improvement over the existing Google Sheets API, it doesn’t natively support a lot of things that Airtable can – such as Select queries (explained later).
You would frequently need to issue SQL SELECT like queries when fetching information from your database. Since Airtable is designed from the ground up to act like a database, they have pretty good support for this feature. (Read: how to filter by formula using Airtable API)
Even if it is not as intuitive as an actual SELECT statement in SQL, issuing select queries is much easier in Airtable than with the Google Sheets API.
Since spreadsheets support non-tabular data, there are also quite a lot of API functions which are designed to accommodate that. As a result, you need a far greater understanding of concepts like range, span etc. to be able to use spreadsheet APIs.
With Airtable, you can just use the name of the table along with the name of the column, and achieve most of these tasks in the API.
Google is building its own Airtable clone
Also, it is worth noting that both Google and Microsoft are trying to build Airtable clones.
In 2020, both Google and Microsoft made announcements that they are building products which look exactly like Airtable. 🙂 That is the surest sign that you should prefer Airtable for app development – at least until the clones become as powerful as Airtable.
I will also address the common complaint about Airtable – that it provides far too little capacity (max 50000 rows per base on the most expensive non-enterprise plan). You will almost certainly not hit this capacity for most Dialogflow bots, and if you think you will, Google Sheets or Excel is probably not a viable alternative anyway for the reasons I have provided here.
In fact, I would suggest you try to build a prototype of your Dialogflow bot using Airtable’s free plan. You will be surprised how far you can go with it. 🙂
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