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.
npm install google-spreadsheet
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...
});
})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).
- 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
This is a fairly major rewrite of code by samcday. original version here Also big thanks fo GoogleClientLogin for dealing with authentication.
node-google-spreadsheets is free and unencumbered public domain software. For more information, see the accompanying UNLICENSE file.