While database technology and other tools have come a long way, it's still tough to beat the humble spreadsheet's versatility and intuitiveness. While using them for sensitive, critical applications like storing COVID-19 patient data is ill-advised, the fact that everyone knows how to use a spreadsheet means they're great for smaller cross-functional projects where a non-developer might need to examine or edit data. In this guide, I'll show you how to use Google Sheets as a database, complete with an API interface accessible over HTTP. We'll use , a Node.js API development platform and editor with built-in autocomplete, to deploy a simple app and handle Google's authentication process. I'll also explore the limitations of Google Sheets, including scalability, and where it makes sense to start looking at more complex alternatives. Autocode $ curl --request GET --url \ # Returns all people in the database whose names start # with "bil", case-insensitive 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil' [ { : , : , : , : , : }, { : , : , : , : , : }, { : , : , : , : , : } ] "Name" "Bilbo Baggins" "Job" "Burglar" "Fictional" "TRUE" "Born On" "9/21/1937" "Updated At" "" "Name" "Bill Nye" "Job" "Scientist" "Fictional" "FALSE" "Born On" "11/27/1955" "Updated At" "" "Name" "billie eilish" "Job" "Artist" "Fictional" "FALSE" "Born On" "12/18/2001" "Updated At" "" You don't need anything other than a Google account and a free Autocode account to get started. Let's go! TL;DR (30s) First, you'll need to clone your own copy of the template Google Sheet by . This will add the sample spreadsheet to your personal Google account. clicking here, then pressing "Use Template" in the top right Once you've done that, . Poke around the source code if you'd like, then install the app to your Autocode account by pressing the green button. When prompted to link a Google Sheet, follow the instructions in the flow to link your Google account, then select the spreadsheet you just cloned. click here to open the starter app in Autocode After that, your app should be ready to go! Try accessing a few of the endpoints via their URLs and see what is returned/what happens to your new spreadsheet database. You can check out the section below for example calls. Endpoints Limitations That tl;dr was easy, right? So why isn't everything built on Google Sheets? While a backend that takes 30 seconds to set up and that everyone can interact with is extremely appealing, there are some obvious limitations. A spreadsheet as a database as described above doesn't naturally support multiple tables or relationships between rows. There's also no concept of enforcing types for a given column, transactions, built-in backups, or encryption, so sensitive/critical data (like COVID-19 patient data) is probably best stored elsewhere. In terms of scalability, Google Sheets has a (including blank cells). When I tried to verify this by creating a spreadsheet with that many values, however, I encountered significant issues in performance before that threshold: hard limit of 5,000,000 cells Mass-operations like pasting a large number of cells slowed, then began to fail at around 1m cells. Navigation was generally sluggish. My experiments around making API calls yielded similar results. Query speed seemed to scale linearly with the number of cells: Queries became impractically slow around the 500,000 cell mark, but were still below 2 seconds for a 100,000 cell query. Therefore, if you anticipate a dataset larger than a few hundred thousand cells, it would probably be smart to choose a more scalable option. How It Works When you link your cloned Google Sheet to your app and install it to your account, automatically handles authentication between your app and your Google account using your app's token (see the line at the top of all the endpoints). Autocode const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN}) For the actual queries, each endpoint contains Node.js code that calls a method from the . These APIs take a parameter called formatted in that corresponds to the part of the spreadsheet the API call should consider as part of the database. googlesheets.query API range A1 notation queryResult = lib.googlesheets.query[ ].select({ : , : , : [{ : query }] }); let await '@0.3.0' range `A:E` bounds 'FULL_RANGE' where 'Name__istartswith' A value of is essentially shorthand for "use all rows in columns through in the spreadsheet as my database". The query interprets the first row of each column in that range as the field name of the values in that column. Given the template you cloned, the above query will check all values in the column (named ) for rows with a value matching the query. range A:E A E A Name These API calls use the . If you're interested in a deep dive, you can check it out for more examples. KeyQL query language Calling Endpoints As previously mentioned, these endpoints are accessible via HTTP, so you can make calls to them via , , or whatever other HTTP client you prefer. You can use your web browser directly: fetch cURL And you can even use the same Node package that the endpoints use to call the Google Sheets APIs: lib-node Your endpoints will respond to either GET or POST requests. Parameters are parsed from the querystring for GET requests and the request body for POST requests. Each endpoint has default parameters set for the sake of clarity. You can find examples for each endpoint below. Endpoints functions/select/job/contains.js This endpoint is an example of a KeyQL query. It looks for rows in the linked Google Sheet where the field contains a substring (case-sensitive) matching the parameter . From the sample sheet, it returns: contains Job query $ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/job/contains/?query=ist' [ { : , : , : , : , : }, { : , : , : , : , : }, { : , : , : , : , : } ] "Job" "Mistborn" "Born On" "2006-07-17" "Fictional" "TRUE" "Name" "Vin Venture" "Updated At" "" "Job" "Scientist" "Born On" "1955-11-27" "Name" "Bill Nye" "Fictional" "FALSE" "Updated At" "" "Job" "Artist" "Born On" "2001-12-18" "Name" "billie eilish" "Fictional" "FALSE" "Updated At" "" functions/select/born_on/date_gt.js This endpoint is an example of a KeyQL query. It looks for rows in the linked Google Sheet where the field is after the parameter, formatted as . From the sample sheet, it returns: date_gt Born On query 2000/01/01 $ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/born_on/date_gt/?query=2000/01/01' [ { : , : , : , : , : }, { : , : , : , : , : } ] "Job" "Mistborn" "Born On" "2006/07/17" "Fictional" "TRUE" "Name" "Vin Venture" "Updated At" "" "Job" "Artist" "Born On" "2001/12/18" "Name" "billie eilish" "Fictional" "FALSE" "Updated At" "" functions/select/name/istartswith.js This endpoint is an example of a KeyQL query. It looks for rows in the linked Google Sheet where the field starts with the parameter (case-insensitive). From the sample sheet, it returns: istartswith Name query $ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil' [ { : , : , : , : , : }, { : , : , : , : , : }, { : , : , : , : , : } ] "Job" "Scientist" "Born On" "1955-11-27" "Name" "Bill Nye" "Fictional" "FALSE" "Updated At" "" "Job" "Artist" "Born On" "2001-12-18" "Name" "billie eilish" "Fictional" "FALSE" "Updated At" "" "Job" "Burglar" "Born On" "1937-09-21" "Fictional" "TRUE" "Name" "Bilbo Baggins" "Updated At" "" functions/insert.js This endpoint is an example of an insert query. It passes the input parameters into the parameter of the . For example, to add to your spreadsheet, you could make the following request (all parameters are lower-case): fieldsets googlesheets.query.insert API Bill Gates $ curl --request POST \ --header \ --data \ --url "Content-Type: application/json" '{"name":"Bill Gates","job":"CEO","fictional":false,"bornOn":"10/28/1955"}' 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/insert/' Autocode APIs do not respond exclusively one HTTP method over another, and instead rely on descriptive naming to avoid confusion over functionality. Note: functions/update.js This endpoint is an example of an update query. It sets the field of people whose names exactly match the parameter, and updates other fields based on input parameters. It uses the . Updated At name googlesheets.query.update API For example, to update the field of to in your spreadsheet, you could make the following request (all parameters are lower-case): Job Bilbo Baggins Ring Bearer $ curl --request POST \ --header \ --data \ --url "Content-Type: application/json" '{"name":"Bilbo Baggins","job":"Ring Bearer"}' 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/update/' This can affect multiple rows if more than one row matches the query conditions. Note: functions/delete.js This endpoint is an example of a delete query. It removes rows of people whose names exactly match the parameter. It uses the . For example, to remove from your spreadsheet, you could make the following request: name googlesheets.query.delete API Bilbo Baggins $ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/delete/?name=Bilbo%20Baggins' This can affect multiple rows if more than one row matches the query conditions. Note: Thank You! If you have any questions or feedback, the best thing to do is to join the Autocode community Slack channel. You can get an invite from the tab in the top bar on . You can also reach out to me directly on Twitter, . Community the website @Hacubu If you want to stay up to date on the latest from Autocode, you can follow . Happy hacking! @AutocodeHQ
