voidit/gsheets

Get public Google Sheets as plain JavaScript/JSON

gsheets

Get public Google Sheets as plain JavaScript/JSON.

👉 Try gsheets in your browser

Usage

Google Sheets Api Key

Starting with version 3.0.0, gsheets uses the official Google Sheets API v4, which requires an API key. To get an API key follow these instructions. You also need to enable the Sheets API for the project you've generated the API key for.

Node.js

Node.js >= 12 is required.

npm install gsheets

const gsheets = require("gsheets");

gsheets
  .getWorksheet("SPREADSHEET_KEY", "WORKSHEET_TITLE", "GSHEETS_API_KEY")
  .then(
    (res) => console.log(res),
    (err) => console.error(err)
  );

In the Command Line

gsheets --key --title [--apiKey] [--out] [--pretty] [--dsv]
  --key     Spreadsheet Key (ID), required
  --title   Worksheet title, required

  --apiKey  Google Sheets v4 API key,
  --out     Output file; defaults to /dev/stdout
  --dsv     Format as delimiter-separated values
  --csv     Shortcut for --dsv=,
  --tsv     Shortcut for --dsv=$'\t'

Supports providing the apiKey via arg:

npx gsheets --key=SPREADSHEETID --title=foobar --apiKey=GSHEETS_API_KEY --pretty

or via env:

npx GSHEETS_API_KEY=xyz gsheets --key=SPREADSHEETID --title=foobar --pretty

if you are using dotenv in your project, you can specify GSHEETS_API_KEY as a var in your .env file and run:

NODE_OPTIONS='-r dotenv/config' gsheets --key=SPREADSHEETID--title=foobar --pretty

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
• Empty rows and empty columns are omitted
• Empty worksheets returns an object with an empty data array: {data: []}

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");

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

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

For empty worksheets data is [].

gsheets
  .getWorksheet("SPREADSHEET_KEY", "WORKSHEET_TITLE", "GSHEETS_API_KEY")
  .then((res) => console.log(res));

Example Response:

{
  "data": [
    {
      "foo": "bar",
      "baz": 42,
      "boing": null
    },
    // more rows ...
  ]
}

Development

Replace "YOUR_GSHEETS_API_KEY" in index.tests.js with your key, then 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