sheetsdb

Topics

• Introduction
• Setup
• Installation
• Basic Examples
  • Create
  • Insert
  • Select
  • Update
  • Delete
• Joins
• Accessible Classes
  • Database
  • Table
  • Row
  • Query/Update/Delete
• License

Introduction

This tool is designed to turn Google Sheets into a feasible database, providing a wrapper to the Sheets API that is more condusive to database interactions.

Setup

To use this tool, you must first set up a service account on the Google Developers Console.

1. Create a project

2. Select Add Credentials, and select Service Account. (Make sure to create a JSON key type)

3. Once the account has been created, generate a new JSON key. This will be used as credentials to connect to the Google Sheet.

4. Create a new Google Sheet. Note the long string of characters in the url. This is the id by which the sheet can be connected.

5. Share the new Sheet with the service account, allowing edit access to the service credentials

Installation

npm install sheetsdb

Basic Examples

Create

// that long string of characters in the sheet\'s url
var ID = 'sheet key';
 
// can be retrieved from the downloaded json file for your service account
var creds = {
  client_email: 'email',
  private_key: 'key'
};
 
var Database = require('sheetsdb');
Database.connect(ID, creds, function(err, db) {
  if(err) console.log(err);
  
  db.create('Sheet2', function(err, table) {
    if(err) console.log(err);
    
    // new table has been created
  });
});

Insert

// that long string of characters in the sheet\'s url
var ID = 'sheet key';
 
// can be retrieved from the downloaded json file for your service account
var creds = {
  client_email: 'email',
  private_key: 'key'
};
 
var Database = require('sheetsdb');
Database.connect(ID, creds, function(err, db) {
  if(err) console.log(err);
  
  db.insert('Sheet1', {id:1, name:'John Doe'}, function(err) {
    if(err) console.log(err);
    
    // row has been inserted into table
  });
});

Select

// that long string of characters in the sheet\'s url
var ID = 'sheet key';
 
// can be retrieved from the downloaded json file for your service account
var creds = {
  client_email: 'email',
  private_key: 'key'
};
 
var Database = require('sheetsdb');
Database.connect(ID, creds, function(err, db) {
  if(err) console.log(err);
  
  db.select('Sheet1').where({id: 1}).exec(function(err, rows) {
    if(err) console.log(err);
    
    // continue working with the newly retrieved rows
  });
});

Update

// that long string of characters in the sheet\'s url
var ID = 'sheet key';
 
// can be retrieved from the downloaded json file for your service account
var creds = {
  client_email: 'email',
  private_key: 'key'
};
 
var Database = require('sheetsdb');
Database.connect(ID, creds, function(err, db) {
  if(err) console.log(err);
  
  db.update('Sheet1', {id: 2}).where({id: 1}).exec(function(err) {
    if(err) console.log(err);
    
    // rows whose attribute id was 1 now have an id of 2
  });
});

Delete

// that long string of characters in the sheet\'s url
var ID = 'sheet key';
 
// can be retrieved from the downloaded json file for your service account
var creds = {
  client_email: 'email',
  private_key: 'key'
};
 
var Database = require('sheetsdb');
Database.connect(ID, creds, function(err, db) {
  if(err) console.log(err);
  
  db.delete('Sheet1').where({id: 1}).exec(function(err) {
    if(err) console.log(err);
    
    // rows in which attribute id == 1 have been deleted
  });
});

Joins

It is important to note that joins are not currently implemented and should not be expected soon given the nature of the google sheets api. There are feasible workarounds, however, one of which can be found here.

Accessible Clases

Database

Static Methods

connect(id:String, creds:Object, callback:Function)

connects to the given spreadsheet using the provided credentials

Passes Back (err, db) where db is an instance of Database

Instance Methods

table(tableName:String):Table

return the table of the given name

create(tableName:String, callback:Function)

creates and a new Table with the given name

Passes back the new Table

insert(tableName:String row:Object, callback:Function)

inserts a new row with specified attributes into the table given

select(tableName:String):Select

returns a Select Object for the given table

update(tabeName:String, update:Object):Update

returns an Update Object for the given table

delete(tableName:String):Delete

returns a Delete Object for the given table

Table

Instance Methods

insert(tableName:String row:Object, callback:Function)

inserts a new row with specified attributes into the table

select(tableName:String):Select

returns a Select Object for the table

update(tabeName:String, update:Object):Update

returns an Update Object for the table

delete(tableName:String):Delete

returns a Delete Object for the table

drop(callback:Function)

drops the table

Row

Instance Methods

get(attribute:String)

retrieves the value for the sttribute specified

set(attributeKey:String, attributeValue:Any)

sets the value of the attribute specified

(All values are saved as Strings unless modified)

save(callback:Function)

saves the current state of the Row Object back to to Google Sheet

del(callback:Function)

deletes the Row from the Google Sheet

Query/Update/Delete

Instance Methods

where(clause:Object):Query/Update/Delete

Adds a 'where' clause to the statement

orderBy(clause:String):Query/Update/Delete

Orders the rows retrieved by the specified column

limit(clause:String):Query/Update/Delete

Limits the number of rows affected by the specified amount

offset(clause:String):Query/Update/Delete

Offsets the row retrieval by the indicated value

exec(callback:Function)

Executes the query/update/delete

Query passes back the filtered rows

License

The MIT License (MIT)

Copyright (c) 2015 Chris Whelan

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.