@cag-group/google-api-tools

Google Cloud API tools for Node.js

Various nice to have helpers for Sheets, Drive, Calendar etc.

Get credentials

Create a service account in cloud console and save it as service-account.json in the root directory.

const {DriveClient} = require('@cag-group/google-api-tools')
const key = require('../service-account-key.json')
const driveClient = new DriveClient(key.client_email, key.private_key)

Google Drive

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

const files = await driveClient.getFilesInFolder(folderId)

example result:

[ { kind: 'drive#file',
    id: '000000000000000000000000000000000000000000000',
    name: 'mysheet 1',
    mimeType: 'application/vnd.google-apps.spreadsheet' } ]

or get meta data for a named file in a specific folder:

const files = await driveClient.getFileMetaData(folderId, 'Test 1')

Read Google Spreadsheet

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

with the sheetsClient we can read in a spreadsheet:

const rows = await sheetsClient.readSheet(sheetId, 'A1:K')

It returns a two-dimensional array of values.

Spreadsheet row unmarshalling

Given this spreadsheet:

Nummer	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 can be used to read each row into objects.

const {SheetsTableReader, SheetsAPIUtil} = require('@cag-group/google-api-tools')

Given a field mapping, mapping column headers to object property names and optional value conversions:

const fieldMapping = [
    {property: 'id', header: 'nummer'},
    {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', convert: SheetsAPIUtil.convertToDate}
]

the reader is used like this:

const {SheetsTableReader, SheetsAPIUtil} = 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 } ]