google-spreadsheet

Google Spreadsheet Data API -- simple interface to read/write rows + cells

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. Supports both list-based and cell-based feeds.

npm install google-spreadsheet
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(errrow_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){
 
    // getInfo returns info about the sheet and an array or "worksheet" objects 
    my_sheet.getInfo( function( errsheet_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( errrows ){
            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 based on the header 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(errrow_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 a lot 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).

The main class that represents an entire spreadsheet.

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

Creates an auth token using a username and password. It will be used for all future requests. Internally uses googleclientlogin. Remember NEVER to save your google credentials in version control!

Use an already created auth token for all future requets.

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

  • 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)
  • 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

Add a row to the spreadsheet.

  • worksheet_id - the index of the sheet to add to (index starts at 1)
  • data - key-value object to add - keys should match the header row on your sheet
  • `callback(err)

Get an array of cell objects.

  • options (optional)
    • min-row + max-row - row range (uses #s visible on the left)
    • min-col + max-col - column range (uses numbers, not letters!)
    • return-empty - include empty cells (boolean)

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:

  • 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

See above.

See above.

See above.


Represents a single row from a sheet.

You can treat the row as a normal javascript 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.

Saves any changes made to the row's values.

Deletes the row from the sheet.


Represents a single cell from the sheet.

Set the value of the cell and save it.

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


  • 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.