Google Spreadsheet Data API -- simple interface to read/write rows + cells
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.
var GoogleSpreadsheet = require"google-spreadsheet";// spreadsheet key is the long id in the sheets URLvar my_sheet = '<spreadsheet key>';// Without auth -- read only// IMPORTANT: See note below on how to make a sheet public-readable!// # is worksheet id - IDs start at 1my_sheetgetRows 1console.log 'pulled in '+row_datalength + ' rows';;// With auth -- read + write// see below for authentication instructionsvar creds = require'./google-generated-creds.json';// OR, if you cannot save the file locally (like on heroku)var creds =client_email: 'email@example.com'private_key: 'your long private key stuff here'my_sheetuseServiceAccountAuthcreds// getInfo returns info about the sheet and an array or "worksheet" objectsmy_sheetgetInfoconsole.log sheet_infotitle + ' is loaded' ;// use worksheet object if you want to stop using the # in your callsvar sheet1 = sheet_infoworksheets0;sheet1getRowsrows0colname = 'new val';rows0save; //async and takes a callbackrows0del; //async and takes a callback;;// column names are set by google and are based// on the header row (first row) of your sheetmy_sheetaddRow 2 colname: 'col value' ;my_sheetgetRows 2start: 100 // start indexnum: 100 // number of rows to pullorderby: 'name' // column to order results by// do something...;
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.
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.
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)
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
visibility- defaults to
projection- defaults to
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.
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
worksheets- an array of
SpreadsheetWorksheetobjects (see below)
Get an array of row objects from the sheet.
worksheet_id- the index of the sheet to read from (index starts at 1)
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)
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
Get an array of cell objects.
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)
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.
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
Represents a single row from a sheet.
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
node-google-spreadsheets is free and unencumbered public domain software. For more information, see the accompanying UNLICENSE file.