@stellasphere/google-sheets

A simple Google Sheets API wrapper designed to be able to access sheet data in a database-like way.

Installation

Install @stellasphere/google-sheets with npm

npm install @stellasphere/google-sheets

Guide

Using this testing spreadsheet. Occuring in a async function:

const GoogleSheets = require("@stellasphere/google-sheets")
var sheet = new GoogleSheets("1x268zkmymCjNm_iUwBM4v4EQCjT0H5IwcVAB-rfZ9x8")

init()

async function init() {
  // Remember to remove all but one of the authentication methods.
  // AUTHENTICATION VIA API KEY
  await sheet.authViaAPIKey(process.env.googleapikey) // Get from: https://console.cloud.google.com/apis/credentials/key 


  // AUTHENTICATION VIA SERVICE ACCOUNT
  await sheet.authViaServiceAccount(process.env.clientemail, process.env.privatekey) // Get from: https://console.cloud.google.com/iam-admin/serviceaccounts


  // AUTHENTICATION VIA SERVICE ACCOUNT FILE
  await sheet.authViaServiceAccountFile("./authentication.json") // Get from: https://console.cloud.google.com/iam-admin/serviceaccounts


  // GET THE ROWS IN A INDEX
  var index = await sheet.index("sheet")
  console.log(index)
  /*
  {
    '123': { id: '123', name: 'testing' },
    '234': { id: '234', name: 'testing2' },
    '345': { id: '345', name: 'testing3' },
    '456': { id: '456', name: undefined }
  }
  */


  // GET THE ROWS IN A ARRAY
  var list = await sheet.list("sheet")
  console.log(list)
  /*
  [
    { id: '123', name: 'testing' },
    { id: '234', name: 'testing2' },
    { id: '345', name: 'testing3' },
    { id: '456', name: undefined }
  ]
  */


  // GET THE ROW
  var result = await sheet.get("sheet","123")
  console.log(result)
  /*
  {
    id: '123',
    name: 'testing'
  }
  */
}

API Reference

Inital Constructor

GoogleSheets

  new GoogleSheets(sheetid,options)

Constructor

Parameter	Type	Description
sheetid	string	Required. Your Google Sheets document ID. (Visible in the url: https://docs.google.com/spreadsheets/d/*id*/edit#gid=0)
options	GoogleSheetsOptions	Valid options are undefinedifblank and debug

Authentication

GoogleSheets().authViaServiceAccount(clientemail,privatekey)

Authenticates using a service account client email and private key. Service accounts are created on the service accounts page on Google Cloud Console. To use the JSON file, use authViaServiceAccountFile().

  var sheet = new GoogleSheets()
  await sheet.authViaServiceAccount(clientemail,privatekey)

Parameter	Type	Description
clientemail	string	Required. The client email of a service account.
privatekey	string	Required. The private key of a service account.

GoogleSheets().authViaServiceAccountFile(path)

Authenticates using a service account client email and private key. Service accounts are created on the service accounts page on Google Cloud Console.

  var sheet = new GoogleSheets()
  await sheet.authViaServiceAccountFile(path)

Parameter	Type	Description
path	filepath	Required. The file path of a service account JSON key.

GoogleSheets().authViaAPIKey(apikey)

Authenticates using a API key. API keys are created on the credentials page on Google Cloud Console.

  var sheet = new GoogleSheets()
  await sheet.authViaAPIKey(apikey)

Parameter	Type	Description
apikey	string	Required. The API key for Google Sheets API.

Google Sheet Methods

GoogleSheets().docInfo()

Gets the information on the Google Sheet document file.

  var sheet = new GoogleSheets()
  var result = await sheet.docInfo()

GoogleSheets().sheet(sheetname)

Gets the sheet information, rows and headers.

  var sheet = new GoogleSheets()
  var result = await sheet.sheet(sheetname)

Parameter	Type	Description
sheetname	string	Required. The name of the sheet or "worksheet".

GoogleSheets().index(sheetname,customprimarykey)

Gets the rows of the sheet in the form of a object. The primary key will be the first header available if a custom one is not specified.

  var sheet = new GoogleSheets()
  var result = await sheet.index(sheetname,customprimarykey)

Parameter	Type	Description
sheetname	string	Required. The name of the sheet or "worksheet".
customprimarykey	string	A optional custom primary key.

GoogleSheets().list(sheetname)

Gets the rows of the sheet in the form of an array of objects.

  var sheet = new GoogleSheets()
  var result = await sheet.list(sheetname)

Parameter	Type	Description
sheetname	string	Required. The name of the sheet or "worksheet".

GoogleSheets().get(sheetname,key,customprimarykey)

Gets a row from the sheet in the form of a object.

  var sheet = new GoogleSheets()
  var result = await sheet.get(sheetname,key,customprimarykey)

Parameter	Type	Description
sheetname	string	Required. The name of the sheet or "worksheet".
key	string	Required. Id of item to fetch
customprimarykey	string	Required. Id of item to fetch

Internal Functions

GoogleSheets().rowparser(headers,row)

This is a internal function. It parses a array of headers and a Google Sheets API row object.

  var sheet = new GoogleSheets()
  var result = await sheet.rowparser(headers,row)

Parameter	Type	Description
headers	string	Required. Id of item to fetch
row	string	Required. Id of item to fetch

GoogleSheets().authComplete()

This is a internal function. It sets the authenticated property true and loads the document info.

  var sheet = new GoogleSheets()
  await sheet.authComplete()