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

NodeJS Google Spreadsheets Data API

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

Works without authentication for read-only sheets or with auth for adding/editing/deleting data. Currently only supports list-based feeds (dealing with rows) as it seems more useful, but will probably add in cell-based feeds eventually.

Installation

npm install google-spreadsheet

Basic Usage

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

var my_sheet = new GoogleSpreadsheet('<spreadsheet key>');

// without auth -- read only
// # is worksheet id - IDs start at 1
my_sheet.getRows( 1, function(err, row_data){
	console.log( 'pulled in '+row_data.length + ' rows ')
})

// set auth to be able to edit/add/delete
my_sheet.setAuth('<google email/username>','<google pass>', function(err){
	my_sheet.getInfo( function( err, sheet_info ){
		console.log( sheet_info.title + ' is loaded' );
		// use worksheet object if you want to forget about ids
		sheet_info.worksheets[0].getRows( function( err, rows ){
			rows[0].colname = 'new val';
			rows[0].save();
			rows[0].del();
		}
	}

	// column names are set by google based on the 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
	}, function(err, row_data){
		// do something...
	});
})

A note on authentication

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.

Generally, you'll find alot of public spreadsheets may not have had this treatment, so your best bet is to just authenticate a Google account and access the API in that manner.

This library uses googleclientlogin internally to provide basic authentication. Optionally you can pass in an auth token that you have created already (using googleclientlogin or something else).

Further possibilities & to-do

• Adding cell-based feeds (was in the original package)
• 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.