@yuanqing/google-sheets

An easier interface to read from and write to Google Sheets

Quick start

$ yarn add @yuanqing/google-sheets

const { getSpreadsheet } = require('@yuanqing/google-sheets')

async function main () {
  const serviceAccountCredentials = {
    clientEmail: '<client_email>',
    priateKey: '<private_key>'
  }
  const spreadsheetId = '<spreadsheet_id>'
  const sheetName = '<sheet_name>'
  const spreadsheet = await getSpreadsheet(
    serviceAccountCredentials,
    spreadsheetId
  )
  const sheet = await spreadsheet.getSheet(sheetName)
  const rows = await sheet.getAllRows()
  console.log(rows)
}

await main()

• <client_email> and <private_key> are credentials for a Service Account with edit access to your spreadsheet. See Initial setup.
• <spreadsheet_id> is the value between /d/ and /edit in your spreadsheet URL.
• <sheet_name> is the name of the sheet that you want to read from or write to.

Initial setup

1. Navigate to the Google API Console
2. Select a project from the drop-down box in the top bar.
3. Click Credentials (the Key icon) on the left navigation bar.
4. Click the Create credentials drop-down box, and select Service account key.
5. Click the Select… drop-down box, and select New service account. Enter a Service account name. For Role, select Project › Editor.
6. For Key type, select JSON.
7. Click the Create button. A JSON file with the Service Account credentials will be generated. Note the client_email and private_key values in the generated JSON file.

1. Navigate to your spreadsheet.
2. Click the Share button on the top-right corner of the page.
3. In the Enter names or email addresses… text box, enter the client_email of the Service Account, then click the Send button.

Assumptions

1. Data is row-based. Each field is stored on a column.
2. Row 1 of the sheet contains the headers for the data. “Actual” data starts from Row 2.

Example spreadsheet

API

const { createSpreadsheet, getSpreadsheet } = require('@yuanqing/google-sheets')

Spreadsheet

const spreadsheet = await createSpreadsheet(serviceAccountCredentials)

Creates a new spreadsheet and returns a Promise for it.

• serviceAccountCredentials is an object literal with the following keys:

  Key | Description :-|:- clientEmail | Email address of the Service Account that has edit access to the spreadsheet. privateKey | Private key of the Service Account.

const spreadsheet = await getSpreadsheet(serviceAccountCredentials, spreadsheetId)

Returns a Promise for an existing spreadsheet.

• spreadsheetId is the value between /d/ and /edit in the spreadsheet URL.

const sheet = await spreadsheet.createSheet(spreadsheetName, headers)

Creates a new sheet and returns a Promise for it.

• spreadsheetName is the name of the new sheet.
• headers is an array of headers for the new sheet.

const sheet = await spreadsheet.getSheet(spreadsheetName)

Returns a Promise for an existing sheet.

Sheet

const rows = await sheet.getAllRows()

Returns a Promise for an array containing all the rows from the sheet.

const rows = await sheet.getRowsByRange(m, n)

Returns a Promise for an array containing rows from the row m to row n of the sheet.

await sheet.addRows(rows)

Appends the given array of rows to the sheet, and returns a Promise that resolves.

Installation

$ yarn add @yuanqing/google-sheets

License

MIT