db-google-spreadsheets

Google Spreadsheet as DB (db-google-spreadsheet)

Use Google Spreadsheet as DB and Worksheets as Tables:

Setup

Create oauth2.keys.json file with your Google App id and secret as shown in example_oauth2.keys.json

You may need a CLIENT_ID, CLIENT_SECRET and REDIRECT_URL. You can find these pieces of information by going to the Developer Console, clicking your project --> APIs & auth --> credentials.

Then execute quickstart.js file to get the oauth2.token.json file and use the module.

How to use

Open the Spreadsheet

    var params = {
      spreadsheetId: "189ubij3PIK7ujsoSXXXXr3hsZqap_4w",
      worksheetName: "Users"
    };
    var sheet = Spreadsheet(params);

Example Spreadsheet Data:

Get Rows as Objects:

var rows = await sheet();
console.log(rows)

Console output:

[ Row { email: '[e-mail]', useCount: '0' },
  Row { email: '[e-mail]', useCount: '0' },
  Row { email: '[e-mail]', useCount: '0' } ]

Update Data:

var editRow = rows.filter((row) => row.email === '[e-mail]')[0]
editRow.useCount++;
editRow.date = '20/10/2019'
editRow.save()

Updated Spreadsheet:

Note that it automatically added the new Date column

Add new Row to Spreadsheet:

var newRow = sheet({
    email: "[e-mail]",
    useCount: 19
});
await newRow.save();

Updated Spreadsheet:

Note that it automatically added the new Date column

Check Rows as Objects:

var rows = await sheet();
console.log(rows)

Console output:

[ Row { email: '[e-mail]', useCount: '0' },
  Row { email: '[e-mail]', useCount: '1', date: '20/10/2019' },
  Row { email: '[e-mail]', useCount: '0' },
  Row { email: '[e-mail]', useCount: '19' } ]

Column Attribute Mapping

The first row is reserved for the column names, so that order of columns and row two is used for human-friendly names that can be modified without affecting the DB.

This first row can be hidden for better visualization with right-click -> hide Row: