GSX2JSON - Google Spreadsheet to JSON API service.

Warning

Please do not use this hosted version for anything important. I am not able to quickly react if Google updates their API, or if the Heroku app crashes. If you want to use this for something important, I suggest Self Hosting.

It is VERY likely that this service will hit the Google rate limit on their new V4 API. Therefore, although an api_key parameter is listed as optional, I STRONGLY suggest you use it.

I do not accept any responsibility for issues caused by use of this service.

Self hosting

You can download the source code here

About

One useful feature of Google Spreadsheets is the ability to access the data as JSON by using a particular feed URL. However, this is a bit fiddly to do, and the resulting JSON is pretty unreadable, with usable data buried deep inside objects.

This API connects to your spreadsheet and santizes the data, providing simple, readable JSON, that accepts queries, for you to use in your app.

Usage

First, you must make sure your Google Sheet is set to be shared to 'anyone with the link'.

You can then access your readable JSON API using the /api endpoint.

http://gsx2json.com/api?id=SPREADSHEET_ID&sheet=SHEET_NAME&q=QUERY

This will update live with changes to the spreadsheet.

Take a look

Parameters

api_key (optional): Your Google Developer API Key for use with Google Sheets. This is optional as I have one in the app, but if you want your own rate limits etc I suggest you get one and add it as a parameter.

id (required): The ID of your document. This is the big long aplha-numeric code in the middle of your document URL.

sheet (required): The name of the individual sheet you want to get data from. Since the update to Google V4 API this now needs to be a name rather than an ID. Example

q (optional): A simple query string. This is case insensitive and will add any row containing the string in any cell to the filtered result. Example

[field_name] (optional): In addition to a general query, you can do a query on any of your fields by referencing the field name. This does a case insensitive match, but uses a '===' operator so must match the full string.
NOTE: The field name is based on how it appears in the returned JSON, so a field in Google Sheets that is 'My Field' should be queried as 'myfield' Example

integers (optional - default: true): Setting 'integers' to false will return numbers as a string (useful for decimal points). Example

rows (optional - default: true): Setting 'rows' to false will return only column data. Example

columns (optional - default: true): Setting 'columns' to false will return only row data. Example

Example Response

There are two sections to the returned data - Columns (containing each column as a data array), and Rows (containing each row of data as an object. 

{
    columns: {
        name: [
            "Nick",
            "Chris",
            "Barry"
        ],
        age: [
            21,
            27,
            67;
        ]
    },
    rows: [
        {
            name: "Nick",
            age: 21
        },
        {
            name: "Chris",
            age: 27
        },
        {
            name: "Barry",
            age: 67
        }
    ]
}

Notes