Google sheets example

google-sheet.ts

import {JWT} from 'google-auth-library'
import * as credentials from '../../.gcloud/reporter.json'
import {
  drive,
  drive_v3 as v3,
} from '@googleapis/drive'
import {
  GoogleSpreadsheet,
  GoogleSpreadsheetWorksheet,
} from 'google-spreadsheet'

const getClient = (): v3.Drive => {
  const auth = new JWT({
    email: credentials.client_email,
    key: credentials.private_key,
    scopes: [
      'https://www.googleapis.com/auth/drive',
    ],
  })
  return drive({
    version: 'v3', auth,
  })
}

export const doSheetAuth = async (doc: GoogleSpreadsheet): Promise<void> => {
  await doc.useServiceAccountAuth({
    client_email: credentials.client_email,
    private_key: credentials.private_key,
  })
}

export const createWorksheet = async (docId: string, title: string, headerValues: string[]): Promise<GoogleSpreadsheetWorksheet> => {
  const doc = new GoogleSpreadsheet(docId)
  await doSheetAuth(doc)
  const newSheet = await doc.addSheet({
    title,
    index: 0, // always inserts to the left
  })
  await newSheet.resize({
    rowCount: 1,
    columnCount: headerValues.length+2,
  })
  await newSheet.setHeaderRow(headerValues)
  return newSheet
}