How to Use Google Sheets as a Database in 30 Seconds
This Autocode app provides an example of how to use Google Sheets as a database, completely accessible as an API! All the
endpoints are accessible via HTTP and contain sample code for inserting, updating, deleting, and querying for rows
using various methods from the googlesheets.query
API from Autocode's Standard Library. Deploy your own working version of this
app in just a few clicks, then fork it to a sandbox to
customize it however you'd like and use it as inspiration for your own use cases.
# Returns all people in the database whose names start with "bil", case-insensitive
$ curl --request GET --url \
'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": ""
}
]
TL;DR (30s)
Before pressing the install button, you'll need to clone your own copy of the
template Google Sheet by clicking here, then pressing Use Template in the top right
to add the spreadsheet to your own Google account.
Once you've done that, install the app to your Autocode account by pressing the
green button above. 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.
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 Calling Endpoints section below for example calls.
How It Works
When you link your cloned Google Sheet to your app and install it to your account, Autocode
automatically handles authentication between your app and your Google account
using your app's token (see the const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN})
line at the top of all the endpoints).
For the actual queries, each endpoint calls a method from the
googlesheets.query API. These APIs take a
parameter called range formatted in A1 notation that corresponds to
the part of the spreadsheet the API call should consider as part of the database.
let queryResult = await lib.googlesheets.query['@0.3.0'].select({
range: `A:E`,
bounds: 'FULL_RANGE',
where: [{
'Name__istartswith': query
}]
});
A range value of A:E is essentially shorthand for "use all rows in
columns A through E 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 A
column (named Name) for rows with a value matching the query.
These API calls use the KeyQL query language.
If you're interested in a deep dive, you can check it out for more examples.
Calling Endpoints
As previously mentioned, these endpoints are accessible via HTTP, so you can make
calls to them via fetch, cURL, or whatever other HTTP client you prefer.
You can use your web browser directly:
And you can even use the same lib Node package
that the endpoints use to call the Google Sheets APIs:
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 contains KeyQL query. It looks for rows in
the linked Google Sheet where the Job field contains a substring
(case-sensitive) matching the parameter query. From the sample sheet, it returns:
$ 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 date_gt KeyQL query. It looks for rows in
the linked Google Sheet where the Born On field is after the query parameter,
formatted as 2000/01/01. From the sample sheet, it returns:
$ 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 an istartswith KeyQL query. It looks for rows in
the linked Google Sheet where the Name field starts with the query parameter
(case-insensitive). From the sample sheet, it returns:
$ 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 fieldsets parameter of the googlesheets.query.insert
API. For example, to add Bill Gates to your spreadsheet, you could make the following
request (all parameters are lower-case):
$ curl --request POST \
--header "Content-Type: application/json" \
--data '{"name":"Bill Gates","job":"CEO","fictional":false,"bornOn":"10/28/1955"}' \
--url 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/insert/'
Note: Autocode APIs do not respond exclusively one HTTP method over another, and instead
rely on descriptive naming to avoid confusion over functionality.
functions/update.js
This endpoint is an example of an update query. It sets the Updated At field
of people whose names exactly match the name parameter, and updates other fields based on input parameters. It uses the
googlesheets.query.update API.
For example, to update the Job field of Bilbo Baggins to Ring Bearer in your spreadsheet, you could make the following
request (all parameters are lower-case):
$ curl --request POST \
--header "Content-Type: application/json" \
--data '{"name":"Bilbo Baggins","job":"Ring Bearer"}' \
--url 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/update/'
Note: This can affect multiple rows if more than one row matches the query conditions.
functions/delete.js
This endpoint is an example of a delete query. It removes rows
of people whose names exactly match the name parameter. It uses the
googlesheets.query.delete API.
For example, to remove Bilbo Baggins from your spreadsheet, you could make the following
request:
$ curl --request GET --url \
'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/delete/?name=Bilbo%20Baggins'
Note: This can affect multiple rows if more than one row matches the query conditions.
Thank You!
If you have any questions or feedback, the best thing to do is to join our community Slack
channel. You can get an invite from the Community tab in the top bar.
If you want to stay up to date on the latest from Autocode, you can also follow
us at @AutocodeHQ. Happy hacking!
