Google SheetAsJSON + Filtering

This is an extension of DJ Adams’ excellent SheetAsJSON Google Apps Script, which provides a way to GET a published Google Spreadsheet as a JSON feed. This version allows generic filtering for terms, more specific control over which rows to parse, and correct MIME type for JSONP output.

Minimal Usage

The following parameters are required for the script to work.
+ id=<spreadsheet key>
+ sheet=<sheet name on spreadsheet>

Per the original, the above script serves a representation of all the sheet’s data as JSON, using the first row as the set of keys:

{ records : [
    { (row1, column1): (row2, column1), (row1, column2): (row2, column2), ..},
    { (row1, column1): (row3, column1), (row1, column2): (row3, column2), ..},

Try it:

Accessing a (new-style, public) Google sheet as JSON

If you want to use the latest API (v4), you’ll need to do the following:

  1. Generate a spreadsheets API key (see instructions below).
  2. Make your sheet publicly accessible.
  3. Use a request of the form:

You’ll then get a clean JSON response back:

  "range": "Sheet1!A1:D5",
  "majorDimension": "ROWS",
  "values": [
    ["Item", "Cost", "Stocked", "Ship Date"],
    ["Wheel", "$20.50", "4", "3/1/2016"],
    ["Door", "$15", "2", "3/15/2016"],
    ["Engine", "$100", "1", "30/20/2016"],
    ["Totals", "$135.5", "7", "3/20/2016"]

Note that if you want to specify the entire contents of a page, an identifier such as Sheet1 is perfectly valid.

See Basic Reading for more information.

As of v4 API, all requests must be accompanied by an identifier (e.g. API key):

Requests to the Google Sheets API for public data must be accompanied by an identifier, which can be an API key or an access token.

Google Sheets API – Read Data – Part 1

Read data from a Google Sheet from an HTML form using the Google Sheets API. Step by step instruction to create an API project, configure access credentials, obtain an oAuth token and read data from the sheet using HTML, PHP and Javascript.

This is part 1. Setting up a project, and testing the read functionality of the sheets api


The First Rule of Microsoft Excel—Don’t Tell Anyone You’re Good at It

At the same time, it has complicated the lives of the office Excel Guy or Gal, the virtuosos whose superior skills at writing formula leave them fighting an endless battle against the circular references, merged cells and mangled macros left behind by their less savvy peers.

.. “If someone tells you that they ‘just have a few Excel sheets’ that they want help with, run the other way,” tweeted 32-year-old statistician Andrew Althouse.
.. “Let’s just say that was a poor use of time,” he said. He advises altruistic Excel mavens to “figure out what you’re getting into” before offering to lend a hand.
.. As an Excel expert, “you become indispensable, and that’s a double-edged sword,” Mr. McIllece said.