gsheets
    DefinitelyTyped icon, indicating that this package has TypeScript declarations provided by the separate @types/gsheets package

    3.0.1 • Public • Published

    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.

    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

    Install

    npm i gsheets

    DownloadsWeekly Downloads

    291

    Version

    3.0.1

    License

    BSD-3-Clause

    Unpacked Size

    11.1 kB

    Total Files

    6

    Last publish

    Collaborators

    • herrstucki
    • werehamster