NAV Navbar
shell python

Introduction

Welcome to the Sheetson API! You can use our API to access data in any Google Sheet file that you have access to. Basically, every Google Sheet file is a database, and each tab within that file is a table. Sheetson API is designed around these definition.

The Sheetson API is a RESTful API based on HTTP requests and JSON responses, as hinted in its name ;). If you're familiar with the APIs of Parse Server, Twitter or many other services, you are at the right place.

API Endpoint

All API calls must be made to https://api.sheetson.com

JSON-only

All responses will be in JSON. Input data passed through the request body can be form-encoded or JSON-encoded. If using a JSON body, please specify the Content-Type header as application/json.

String-only

Currently, all values in row object are returned with an exception of rowIndex which is automatically added to the results set.

Glossary

Following this documentation, we will be using below terms:

Getting Started

Preparing your spreadsheet

Although it is not mandatory, you should always identify the first row of your sheet as header. You can use any character or any length, but it is recommended to use alphanumeric characters only. Take this sheet below as an example

alt text

It should return a JSON like this:

  
  {
    "results":[
      {
        "rowIndex": 1,
        "rank": "1",
        "city": "Vienna",
        "country": "Austria"
      },
      {
        "rowIndex": 2,
        "rank":"2",
        "city":"Z├╝rich",
        "country":"Switzerland"
      },
      .....
    ]
  }
  

Sharing spreadsheet

alt text

Share with edit access to this email: [email protected]

Identify spreadsheetId and sheetName

In order to instruct Sheetson to fetch your data, it is required that you pass spreadsheetId in header or parameter and replace sheetName with your actual sheet name. Below screenshot demonstrates how you can find these information.

alt text

For example, the screenshot spreadsheetId and sheetName are 1h-Eet6qTsPrdL5IVwiAvPXH47wjEludJVpwrpdCWEuM and LivingQuality respectively.

Then the URL you need to use to fetch list of resources is:

https://api.sheetson.com/v1/sheets/LivingQuality?spreadsheetId=1h-Eet6qTsPrdL5IVwiAvPXH47wjEludJVpwrpdCWEuM

Status Codes

Every API request to Sheetson API returns a HTTP status code that reflect the result. Following codes should be returned by Sheetson API.

Successful requests

Code Meaning
200 OK Request successful
201 Created A resource has been created (POST request)
204 No Content A resource has been deleted (DELETE request)

Error requests

Code Meaning
400 Bad Request Invalid request parameters
401 Unauthorized Incorrect or missing API key, spreadsheetId or sheetName

All error responses will contain an object with below attributes:

Parameter Description
code Numeric value of the error
message Detailed explanation of the error

Pagination

All operations that return array of objects will be paginated using skip and limit parameters. skip starts at 0, while limit is defaulted to 24.

API Reference

Retrieve multiple rows

curl "http://api.sheetson.com/v1/sheets/Demo"
  -H "X-Spreadsheet-Id: 1h-Eet6qTsPrdL5IVwiAvPXH47wjEludJVpwrpdCWEuM"

Example response from demo sheet

{
  "results": [
    {
      "rowIndex": 1,
      "username": "acalken0",
      "avatar": "https://robohash.org/quiailloin.jpg?size=50x50&set=set1",
      "email": "[email protected]",
      "gender": "Female"
    },
    {
      "rowIndex": 2,
      "username": "aodocherty1",
      "avatar": "https://robohash.org/eaquequiquia.jpg?size=50x50&set=set1",
      "email": "[email protected]",
      "gender": "Male"
    }
  ]
}

This endpoint retrieve records from sheetName tab

HTTP Request

GET /{sheetName}

URL Parameter

Parameter Description
sheetName Name of the sheet containing data

Query Parameters

Parameter Description
spreadsheetId Required if X-Spreadsheet-Id header is not presented
limit Limit number of rows to be returned - default is 24
skip Number of rows skipped - default is 0
keys Specify what fields to be returned. Separated by comma.

Response Body

An array of objects. Each object is a row in a sheet and has header row as object keys.

Retrieve a single row

curl "http://api.sheetson.com/v1/sheets/{sheetName}/1"
  -H "X-Spreadsheet-Id: 1h-Eet6qTsPrdL5IVwiAvPXH47wjEludJVpwrpdCWEuM"

Example response from demo sheet

{
  "rowIndex": 1,
  "username": "acalken0",
  "avatar": "https://robohash.org/quiailloin.jpg?size=50x50&set=set1",
  "email": "[email protected]",
  "gender": "Female"
}

This endpoint return data at a specific row.

HTTP Request

GET /{sheetName}/{rowIndex}

URL Parameter

Parameter Description
sheetName Name of the sheet containing data
rowIndex Index of row to return

Response Body

Object which is a row in a sheet and has header row as object keys.

Create a specific row

This endpoint create a new row at the last index

HTTP Request

POST /{sheetName}

URL Parameter

Parameter Description
sheetName Name of the sheet containing data

Response Body

Object which is a newly-created row in a sheet and has header row as object keys.

Update a specific row

curl "http://api.sheetson.com/v1/sheets/Demo/1"
  -X PATCH
  -H "X-Spreadsheet-Id: 1h-Eet6qTsPrdL5IVwiAvPXH47wjEludJVpwrpdCWEuM"
  -H "Content-Type: application/json"
  -d '{"email" : "[email protected]"}'
from urllib.request import Request

req = Request(
    "http://api.sheetson.com/v1/sheets/Demo/1",
    json.dumps({"email" : "[email protected]"}).encode('utf-8'),
    {"Content-Type": "application/json"}
res = request.urlopen(req)
print(res)

Example response from Demo sheet

  {
    "rowIndex": 1,
    "email": "[email protected]"
  }

This endpoint update data at a specific row

HTTP Request

PATCH /{sheetName}/{rowIndex}

URL Parameter

Parameter Description
sheetName Name of the sheet containing data
rowIndex Index of row to return

Response Body

Object with rowIndex and requesting data

Delete a specific row

curl "http://api.sheetson.com/v1/sheets/{sheetName}/{rowIndex}"
  -X DELETE
  -H "X-Spreadsheet-Id: 1h-Eet6qTsPrdL5IVwiAvPXH47wjEludJVpwrpdCWEuM"

This endpoint deletes a specific row in a sheet

HTTP Request

DELETE /{sheetName}/{rowIndex}

URL Parameter

Parameter Description
sheetName Name of the sheet containing data
rowIndex Index of row to return

URL Parameter

Parameter Description
sheetName Name of the sheet containing data
rowIndex Index of row to delete

Response Body

None