# Features that make it a good database

Google Sheets

```
Store Data in Rows and columns
```
Security
Consistency
Easy to Setup
Less in Cost

# Google API's

Google Api's for Sheets

```
Get
```
Update Single/Batch Cells
Delete Single/Batch Cell

Functionalities

Obstacles

Lack of params for different CRUD Operations

Proper Documentations not Available

Idea and source of Motivation

# Idea

Idea: Build a ORM for Gsheets support using google API's as a dependency

Source of Motivations:

- Lack of a updated ORM with proper documentation.
The only one that exists (https://github.com/mickhansen/google-sheets-orm) doesn't have a documentation so very difficult to use.

- Easy and fast setup

: Setting up Google sheets thorugh Google Console Portal is so easy and fast to setup for quick running of a database service.

- Best Platform for non-tech people

: Google Sheets can be a best platform non-technical people and the sheet can also be integrated to SQL databases like Postgres using tools like https://www.wax.run/

```
# PRESENTING CODE
```

Implementation in Javascript

 const {google} = require("googleapis");
 
const keys = require("/keys.json");
 
const cl = new google.auth.JWT(keys.client_email, null, keys.private_key, [
    "https://www.googleapis.com/auth/spreadsheets",
]);
 
class Sheet {
    constructor(sheetId, sheetName) {
        this.sheetId = sheetId;
        this.sheetName = sheetName;
    }
    // find function to GET all Cells
  	async find() {
        try {
            const gsapi = google.sheets({version: "v4", auth: cl});
            const opt = {
                spreadsheetId: this.sheetId,
                range: this.sheetName,
            };
          
          	// Get all rows in the sheet. This response is a array of arrays
            let rows = await gsapi.spreadsheets.values.get(opt).data.rows;
            const response = [];
          	
            // converting array format to json with first row as key. 
            for (let row_id = 1; row_id < rows.length; row_id++) {
                let row = rows[row_id];
                let obj = {};
                for (const heading in rows[0]) {
                    obj[rows[0][heading]] = row[heading];
                }
                response.push(obj);
            }
            return response;
        } catch (err) {
            throw new Error("No such Sheet found");
        }
    }
}
 
const data_instance=new Sheet(env.SheetID,env.Sheetname)
const all_data=data_instance.find();

1

Start with the basic CRUD requests that returns all the data.

2

Add features like filtering by different query params

3

Release a basic version on npm

4

Add batch Update for all features

Timeline

5

Add advanced options such as virtuals and Eager Loading

thank you

- Rajiv Harlalka

- github.com/rajivharlalka

- rajivharlalaka.me

	const {google} = require("googleapis");

	const keys = require("/keys.json");

	const cl = new google.auth.JWT(keys.client_email, null, keys.private_key, [
	"https://www.googleapis.com/auth/spreadsheets",
	]);

	class Sheet {
	constructor(sheetId, sheetName) {
	this.sheetId = sheetId;
	this.sheetName = sheetName;
	}
	// find function to GET all Cells
	async find() {
	try {
	const gsapi = google.sheets({version: "v4", auth: cl});
	const opt = {
	spreadsheetId: this.sheetId,
	range: this.sheetName,
	};

	// Get all rows in the sheet. This response is a array of arrays
	let rows = await gsapi.spreadsheets.values.get(opt).data.rows;
	const response = [];

	// converting array format to json with first row as key.
	for (let row_id = 1; row_id < rows.length; row_id++) {
	let row = rows[row_id];
	let obj = {};
	for (const heading in rows[0]) {
	obj[rows[0][heading]] = row[heading];
	}
	response.push(obj);
	}
	return response;
	} catch (err) {
	throw new Error("No such Sheet found");
	}
	}
	}

	const data_instance=new Sheet(env.SheetID,env.Sheetname)
	const all_data=data_instance.find();

G-Sheets as a Database