Skip to content
Get public Google Sheets as plain JavaScript/JSON
Branch: master
Clone or download
Latest commit 012b074 Nov 23, 2016
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
bin
scripts
src
test
.babelrc
.flowconfig
.gitignore
.travis.yml
CHANGELOG.md
LICENSE
README.md
package.json
rollup.config.js

README.md

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/gsheets@next/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:

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

You can’t perform that action at this time.