google-spreadsheet

What is google-spreadsheet?

The google-spreadsheet npm package allows you to interact with Google Sheets through the Google Sheets API. It provides a simple and intuitive way to read, write, and manage Google Sheets data programmatically.

What are google-spreadsheet's main functionalities?

Authentication

This feature allows you to authenticate with the Google Sheets API using either service account credentials or OAuth2 tokens.

const { GoogleSpreadsheet } = require('google-spreadsheet');
const doc = new GoogleSpreadsheet('<spreadsheet-id>');

// Using service account credentials
await doc.useServiceAccountAuth(require('./path/to/credentials.json'));

// OR using OAuth2 tokens
await doc.useOAuth2Client(oAuth2Client);

Loading a Spreadsheet

This feature allows you to load the spreadsheet's metadata and worksheets, making it possible to interact with the spreadsheet's structure and data.

await doc.loadInfo(); // loads document properties and worksheets
console.log(doc.title);

Reading Rows

This feature allows you to read rows from a specific sheet within the spreadsheet. You can access individual cell values and iterate through the rows.

const sheet = doc.sheetsByIndex[0]; // or use doc.sheetsById[id]
const rows = await sheet.getRows(); // can pass in { limit, offset }
console.log(rows[0].name);

Adding Rows

This feature allows you to add new rows to a sheet within the spreadsheet. You can specify the values for each column in the new row.

await sheet.addRow({ name: 'John Doe', email: '[e-mail]' });

Updating Cells

This feature allows you to update the values of specific cells in a row and save the changes back to the spreadsheet.

const row = rows[0];
row.name = 'Jane Doe';
await row.save();

Other packages similar to google-spreadsheet

googleapis

The googleapis package is a comprehensive library for interacting with various Google APIs, including the Google Sheets API. It provides more extensive functionality beyond just Google Sheets, but it requires more setup and configuration compared to google-spreadsheet.

gsheets

The gsheets package is a lightweight library for accessing Google Sheets data. It offers basic functionalities for reading and writing data but lacks some of the advanced features and ease of use provided by google-spreadsheet.

g-sheets-api

The g-sheets-api package is designed for simple and quick access to Google Sheets data. It is less feature-rich compared to google-spreadsheet but can be a good choice for straightforward use cases where minimal setup is desired.

README

Simple Google Spreadsheet Access (node.js)

A simple Node.js library to read and manipulate data in Google Spreadsheets.

Works without authentication for read-only sheets or with auth for adding/editing/deleting data. Supports both list-based and cell-based feeds.

Installation

Basic Usage

var GoogleSpreadsheet = require("google-spreadsheet");

// spreadsheet key is the long id in the sheets URL
var my_sheet = new GoogleSpreadsheet('<spreadsheet key>');

// Without auth -- read only
// IMPORTANT: See note below on how to make a sheet public-readable!
// # is worksheet id - IDs start at 1
my_sheet.getRows( 1, function(err, row_data){
	console.log( 'pulled in '+row_data.length + ' rows');
});

// With auth -- read + write
// see below for authentication instructions
var creds = require('./google-generated-creds.json');
// OR, if you cannot save the file locally (like on heroku)
var creds = {
  client_email: '[e-mail]',
  private_key: 'your long private key stuff here'
}

my_sheet.useServiceAccountAuth(creds, function(err){
	// getInfo returns info about the sheet and an array or "worksheet" objects
	my_sheet.getInfo( function( err, sheet_info ){
		console.log( sheet_info.title + ' is loaded' );
		// use worksheet object if you want to stop using the # in your calls

		var sheet1 = sheet_info.worksheets[0];
		sheet1.getRows( function( err, rows ){
			rows[0].colname = 'new val';
			rows[0].save();	//async and takes a callback
			rows[0].del();  //async and takes a callback
		});
	});

	// column names are set by google and are based
  // on the header row (first row) of your sheet
	my_sheet.addRow( 2, { colname: 'col value'} );

	my_sheet.getRows( 2, {
		start: 100,			 // start index
		num: 100,			   // number of rows to pull
		orderby: 'name'  // column to order results by
	}, function(err, row_data){
		// do something...
	});
})

Authentication

IMPORTANT: Google recently deprecated their ClientLogin (username+password) access, so things are slightly more complicated now. Older versions of this module supported it, so just be aware that things changed.

Unauthenticated access (read-only access on public docs)

By default, this module makes unauthenticated requests and can therefore only access spreadsheets that are "public".

The Google Spreadsheets Data API reference and developers guide is a little ambiguous about how you access a "published" public Spreadsheet.

If you wish to work with a Google Spreadsheet without authenticating, not only must the Spreadsheet in question be visible to the web, but it must also have been explicitly published using "File > Publish to the web" menu option in the google spreadsheets GUI.

Many seemingly "public" sheets have not also been "published" so this may cause some confusion.

Service Account (recommended method)

This is a 2-legged oauth method and designed to be "an account that belongs to your application instead of to an individual end user". Use this for an app that needs to access a set of documents that you have full access to. (read more)

Setup Instructions

1. Go to the Google Developers Console
2. Select your project or create a new one (and then select it)
3. Enable the Drive API for your project

• In the sidebar on the left, expand APIs & auth > APIs
• Search for "drive"
• Click on "Drive API"
• click the blue "Enable API" button

1. Create a service account for your project

• In the sidebar on the left, expand APIs & auth > Credentials
• Click blue "Add credentials" button
• Select the "Service account" option
• Select the "JSON" key type option
• Click blue "Create" button
• your JSON key file is generated and downloaded to your machine (it is the only copy!)
• note your service account's email address (also available in the JSON key file)

