@cag-group/google-api-tools

Google Cloud API tools for Node.js

Various helpers for Google Calendar, Drive and Sheets.

Authorization

This module authorizes with a service account only, as opposite to google user delegation in most Google's examples. Create a service account in cloud console and save it as service-account.json. Enable the APIs (Calendar, Drive and/or Sheets).

const key = require('./service-account-key.json')

Google Drive

API reference.

The drive client is a helper that can list meta data about files in a folder:

const {DriveClient} = require('@cag-group/google-api-tools')
const client = new DriveClient(key.client_email, key.private_key)
const response = await client.getFilesInFolder(folderId)

Get meta data for a named file in a specific folder:

const file = await client.getFileMetaData(folderId, 'Walt Disney characters')

Google Calendar

API reference.

Read calendar items within a time interval.

The calendar shall be public or shared with the service account.

const {CalendarClient} = require('@cag-group/google-api-tools')
const client = new CalendarClient(key.client_email, key.private_key)

const items = await client.getAllItems(calendarId, new Date('2017-09-15T00:00'), new Date('2017-09-16T00:00'))
console.log(items)

Google Sheets

API reference.

Read cell values

const {SheetsClient} = require('@cag-group/google-api-tools')
const client = new SheetsClient(key.client_email, key.private_key)

Read cell values given a cell range:

const rows = await client.readSheetCellRange(sheetId, 'A1:K')

It returns a two-dimensional array of values.

Object creation from spreadsheet cells

Given an example spreadsheet:

ID      Förnamn	    Efternamn	Unused	Förmögenhet     Först sedd
1       Kalle       Anka        foo     1               1934-06-09
2       Alexander   Lukas       bar     1000000         1948-12-01
3       Joakim      von Anka    zoo     1×10^+30        1947-12-01 

the SheetsTableReader is used to read each row into objects.

A field mapping is specified, mapping column headers to object property names and optional cell value conversions:

const {SheetsAPIUtil} = require('@cag-group/google-api-tools')
const fieldMapping = [
    {property: 'id', header: 'ID'},
    {property: 'firstName', header: 'Förnamn'},
    {property: 'lastName', header: 'efternamn'},
    {property: 'balance', header: 'förmögenhet', convert: v => v / 100},
    {property: 'firstSeen', header: 'Först sedd'}
]

Specifying this columns can change order and place in the spreadsheet without affecting the code reading it. New columns can also be added - they will be ignored.

In order to match column headers with field mapping, common human errors are addressed by relaxed string comparisions: Headers in code and in sheet are converted to lower case and all spaces are removed before comparision.

example usage:

const {SheetsTableReader} = require('@cag-group/google-api-tools')
const reader = new SheetsTableReader(rows[0], fieldMapping)
const rowObjects = rows.slice(1).map(r => reader.createObject(r))

Outputted rowObjects:

[ { id: 1,
    firstName: 'Kalle',
    lastName: 'Anka',
    balance: 0.01,
    firstSeen: 1934-06-09T00:00:00.000Z },
  { id: 2,
    firstName: 'Alexander',
    lastName: 'Lukas',
    balance: 10000,
    firstSeen: 1948-12-01T00:00:00.000Z },
  { id: 3,
    firstName: 'Joakim',
    lastName: 'von Anka',
    balance: 1e+28,
    firstSeen: 1947-12-01T00:00:00.000Z } ]