Is there a RESTful Google Sheets API for reading rows from one sheet?

Question

Here's my Google Sheet in comma-separated form:

ROW, TITLE
1, "Green eggs and ham"
2, "War and peace"
3, "Good Burger: The Movie: The Book"

What URL must I enter into my browser to get this data in JSON or XML?

Answer 1

THIS ANSWER IS NOW OBSOLETE!

This no longer works since the Sheets API changing to version 4.

Here is what is working for me to get XML

https://spreadsheets.google.com/feeds/list/yourSpreadsheetID/od6/public/values

and for JSON:

https://spreadsheets.google.com/feeds/list/yourSpreadsheetID/od6/public/values?alt=json

That is from a spreadsheet that is published to the web. If the sheet is not published to the web, I get an error:

We're sorry. This document is not published.

Here's what I could get for information from the documentation. If any info is wrong, anyone who knows better, please comment.

The documentation for the Sheets REST API is at the following link:

Google Sheets API

The way any REST Api works, is that an HTTPS GET or POST request must be made. Don't look at the client library's for other languages like GO, Java, .NET, etc. if you want to use the REST API.

The only way that you can get information out of a spreadsheet without going through the authorization process, is if that spreadsheet is published to the web. So, if you are trying to get data out of a spreadsheet with the REST Api that you don't want to publish to the web, you need to go through the authorization process.

Quote from Google documentation:

When your application requests non-public user data, it must include an authorization token. The token also identifies your application to Google.

Authorizing Requests

A URL typed into the browser's address bar is a GET request. Anything that requires a POST request could not be used in the browser's address bar. Even though a GET request can be made in the browser's address bar, some HTTP requests still need an appropriate authorization header. Which I don't think you can do in the browser's address bar.

Answer 2

Try Sheetsu. You can generate rest api's with it based on a Google Spreadsheet url.

Answer 3

I think you are looking for this: https://developers.google.com/sheets/api/reference/rest/#rest-resource-v4spreadsheetsvalues

REST Resource: v4.spreadsheets Methods

batchUpdate     POST /v4/spreadsheets/{spreadsheetId}:batchUpdate Applies one or more updates to the spreadsheet.
create          POST /v4/spreadsheets Creates a spreadsheet, returning the newly created spreadsheet.
get             GET /v4/spreadsheets/{spreadsheetId} Returns the spreadsheet at the given ID.
getByDataFilter     POST /v4/spreadsheets/{spreadsheetId}:getByDataFilter

Returns the spreadsheet at the given ID.

Answer 4

I have been looking for a solution myself and came across https://steinhq.com. It is an Open Source solution and SaaS.

# Adds a row to spreadsheet
$ curl "https://api.steinhq.com/v1/storages/5cc158079ec99a2f484dcb40/Sheet1" \
    -X POST \
    -H "Content-Type: application/json" \
    -d '[{"title": "...", "author": "...", ...}, ...]'