README

Google Sheets Helper

The Google Sheets Helper is an extension for the Skypager Node Runtime which lets you treat google sheet documents as modules.

An instance of the Sheet Helper can be used in scripts, or node.js servers, to read and write from that spreadsheet in real time.

You can add, delete, or modify the worksheets in a google spreadsheet.
You can add, delete, or modify rows in a worksheet.
Each worksheet represents its data as objects. The keys to this object correspond to the values of the columns in Row 1. Aka your headers.
You can define classes which represent each row in a worksheet. The default RowEntity class has getters and setters for each column attribute.

For example, this google sheet https://docs.google.com/spreadsheets/d/1w5LZe7wXL59S2NhafvbQXudFpdVI3Z0NVwBMrexQvXQ/edit?usp=sharing

has a worksheet 'sheet1'
which has column headers ["column-one","column-two","column-three","column-four","column-five"]
has data in rows 2, 5

it produces the following data

const data = {
  sheet1: [{
    columnOne: "row one column one",
    columnTwo: "row one column two",
    columnThree: "row one column three",
    columnFour: "row one column four",
    columnFive: "row one column five"
  }, /* ... */]
}

Installation

To use this library in your own project, make sure to install it

Via NPM

npm install @skypager/node @skypager/helpers-sheet --save

Via YARN

yarn add @skypager/node @skypager/helpers-sheet --save

Required Setup

You'll need

a Service Account JSON
The Google Drive and Google Sheets APIs
Google Sheets shared with the client_email from the service account.

Server to Server Auth

Using this module requires using interacting with Google APIs using their server to server auth method, which is powered by Service Account credentials.

A Service Account credential looks like:

{
  "type": "service_account",
  "project_id": "your-project-id",
  "private_key_id": "your-private-key-id",
  "private_key": "-----BEGIN PRIVATE KEY-----\nYOUR-PRIVATE_KEY\n-----END PRIVATE KEY-----\n",
  "client_email": "some-random-email@your-project-id.iam.gserviceaccount.com",
  "client_id": "your-client-id",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/your-project-id.iam.gserviceaccount.com"
}

You can manage your Service Accounts by going to the Google Cloud Console and creating a project or selecting the one you want to work with.

