spreadsheetdb

Fluent & complete API for working with Google Sheets.

Usage

var Sheet = require('spreadsheetdb')
 
var sheet = Sheet({
  client_email: process.env.CLIENT_EMAIL,
  private_key: process.env.PRIVATE_KEY
})
 
sheet
  .load(process.env.SHEET)
  .create('Person')
  .update('Person', { id: 5, name: 'Matt' }, { name: 'Matthew' })
  .query('Person', { id: 5 })
  .then(res => {
    console.log('response', res)
  })
  .catch(e => {
    console.log('error', e.stack)
  })

Installation

npm install spreadsheetdb

Authenticate with Google Sheets

You have to do this once.

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.

You have to do this each time you create a new database

1. Create a new Google Sheet
2. Share the sheet with the service account, allowing edit access to the service credentials, it should look something like this: [e-mail]

API

Sheet(credentials)

Authenticate with Google. credentials requires the following:

• client_email (ex. [e-mail])
• private_key (ex. -----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCe6y ...)

sheet.load(url)

Load a specific sheet by its url or id.

sheet.create(table)

Create a new table. In Google Sheet speak, this is a new worksheet.

sheet.insert(table, object)

Insert a new row into the database

sheet.insert('Person', { name: 'Matt', age: 26, email: '[e-mail]' })

sheet.update(table, query, update)

Find and update rows in a table.

sheet.update('Person', { id: 5, name: 'Matt' }, { name: 'Matthew' })

sheet.query(table, query)

sheet.update('Person', { id: 5, name: 'Matt' }, { name: 'Matthew' })

sheet.columns(table)

Get the table columns

sheet.columns(table, columns)

Set the table columns

sheet.columns('Person', ['name', 'age', 'phone', 'email'])

License

MIT