HTTPS SSH

Google sheets API

node.js API for google sheets

GitHub license GitHub issues Coveralls bitHound bitHound

Babel code style xo Standard Version test ava

yarn Commitizen friendly

<!-- toc -->

<!-- tocstop -->

Developing

  • run babel: generates lib from source
  • run lint: lints JavaScript files
  • run release: release new version using "standard-version"

Testing

Tests are currently not implemented.

Create Service Account

  1. Go to the Google Developer console.
  2. Select Credentials in the left sidebar.
  3. Click the blue button "Create Credentials".
  4. Choose "Service account key".
  5. Create Service account or choose existing.
  6. Choose "JSON".
  7. Click Create.
  8. Your JSON key file will be downloaded to your machine (THIS IS THE ONLY COPY!)
  9. Save this file and use it as service_account.json
  10. Share your spreadsheet with this user (email can be found in the JSON as client_email)

Your application now has the authority to make API calls as users in your domain (to "impersonate" users). When you prepare to make authorized API calls, you specify the user to impersonate.

Create oAuth Client

  1. Go to the Google Developer console.
  2. Select Credentials in the left sidebar.
  3. Click the blue button "Create Credentials".
  4. Choose "oAuth client ID".
  5. Choose "Other".
  6. Enter a name.
  7. Click Create.
  8. You can download the data from your Project page in the Google Developer console
  9. Save this file and use it as client_secret.json

Basic Usage

import SheetsAPI from 'node-google-sheets-api'

const sheetsAPI = new SheetsAPI()

sheetsAPI()
  .then(sheets => sheets.goToId('<SHEET_ID>'))
  .then(sheets => sheets.goToSheet('<SHEET_NAME>'))
  .then(sheets => sheets.getRange(sheets, 'A2:E10'))
  .then(response => {
    console.log(reponse.values)
  })

Methods

  • new SheetsAPI(props [opt]) → {function}
  • Get/Set data

    • addRange(range, values) → {promise}
    • batchGetRange(ranges, sheetName [opt]) → {promise}
    • clearRange(range) → {promise}
    • getRange(range) → {promise}
    • setRange(range, values) → {promise}
    • setCell(cell, value) → {promise}
  • Set options

    • goToId(spreadsheetId) → {promise}
    • goToSheet(sheetName) → {promise}
  • Aliases

    • batchGetCell(cells, sheetName [opt]) → {promise} (batchGetRange)
    • clearCell(cell) → {promise} (clearRange)
    • getCell(cell) → {promise} (getRange)

Helpers

Helpers allow keeping a shallow queue. They return the sheet methods and allow setting a callback

import SheetsAPI from 'node-google-sheets-api'
import {getRange, setRange, setCell, clearRange, toJSON} from 'node-google-sheets-api/helpers'

const sheetsAPI = new SheetsAPI({
  scopes: ['https://www.googleapis.com/auth/spreadsheets']
})

sheetsAPI()
  .then(sheets => sheets.goToId('<SHEET_ID>'))
  .then(sheets => sheets.goToSheet('<SHEET_NAME>'))
  .then(sheets => clearRange(sheets, 'A1:C5'))
  .then(sheets => setRange(sheets, 'A1:C1', [['Name', 'Email address', 'Telephone number']]))
  .then(sheets => setRange(sheets, 'A2:C2', [['Marge Simpson', 'marge@simpson.com', '123456789']]))
  .then(sheets => setRange(sheets, 'A3:C3', [['Homer Simpson', 'homer@simpson.com', '123456789']]))
  .then(sheets => setCell(sheets, 'A2', 'Maggie Simpson'))
  .then(sheets => getRange(sheets, 'A1:C4', response => {
    const data = toJSON(response.values)
    console.log(JSON.stringify(data))
    // [{
    // "name": "Marge Simpson",
    // "emailAddress": "marge@simpson.com",
    // "telephoneNumber": "123456789",
    // },{
    // "name": "Homer Simpson",
    // "emailAddress": "homer@simpson.com",
    // "telephoneNumber": "123456789",
    // },{
    // "name": "Maggie Simpson"
    // }]
  }))
  .catch(err => {
    throw err
  })

Options

  • If you want to store your files in a different location you can configure their relative path.
  • Per default the access is readonly. To get write access set a different scope (without ".readonly" suffix)
import SheetsAPI from 'node-google-sheets-api'

const sheetsAPI = new SheetsAPI({
  clientSecret: './google/client_secret.json',
  serviceAccount: './google/service_account.json',
  scopes: ['https://www.googleapis.com/auth/spreadsheets']
})

sheetsAPI()
  .then(sheets => sheets.goToId('<SHEET_ID>'))
  .then(sheets => sheets.goToSheet('<SHEET_NAME>'))
  .then(sheets => sheets.setCell('A2', 'This is cell A2'))
  .then(response => {
    console.log(reponse.values)
  })