Once you are there, under the IAM & admin section you can click on Service Accounts. (e.g. https://console.cloud.google.com/iam-admin/serviceaccounts?project=your-project-id)

Create a service account, and download the JSON to secrets/serviceAccount.json, or some other path.

You'll use this service account when activating the helper, as you'll see in USAGE.

Enable Google APIs

You'll also need to enable the Google Drive and Google Sheets APIs.

In the Google Cloud Console, under APIs and Services choose Library (e.g. https://console.cloud.google.com/apis/library?project=your-project-id)

Share Your Sheets

With the server to server auth set up, the last step is to share the sheets you want to load with the service account client email you'll be using to access them.

You can find the email address in client_id property of the service account json

Usage

Doing the following will create a registry of available sheet helpers at runtime.sheets

const runtime = require('@skypager/node')
const SheetHelper = require('@skypager/helpers-sheet')

runtime.use(SheetHelper, {
  // or process.env.GOOGLE_APPLICATION_CREDENTIALS
  serviceAccount: '/path/to/service.json'
  // or process.env.GCLOUD_PROJECT or read from the service account project_id
  googleProject: 'google-cloud-project'
})

A sheet helper can be created from a module in your project. A valid sheet helper module is any module that exports a sheetId string which refers to the id found in the URL to the spreadsheet

https://docs.google.com/spreadsheets/d/$THIS-IS-THE-ID/edit#gid=something

// my-users-sheet.js
export const sheetId = '$your-google-drive-sheet-id'

To be able to turn this module into a GoogleSheet instance, we register it:

import * as MyUsersSheet from './my-users-sheet'

runtime.sheets.register('users', () => MyUsersSheet)

And activate it when we need it

const sheet = runtime.sheet('users')

// will return objects for each row.
// keys are the header column, value is the cell for that row / column 
sheet.loadAll().then((keyedByWorksheetName) => {
  const { users = [], companies = [], whatever = [] } = keyedByWorksheetName
  console.log(users[0]) // { name: "Jon Soeder", job: "player coach captain", email: "jon@jmoney.com" }
})

Automatically Discovering Spreadsheets via Google Drive API

If you don't need to build a module for your sheet (you just want the data), you can skip registering modules by just remotely discovering sheets available to you.

async () => {
  await runtime.sheets.discover({ sharedWithMe: true, teamDrives: true })

  runtime.sheets.available // will be the camelCased title of each spreadsheet

  const users = runtime.sheet('myUsersSheet')

  await users.loadAll() 
}

Building an interface for working with your sheet

When you write a module for the SheetHelper to wrap, you can export functions to make working with the sheet data more user friendly

export const sheetId = 'my-users-spreadsheet-id'

// will automatically call loadAll() and cache it on this.data when the helper instance initializes
export const eagerLoaded = true

export function getKeyedById() {
  return this.lodash.keyBy(this.data, 'id')
}

export function getGroupedByRole() {
  return this.lodash.groupBy(this.data, 'role') 
}

export async function addUser(userData = {}) {
  await this.spreadsheet.addRow(
    this.spreadsheet.worksheets[0].id,
    Object.values(userData)
  )
}

Now when you work with this sheet helper instance, these will be available as properties

const users = runtime.sheet('users')

async function main() {
  await users.whenReady()
  const { groupedByRole, keyedById } = users

  await users.addUser({ name: 'Jon Soeder', role: 'Baller', id: 'soederpop' })
  // do something
}

Row Level Entities

The spreadsheet helper can load all of the data as plain old javascript objects.

Your column headers in row one determine the attributes, and all of the rows 2 and above get turned into objects with properties that match the column names.

You can also work with each row as an entity, using the Active Record pattern.

By default, each entity is an instance of the RowEntity class, which is just an object that has getters and setters for each of the attributes. Setting the value actually writes the value to the cell in google spreadsheets.

You can define a custom RowEntity class for an individual worksheet.

In the example below, you have a google spreadsheet that has a worksheet titled users.

This worksheet includes two columns, firstName, lastName.

We can define a User entity that lets us define a computed property fullName

const googleSpreadsheet = runtime.sheet('master-users-list')

class User extends googleSpreadsheet.RowEntity {
  set fullName(value) {
    const parts = value.split(" ")

    this.firstName = parts.shift()
    this.lastName = parts.join(" ")

    return value
  }

  get fullName() {
    return this.firstName + ' ' + this.lastName
  }
}

googleSpreadsheet.registerEntity('users', () => User)

async function main() {
  const users = googleSpreadsheet.sheet('users')
  await users.indexCells()

  const user = users.entities[0]
  console.log('User full name' + user.fullName)
}

main()

Sheets as Modules

The above examples show how to use this module.

A very common scenario is you have a project, and there are a few special spreadsheets that everyone references.

In this scenario, you don't need to discover sheets, you will know their id's ahead of time.

Maybe you have a products sheet, one you use in development, and one that is used "in production".

If all you need is the data, then the examples you've seen so far will work well.

If you want to build a more complex system on top of the google sheets (say you have multiple google sheets) then the following setup is something you can build on.

scripts
- reorder-all.js
- generate-website.js
src/
- sheets/
  - products.js
  - inventory.js
  - promotions.js
- runtime.js

Your products.js is for your products sheet, which has a products worksheet that has a SKU column.

We hard code the sheet id, and define an initialize hook which we use to associate a Product class with every row in the sheet's products worksehet.

export const sheetId = 'sheet-id-for-your-products-sheet'

export const eagerLoaded = true

export async function initialize() {
  const sheet = this

  class Product extends sheet.RowEntity {
    get stockSheet() {
      return this.runtime.sheet('inventory')
    }

    get promotionsSheet() {
      return this.runtime.sheet('promotions')  
    }

    get promotions() {
      return this.promotionsSheet.products.filter(({ sku }) => sku === this.sku)
    }

    get inventory() {
      return this.stockSheet.inventory.filter(({ sku }) => sku === this.sku)      
    }

    get inStock() {
      return !!inventory.find(({ onHand }) => onHand > 0)
    }  
  }
}

We do something similar for both inventory.js and promotions.js

// inventory.js
export const sheetId = 'your-inventory-sheet-id'

export const eagerLoaded = true

export async function initialize() {
  const { RowEntity } = this  

  class Stock extends RowEntity {
    async reorder() {
      const { sku, supplierEmail } = this
      await this.runtime.emailer.send(supplierEmail, {
        subject: `Re-order for ${sku}`,
        message: 'yo resupply me'
      })
    }
  }

  this.registerEntity('stock', () => Stock)
}

Promotions keeps track of promotion codes by product sku

// promotions.js
export const sheetId = 'your-promotions-sheet-id'

export const eagerLoaded = true

export async function initialize() {
  const { RowEntity } = this  

  class Promotion extends RowEntity {
    get stockSheet() {
      return this.runtime.sheet('inventory')
    }

    get inventory() {
      return this.stockSheet.filter(({ sku }) => sku === this.sku)
    }

    // a promotion is only valid while we have enough stock, for example
    get isStillValid() {
      return !!inventory.find(({ onHand }) => onHand > this.minimumStockLevel)
    }
  }

  this.registerEntity('promotions', () => Promotion)
}

So three separate google docs are combined in this example, each one is represented by a module.

Each module defines different classes to represent the data found in each row of their named worksheets.

So we can register them with the sheets registry and use them all together, in a file called runtime.js

const runtime = require('@skypager/node')
const SheetHelper = require('@skypager/helpers-sheet')

runtime.use(SheetHelper, {
  // or process.env.GOOGLE_APPLICATION_CREDENTIALS
  serviceAccount: '/path/to/service.json'
  // or process.env.GCLOUD_PROJECT or read from the service account project_id
  googleProject: 'google-cloud-project'
}).use((next) => {
  runtime.sheets.register('products', () => require('./sheets/products.js'))
  runtime.sheets.register('inventory', () => require('./sheets/inventory.js'))
  runtime.sheets.register('promotions', () => require('./sheets/promotions.js'))

  Promise.all([
    runtime.sheets.allInstances().map((sheet) => sheet.whenReady())
  ]).then(() => next()).catch((error) => next(error))
})

Now in this hypothetical example, there are two scripts. reorder-all.js and generate-website.js

The reorder-all script can be run weekly, to resupply all the items you're low on.

The generate-website script can be run whenever the inventory, products, or promotions sheets change.

both of them would only need to include your runtime.js module

import runtime from '../src/runtime'

async function main() {
  await runtime.start()
  const inventory = runtime.sheet('inventory')

  const stockLevels = inventory.ws('stock')

  for(stock of stockLevels) {
    if (stock.onHand < stock.reorderPoint) {
      await stock.reorder()
    }
  }
}

and can be executed by running the scripts

$ skypager reorder-all --esm
$ skypager generate-website --esm

Internals

Internally, this library uses the Node Google Spreadsheet library, as well as the official googleapis

Usage no npm install needed!