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.