sheets-database

// Initialize the Database with doc ID (long id in the sheets URL)
const db = new SheetDatabase('');

// Initialize Auth
// see more available options at https://rahul-jha98.github.io/sheets-database/#/getting-started/authentication
await db.useServiceAccount({
client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
private_key: process.env.GOOGLE_PRIVATE_KEY,
});

await db.sync(); // actually connecting with sheet and fetching data

// ADDING TABLES
const table1 = await db.addTable('table1', ['column1', 'column2', 'column3']);
const table2 = await db.addTable('table2', ['column1', 'column2']);

// RENAMING TABLES
await table1.rename('newTable1');

await db.renameTable('table2', 'newTable2');

// DELETING TABLES
await db.newTable1.drop();

// Insert Single Entry
await table.insertOne({'name': 'Micheal Scott', 'age': 43});

// Insert Multiple Entries
await table.insert([
{'name': 'Jim Halpert', 'age': 30},
['Dwight Schrute', 35]
]);

console.log(table.getData());
/**
* [
* {name: 'Micheal Scott', age: 43},
* {name: 'Jim Halpert', age: 30},
* {name: 'Dwight Schrute', age: 35}
* ]
*/

// Update Rows
// Here we add 10 to all the rows where current age is less than 40
await table.updateRowsWhere(
(currentData) => (currentData.age < 40),
(data) => {
return {age: data.age + 10}
});

console.log(table.getData());
/**
* [
* {name: 'Micheal Scott', age: 43},
* {name: 'Jim Halpert', age: 40},
* {name: 'Dwight Schrute', age: 45}
* ]
*/

Do you ever wonder if you can use Google Sheets as a no-cost database? Well, if your application deals with lot of entries and joins across tables than of course it isn't such a good idea. But if you have a small application or a static website that needs very few dynamic content there is no point in having a backend that deals with a database to serve those content since you could easily use a Google Sheet to store the data. You could also consider this as an option to get the frontend part's development started by using Google Sheet as a mock database while the actual backend is being built.

But the Google Sheet's API v4 is a bit awkward with confusing docs, at least to get started. Moreover, the API is not designed to use Sheets API as a database which is why you would require you to deal with the rows and columns data manually to deal with data. With such a steep learning curve to get started the prospect of using it as a database doesn't seems like a good deal.

The library aims to remove the learning curve completely by providing methods that lets you interact with the database without worrying about the Sheets API at all.
Moreover the API of the library is quite intuitive to get started with and provides functionalities for most of the database operations.

Note

Contributions

This module was written by Rahul Jha.

Contributions are welcome. Make sure to add relevant documentation along with code changes.
Also, since I am new to Typescript and still exploring any help in improving the code practices and conventions would be appreciated.