gsheets

Get public Google Sheets as plain JavaScript/JSON.

👉 Try gsheets in your browser

Usage

Node.js

Node.js >= 4 is required.

npm install gsheets

require('isomorphic-fetch');
const gsheets = require('gsheets');

gsheets.getWorksheet('1iOqNjB-mI15ZLly_9lqn1hCa6MinqPc_71RoKVyCFZs', 'foobar')
  .then(res => console.log(res), err => console.error(err));

In the browser

<script src="https://unpkg.com/[email protected]/gsheets.polyfill.min.js"></script>
<script>
  gsheets.getWorksheet('1iOqNjB-mI15ZLly_9lqn1hCa6MinqPc_71RoKVyCFZs', 'foobar')
    .then(res => console.log(res), err => console.error(err));

</script>

On the Command Line

npm install gsheets -g

gsheets --key=1iOqNjB-mI15ZLly_9lqn1hCa6MinqPc_71RoKVyCFZs --title=foobar --pretty

Compatibility Note

gsheets uses the Fetch API and Promises. Depending on your environment, you'll need to polyfill those. Recommendations:

• isomorphic-fetch
• es6-promise

For direct usage in the browser, there's a pre-built version of gsheets which the polyfills at gsheets.polyfill.js and gsheets.polyfill.min.js.

Features

• Plain JS/JSON data. No 'models'. Just use .map, .filter etc.
• Correct handling of numeric cells (no formatted strings for numbers!)
• Empty cells are converted to null
• A bit of metadata (i.e. when a spreadsheet was updated)
• Empty rows are omitted
• Correct handling of empty worksheets

Non-features

• Authorization (only works with published spreadsheets)
• Querying, ordering, updating
• Caching. Use a reverse proxy or implement your own caching strategy. I recommend this strongly since Google's API isn't the fastest and you don't want to hit rate limits.

Caveats

• Beware of cells formatted as dates! Their values will be returned as Excel-style DATEVALUE numbers (i.e. based on the number of days since January 1, 1900)

Why not use another library?

There are a few libraries around which allow you to access Google Spreadsheets, most notably Tabletop. However, they all have one or several drawbacks:

• They wrap the output in classes or models with a custom API, whereas all we really need is an array of JS objects
• Tabletop just logs errors to the console which makes proper error handling impossible
• Incorrect handling of numeric cell values (you only get a formatted string instead of the actual number, e.g. "123'456.79" instead of 123456.789)

API

var gsheets = require('gsheets');

getSpreadsheet(spreadsheetKey: string): Promise

Returns information about a spreadsheet including a list of worksheets.

gsheets.getSpreadsheet('MY_KEY')
  .then(res => console.log(res));

Example Response:

{
  "updated": "2014-11-19T10:20:18.068Z",
  "title": "My Awesome Spreadsheet",
  "worksheets": [
    {
      "id": "od6",
      "title": "foobar"
    },
    // more worksheets ...
  ]
}

getWorksheet(spreadsheetKey: string, worksheetTitle: string): Promise

Returns the contents of a worksheet, specified by its title. Note that this generates two requests (to resolve a worksheet's title). If you know a worksheet's ID (e.g. via a previous call to listWorksheets), use getWorksheetById

For empty worksheets data is null.

gsheets.getWorksheet('MY_KEY', 'foobar')
  .then(res => console.log(res));

Example Response:

{
  "updated": "2014-11-19T10:20:18.068Z",
  "title": "foobar",
  "data": [
    {
      "foo": "bar",
      "baz": 42,
      "boing": null
    },
    // more rows ...
  ]
}

getWorksheetById(spreadsheetKey: string, worksheetId: string): Promise

Returns the contents of a worksheet, specified by its ID.

For empty worksheets data is [].

gsheets.getWorksheetById('MY_KEY', 'od6')
  .then(res => console.log(res));

Example Response:

{
  "updated": "2014-11-19T10:20:18.068Z",
  "title": "foobar",
  "data": [
    {
      "foo": "bar",
      "baz": 42,
      "boing": null
    },
    // more rows ...
  ]
}

Command Line

Write spreadsheet contents to a file as JSON or DSV.

gsheets --key [--id] [--title] [--out] [--pretty] [--dsv]
  --key     Spreadsheet key; Outputs spreadsheet info if no other option is provided
  --out     Output file; defaults to /dev/stdout
  --id      Worksheet ID; use either this or --title to get worksheet contents
  --title   Worksheet title; use either this or --id to get worksheet contents
  --pretty  Pretty-print JSON
  --dsv     Format as delimiter-separated values
  --csv     Shortcut for --dsv=,
  --tsv     Shortcut for --dsv=$'\t'

Development

Run the tests with

npm run test:watch

Have a look at the test spreadsheet

Publish a new version with

npm run shipit

Author

Jeremy Stucki, Interactive Things

License

BSD, see LICENSE