Use Google Drive spreadsheets as a simple database for Node.js and the browser. Perfect for collaboration with multiple people editing the same spreadsheet:
; // Load the data from the Drive Spreadsheetasync { const db = await ; console;};
id | firstname | lastname | age | city |
---|---|---|---|---|
1 | John | Smith | 34 | San Francisco |
2 | Merry | Johnson | 19 | Tokyo |
3 | Peter | Williams | 45 | London |
Becomes an array of objects with the corresponding keys:
Getting Started
Create the Google Drive spreadsheet and publish it:
-
Create a Google Spreadsheet
-
File > Publish to the Web > Publish
-
Copy the id between
/spreadsheets/
and/edit
in the url:https://docs.google.com/spreadsheets/d/1fvz34wY6phWDJsuIneqvOoZRPfo6CfJyPg1BYgHt59k/edit
Now you can either add the CDN <script>
or use a bundler. For the CDN:
Otherwise install drive-db
in your project:
npm install drive-db
And then load the spreadsheet into your project:
// Include the module and tell it which spreadsheet to useconst drive = ; // Create an async context to be able to call `await`async { // Load the data from the Drive Spreadsheet const db = await ; console;};
The table has to have a structure similar to this, where the first row are the alphanumeric field names:
id | firstname | lastname | age | city |
---|---|---|---|---|
1 | John | Smith | 34 | San Francisco |
2 | Merry | Johnson | 19 | Tokyo |
3 | Peter | Williams | 45 | London |
See this document as an example. Please do not request access to edit it.
API
You import a single default export depending on your configuration:
// For ES7 modules; // For common.js importsconst drive = ;
To retrieve the data call it and await for the promise it returns:
// With async/await:const db = await ;const db = await ;console; // Use the callback syntax:;;
SHEET_ID: alias of options = { sheet: SHEET_ID }
:
const db = await ;console;
options: a simple object containing some options:
const db = await ;
sheet
(required): when editing a google spreadsheet, it's the part between/spreadsheets/
and/edit
in the url. Please make sure to also publish the spreadsheet before copying it (File > Publish to the Web > Publish)tab
("1"
): the tab to use in the spreadsheet, which defaults to the first tab. It's the number as a string of the tab. See this demo as an example of how to load the second tab.cache
(3600
): set the maximum time (in seconds) that the current cache is valid. After this, the data will be loaded again when the function is called. This is really useful when combined with development env constant. Set to 0 to refresh in each request.
It returns a plain Javascript array. With ES6+, operations on arrays are great, but feel free to use Lodash or similar if you want some more advanced queries.
If you are using server for Node.js with ES6+:
const drive = ;const sheet = "1fvz34wY6phWDJsuIneqvOoZRPfo6CfJyPg1BYgHt59k"; // Or from .env const server = ;const get = serverrouter;const render = serverreply; const home = ; ;
Advanced
There are some more advanced things that you might consider. While I recommend you to read the code for this, here are a couple of examples.
Warm the cache
To warm the cache on project launch before the first request comes in, call the promise without awaiting or doing anything with it:
const drive = ;const sheet = "1fvz34wY6phWDJsuIneqvOoZRPfo6CfJyPg1BYgHt59k";// Warms the cache as soon as the Node.js project is launched; const server = ;const get = serverrouter;const render = serverreply; const home = ; ;
Refresh the cache
To force-refresh the cache at any point you can call drive()
with a cache time of 0:
;
Load locally
This is not available anymore. Since drive-db
returns a plain array, you can just load the data locally:
const data = ;