Google Spreadsheet as a Database.
In the past, I often asked by non-technical colleagues to do some DB scripts jobs to mapping their spreadsheets data to the production database. And when their data changes the same work needs to be done again. Since these data are not changed too frequently(compared with other online data), it's also not worth to make a content management system for them.
But why don't make their spreadsheets as a real production database? That's what "Single source of truth" means. What's more, you even could write back some statistical data like "Page View" to the spreadsheets, so they could see the feedback clearly and continue to optimize the content.
- Create a Google Spreadsheet and populate the first row with the columns names, here is an Example Sheet.
- Create a Google Cloud Service Account and download the JSON file that contains your key.
- Find your service account email in credentials console which similar with
- Share your sheets to the above email, and make sure you have assigned it as an editor.
db means the Google Spreadsheet ID. You can find it in your sheet's URL:
table means the Sheet Name in your Spreadsheet. The default is
Every data in sheetsql will be set/get as a string. You need to handle the type mapping on your side.
Your service account JSON key file.
npm i sheetsql -S
// load schema and data from google spreadsheetawait db.load// insert multiple documents// find documents and update themdocs = await db.update,,// find documentsdocs = await db.find// find all documentsdocs = await db.find// find documents and remove themdocs = await db.remove
Using a Proxy
sheetsql depend on
googleapis lib in which you can set the following environment variables to proxy http/https requests:
The two environment variables could let your all requests using the proxy. If that is not your expected behavior and you only need to proxy google APIs, set
Here is the discuss.