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.

<span style="color: #24292e;"></span>
<span style="color: #24292e;">+ id=<spreadsheet key></span>
<span style="color: #24292e;">+ sheet=<sheet name on spreadsheet>
</span>

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: https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec?id=0AgviZ9NWh5fvdDdNMlI2aXRCR2lCX1B1alZ6ZjZxSkE&sheet=Summary&header=2&startRow=3

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: 
    <span class="pln">https</span><span class="pun">:</span><span class="com">//sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/RANGE?key=API_KEY</span>

You’ll then get a clean JSON response back:

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

Note that if you want to specify the entire contents of a page, an identifier such as <span style="color: #242729;">Sheet1</span> 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

Github: https://github.com/acbrunso/Tutorial_…