1. Share the doc (or docs) with your service account using the email noted above

API

GoogleSpreadsheet

The main class that represents an entire spreadsheet.

new GoogleSpreadsheet(sheet_id, [auth], [options])

Create a new google spreadsheet object.

• sheet_id -- the ID of the spreadsheet (from its URL)
• auth - (optional) an existing auth token
• options - (optional)
  • visibility - defaults to public if anonymous
  • projection - defaults to values if anonymous

GoogleSpreadsheet.useServiceAccountAuth(account_info, callback)

Uses a service account email and public/private key to create a token to use to authenticated requests. Normally you would just pass in the require of the json file that google generates for you when you create a service account.

See the "Authentication" section for more info.

If you are using heroku or another environment where you cannot save a local file, you may just pass in an object with

• client_email -- your service account's email address
• private_key -- the private key found in the JSON file

Internally, this uses a JWT client to generate a new auth token for your service account that is valid for 1 hour. The token will be automatically regenerated when it expires.

GoogleSpreadsheet.setAuthToken(id)

Use an already created auth token for all future requets.

GoogleSpreadsheet.getInfo(callback)

Get information about the spreadsheet. Calls callback passing an object that contains:

• id - the URL/id as returned from google
• title - the title of the document
• updated - last updated timestamp
• author - auth info in an object
  • name - author name
  • email - author email
• worksheets - an array of SpreadsheetWorksheet objects (see below)

GoogleSpreadsheet.getRows(worksheet_id, options, callback)

Get an array of row objects from the sheet.

• worksheet_id - the index of the sheet to read from (index starts at 1)
• options (optional)
  • start-index - start reading from row #
  • max-results - max # of rows to read at once
  • orderby - column key to order by
  • reverse - reverse results
  • query - send a structured query for rows (more info)
• callback(err, rows) - will be called with an array of row objects (see below)

GoogleSpreadsheet.addRow(worksheet_id, new_row, callback)

Add a single row to the sheet.

• worksheet_id - the index of the sheet to add to (index starts at 1)
• new_row - key-value object to add - keys must match the header row on your sheet
• callback(err) - callback called after row is added

GoogleSpreadsheet.getCells(worksheet_id, options, callback)

Get an array of cell objects.

• worksheet_id - the index of the sheet to add to (index starts at 1)
• options (optional)
  • min-row - row range min (uses #s visible on the left)
  • max-row - row range max
  • min-col - column range min (uses numbers, not letters!)
  • max-col - column range max
  • return-empty - include empty cells (boolean)

GoogleSpreadsheet.bulkUpdateCells(worksheet_id, cells, callback)

Do a bulk update on cells.

• worksheet_id - the index of the sheet to add to (index starts at 1)
• cells - an array of SpreadsheetCell objects to save

GoogleSpreadsheet.addWorksheet(options, callback)

Add a new worksheet to the doc.

• options (optional)
  • title - title for the new sheet (default = 'New Worksheet')
  • rowCount - number of rows (default = 50)
  • colCount - number of columns (default = 10)

GoogleSpreadsheet.deleteWorksheet(worksheet_id, callback)

Remove a worksheet from the doc.

• worksheet_id - the index of the sheet to add to (index starts at 1)

---

SpreadsheetWorksheet

Represents a single "sheet" from the spreadsheet. These are the different tabs/pages visible at the bottom of the Google Sheets interface.

This is a really just a wrapper to call the same functions on the spreadsheet without needing to include the worksheet id.

Properties:

• url - the URL for the sheet
• id - the ID of the sheet
• title - the title (visible on the tabs in google's interface)
• rowCount - number of rows
• colCount - number of columns

SpreadsheetWorksheet.getRows(options, callback)

See above.

SpreadsheetWorksheet.getCells(options, callback)

See above.

SpreadsheetWorksheet.addRow(new_row, callback)

See above.

SpreadsheetWorksheet.bulkUpdateCells(cells, callback)

See above.

SpreadsheetWorksheet.del(callback)

Remove this sheet from the doc.

---

SpreadsheetRow

Represents a single row from a sheet.

You can treat the row as a normal javascript object. Object keys will be from the header row of your sheet, however the google API mangles the names a bit to make them simpler. It's easiest if you just use all lowercase keys to begin with.

SpreadsheetRow.save( callback )

Saves any changes made to the row's values.

SpreadsheetRow.del( callback )

Deletes the row from the sheet.

---

SpreadsheetCell

Represents a single cell from the sheet. Using cells is the only way to read and modify the formulas in your sheet.

Properties:

• id - the ID of the cell
• row - the row this cell is in
• col - the column this cell is in
• value - the value of the cell
• numericValue - the value of the cell as a number
• inputValue - the "raw" value of the cell which can be a formula

IMPORTANT:

• Cells with regular values can be modified by setting value and calling save
• Cells with formulas in them can be modified by setting inputValue and calling save

SpreadsheetCell.setValue(val, callback)

Set the value of the cell and saves it.

SpreadsheetCell.save(callback)

Saves the current value/formula

SpreadsheetCell.del(callback)

Clear the cell -- internally just calls .setValue('', callback)

---

Further possibilities & to-do

• batch requests for cell based updates
• modifying worksheet/spreadsheet properties
• getting list of available spreadsheets for an authenticated user

Links

• https://developers.google.com/google-apps/spreadsheets/
• https://github.com/Ajnasz/GoogleClientLogin

Thanks

This is a fairly major rewrite of code by samcday. original version here Also big thanks fo GoogleClientLogin for dealing with authentication.

License

node-google-spreadsheets is free and unencumbered public domain software. For more information, see the accompanying UNLICENSE file.