API docs

Versioning and API URL

In the new version of the API all URLs have version. Example URL:
https://sheetsu.com/apis/v1.0/020b2c0f

Example spreadsheet for docs

Every Spreadsheet's first row should contain column names. Check an example here. All examples are shown for the below Spreadsheet:

idnamescore
1Peter43
2Lois89
3Meg10
4Chris43
5Stewie72

Rate Limits

Every API has a rate limit. You can check it here. After this you will get the status code 429 Rate limit exceeded.

HTTP Status Codes

Every response from the API is a JSON. There's always a proper status code in the HEAD part of the response. Below are listed all the available response codes.

HTTP Status codes

Status Code
200 OKStandard response for successful GET, PUT and PATCH requests
201 CreatedSuccessful response for POST requests
204 No ContentSuccessful response for DELETE requests
400 Bad RequestStatus code when there's an error when creating or updating row(s).
401 UnauthorizedReturned when request has wrong authorization credentials
402 Payment RequiredReturned if pro feature is tried to be accessed from free account
403 ForbiddenResponse code returned when action is forbidden by the user (Spreadsheet/API owner)
404 No such routeReturned when route which doesn't exists is requested
429 Rate limit exceededReturned when you have more hits than your quota
500Server error

Accessing multiple sheets Pro feature

You can access all sheets within one spreadsheet, using one API endpoint. To access other sheets you need to add /sheets/:sheet_name_or_number after ID of a sheet in API URL.

Example

If you have API URL like - https://sheetsu.com/apis/v1.0/020b2c0f and you want to access the sheet named AwesomeCalculations you need to change your URL to https://sheetsu.com/apis/v1.0/020b2c0f/sheets/AwesomeCalculations.

Remember to always put /sheets/:sheet_name right after the sheet ID (slug) and before the rest part of the URL.

If you want to use GET /:column/:value with sheet named AwesomeCalculations you should call: https://sheetsu.com/apis/v1.0/020b2c0f/sheets/AwesomeCalculations/column_name/some_value. The same thing applies to other HTTP verbs.

So if you want to change some value in AwesomeCalculations sheet using PUT method you should call:
PUT https://sheetsu.com/apis/v1.0/020b2c0f/sheets/AwesomeCalculations/name/Lois -d { "score": "100" }
This will change the value of column score to 100 in all rows where column name equals Lois in the sheet named AwesomeCalculations.

Another example: https://sheetsu.com/apis/v1.0/020b2c0f/sheets/AwesomeCalculations/search?name=Lois&score=90.

Spaces in sheet names

If your sheet has spaces in its name you need to replace them with %20. The same rules apply to the API URLs as to any other HTTP URLs.

Sheet names vs sheet numbers

You can use sheet name or sheet number. Sheets are numbered from 0.
First sheet - 0 - /sheets/0
Second sheet - 1 - /sheets/1
etc.

Default values

If you didn't pass the /sheets/:sheet_name_or_number by default all operations will be performed on the first sheet.

Accessibility

This feature is available only for Startup and Agency accounts.

GET /
GET https://sheetsu.com/apis/v1.0/020b2c0f

Returns an array with all rows from spreadsheet. Each array's element is hash of row. There is optional parameter fields. If fields parameter is given, only those column will be returned in the row hash. Values in fields need to be comma separated.

Parameters

fields (optional) Comma separated fields which should be returned from the row
limit (optional) Number of how many rows should be returned
offset (optional) Number from which row response should start (default is 0)
Example Request
curl https://sheetsu.com/apis/v1.0/020b2c0f
curl https://sheetsu.com/apis/v1.0/020b2c0f?fields=name,score
curl https://sheetsu.com/apis/v1.0/020b2c0f?fields=id,name
curl https://sheetsu.com/apis/v1.0/020b2c0f?limit=2
curl https://sheetsu.com/apis/v1.0/020b2c0f?offset=2
curl https://sheetsu.com/apis/v1.0/020b2c0f?limit=1&offset=1&fields=id,name
require 'open-uri'
require 'json'
require 'pp'

uri = 'https://sheetsu.com/apis/v1.0/020b2c0f'
path = '/'
params = '?fields=id,name&limit=2&offset=1' # optional
url = uri + path + params

buffer = open(url).read
result = JSON.parse(buffer)

pp result

The easiest way to do it is to use any of the other language, like JS. Below examples shows how to populate HTML website using Java Script and jQuery.

 <html>
<head>
<script src="https://code.jquery.com/jquery-3.0.0-alpha1.js"></script>
</head>

<body>
<ul id="characters-list"></ul>

<script>
var sheetsuUrl = "https://sheetsu.com/apis/v1.0/020b2c0f";

$.ajax({
url: sheetsuUrl,
dataType: 'json',
type: 'GET',

// place for handling successful response
success: function(data) {
addCharacters(data);
},

// handling error response
error: function(data) {
console.log(data);
}
});

addCharacters = function(characters) {
var list = $('#characters-list');
for(var i=0; i<characters.length; i+=1) {
char = characters[i];
html = "<li id=" + char.id + "><h3>" + char.name + "</h3><p>Score: " + char.score + "</p></li>";
list.append(html);
}
}
</script>
</body>
</html>
Example Result
# GET https://sheetsu.com/apis/v1.0/020b2c0f
[
{ id: '1', name: 'Peter', score: '43' },
{ id: '2', name: 'Lois', score: '89' },
{ id: '3', name: 'Meg', score: '10' },
{ id: '4', name: 'Chris', score: '43' },
{ id: '5', name: 'Stewie', score: '72' }
]

# GET https://sheetsu.com/apis/v1.0/020b2c0f?fields=id,name
[
{ id: '1', name: 'Peter' },
{ id: '2', name: 'Lois' },
{ id: '3', name: 'Meg' },
{ id: '4', name: 'Chris' },
{ id: '5', name: 'Stewie' }
]

# GET https://sheetsu.com/apis/v1.0/020b2c0f?limit=2
[
{ id: '1', name: 'Peter', score: '43' },
{ id: '2', name: 'Lois', score: '89' },
]

# GET https://sheetsu.com/apis/v1.0/020b2c0f?offset=2
[
{ id: '3', name: 'Meg', score: '10' },
{ id: '4', name: 'Chris', score: '43' },
{ id: '5', name: 'Stewie', score: '72' }
]

# GET https://sheetsu.com/apis/v1.0/020b2c0f?limit=1&offset=1&fields=id,name
[
{ id: '2', name: 'Lois' },
]

GET /:column/:value
GET https://sheetsu.com/apis/v1.0/020b2c0f/:column/:value

Return array of all rows from spreadsheet where column's value of :column equals :value. Each element of array is hash of row. There is optional parameter fields. If fields parameter is given, only those column will be returned in the row hash. Values in fields need to be comma separated.

Parameters
fields (optional) Comma separated fields which should be returned from the row
Example Request
curl https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois
curl https://sheetsu.com/apis/v1.0/020b2c0f/score/43?fields=name,score
require 'open-uri'
require 'json'
require 'pp'

uri = 'https://sheetsu.com/apis/v1.0/020b2c0f'
path = '/score/43'
params = '?fields=id,name' # optional
url = uri + path + params

buffer = open(url).read
result = JSON.parse(buffer)

pp result

The easiest way to do it is to use any of the other language, like JS. Below examples shows how to populate HTML website using Java Script and jQuery.

<html>
<head>
<script src="https://code.jquery.com/jquery-3.0.0-alpha1.js"></script>
</head>

<body>
<p>
Champs with score = 43: <span id="score-43"></span>.
</p>

<script>
// I'm GETting all records where column score = 43
var sheetsuUrl = "https://sheetsu.com/apis/v1.0/020b2c0f";
var col = "/score";
var value = "/43";

$.ajax({
// The URL looks like https://sheetsu.com/apis/v1.0/020b2c0f/score/43
// It could be really any column.
// https://sheetsu.com/apis/v1.0/020b2c0f/name/Peter would be also ok.
url: sheetsuUrl + col + value,
dataType: 'json',
type: 'GET',

// place for handling successful response
success: function(data) {
addCharacters(data);
},

// handling error response
error: function(data) {
console.log(data);
}
});

addCharacters = function(characters) {
var champs = $('#score-43');
var html = "";
for(var i=0; i<characters.length; i+=1) {
html += characters[i].name;

if (i != characters.length-1) {
html += ", ";
}
}
champs.text(html);
}
</script>
</body>
</html>
Example Result
# GET https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois
[
{ id: '2', name: 'Lois', score: '89' }
]

# GET https://sheetsu.com/apis/v1.0/020b2c0f/score/43?fields=id,name
[
{ id: '1', name: 'Peter' },
{ id: '4', name: 'Chris' },
]

POST /
POST https://sheetsu.com/apis/v1.0/020b2c0f

Creates row (or rows) from a given hash(es). JSON keys should be column names (first row of spreadsheet). JSON values should be rows values. Records are added to the end of the spreadsheet. You can add one row or bunch of rows in single request. To add rows in bulk, send them in array as a value of rows key (see example). On success, API returns created row(s) with status 201.

Parameters
any columns names with values (optional) Values you want add to the spreadsheet
rows (optional) array of hashes for bulk add
Example Request
curl -X POST -H "Content-Type: application/json" https://sheetsu.com/apis/v1.0/020b2c0f -d '{ "id": "6", "name": "Glenn", "score": "69" }'

curl -X POST -H "Content-Type: application/json" https://sheetsu.com/apis/v1.0/020b2c0f -d '{ "rows": [{ "id": "6", "name": "Glenn", "score": "69" }, { "id": "7", "name": "Joe", "score": "98" }] }'
require 'net/http'
require 'json'
require 'pp'

uri = URI.parse('https://sheetsu.com/apis/v1.0/020b2c0f')

Net::HTTP.new(uri.host, uri.port).start do |client|
request = Net::HTTP::Post.new(uri.path, { 'Content-Type' => 'application/json' })

request.body = {
'id' => '99',
'name' => 'Glenn',
'score' => '69'
}.to_json

# bulk add
request.body = {
'rows' => [
{ 'id': '6', 'name': 'Glenn', 'score': '69' },
{ 'id': '7', 'name': 'Joe', 'score': '98' }
]
}.to_json

response = client.request(request)
pp response
end

Regular POST form.

<form enctype="application/json" action="https://sheetsu.com/apis/v1.0/020b2c0f" method="post">
<input name="Id" value="99">
<input name="Name" value="Glenn">
<input name="Score" value="69">
<button type="submit">Send</button>
</form>


POST using jQuery, with our custom redirect or thank-you page after submiting form.

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
</head>

<body>
<form id="form">
<input type="text" name="name">
<input type="text" name="score">
<button type="submit">submit</button>
</form>

<script>
// jQuery snippet for changing HTML form into JSON
(function ($) {
$.fn.serializeFormJSON = function () {
var o = {};
var a = this.serializeArray();
$.each(a, function () {
if (o[this.name]) {
if (!o[this.name].push) { o[this.name] = [o[this.name]]; }
o[this.name].push(this.value || '');
} else { o[this.name] = this.value || ''; }
});
return o;
};
})(jQuery);

$('#form').submit(function(e) {
// prevent default submiting form
e.preventDefault();

// serialize data to JSON
var data = $('#form').serializeFormJSON();

$.ajax({
url: 'https://sheetsu.com/apis/v1.0/020b2c0f',
data: data,
dataType: 'json',
type: 'POST',

// place for handling successful response
// showing (redirecting to) something like /thanks.html
// page could be a good idea
success: function(data) {
window.location.href = 'thank_you.html';
},

// handling error response
error: function(data) {
console.log(data);
}
});

return false;
});
</script>
</body>
</html>
Example Result
# POST https://sheetsu.com/apis/v1.0/020b2c0f { id: '6', name: 'Glenn', score: '69' }
{ id: '6', name: 'Glenn', score: '69' }

# POST https://sheetsu.com/apis/v1.0/020b2c0f { rows: [{ id: '6', name: 'Glenn', score: '69' }, { id: '7', name: 'Joe', score: '98' }] }
{
rows: [
{ id: '6', name: 'Glenn', score: '69' },
{ id: '7', name: 'Joe', score: '98' }
]
}

PUT /:column/:value
PUT https://sheetsu.com/apis/v1.0/020b2c0f/:column/:value

Updates whole row (or rows) with a given hash. Keys of JSON request should be column's names. Values should be row's values. All rows where column :column equals :value are updated.

Difference between PUT and PATCH:

  • PUT updates the whole row. If you don't provide all fields, some of the fields will be empty after PUT. PATCH updates only fields provided in JSON
  • PUT returs whole upated row, PATCH returns only updated fields
Example Request
curl -X PUT -H "Content-Type: application/json" https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois -d '{ "id": "2", "name": "Loo1z", "score": "99999" }'

curl -X PUT -H "Content-Type: application/json" https://sheetsu.com/apis/v1.0/020b2c0f/id/1 -d '{ "name": "Mr. Griffin", "score": "nope" }'
require 'net/http'
require 'json'
require 'pp'

uri = URI.parse('https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois')

Net::HTTP.new(uri.host, uri.port).start do |client|
request = Net::HTTP::Put.new(uri.path, { 'Content-Type' => 'application/json' })
request.body = {
"id": "2",
"name": "Loo1z",
"score": "99999"
}.to_json

response = client.request(request)
pp response
end

PUT using jQuery

var data = {
id: "2",
name: "Loo1z",
score: "99999"
};

$.ajax({
url: 'https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois',
data: data,
dataType: 'json',
type: 'PUT',

// place for handling successful response
// showing (redirecting to) something like /thanks.html
// page could be a good idea
success: function(data) {
console.log(data);
},

// handling error response
error: function(data) {
console.log(data);
}
});
Example Result
# PUT https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois { id: "2", name: "Loo1z", score: "99999" }
[
{ id: '2', name: 'Loo1z', score: '99999' }
]

# PUT https://sheetsu.com/apis/v1.0/020b2c0f/id/1 { name: "Mr. Griffin", score: "nope" }
[
{ id: '', name: 'Mr. Griffin', score: 'nope' }
]

PATCH /:column/:value
PATCH https://sheetsu.com/apis/v1.0/020b2c0f/:column/:value

Updates row (or rows) with a given hash. Keys of JSON request should be column's names. Values should be row's values. All rows where column :column equals :value are updated.

Difference between PUT and PATCH:

  • PUT updates the whole row. If you don't provide all fields, some of the fields will be empty after PUT. PATCH updates only fields provided in JSON
  • PUT returs whole upated row, PATCH returns only updated fields
Example Request
curl -X PATCH -H "Content-Type: application/json" https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois -d '{ "name": "Loo1z" }'

curl -X PATCH -H "Content-Type: application/json" https://sheetsu.com/apis/v1.0/020b2c0f/score/43 -d '{ "name": "Griffins", "score": "not so high" }'
require 'net/http'
require 'json'
require 'pp'

uri = URI.parse('https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois')

Net::HTTP.new(uri.host, uri.port).start do |client|
request = Net::HTTP::Patch.new(uri.path, { 'Content-Type' => 'application/json' })
request.body = {
"name": "Loo1z",
}.to_json

response = client.request(request)
pp response
end

PATCH using jQuery

var data = {
name: "Loo1z",
};

$.ajax({
url: 'https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois',
data: data,
dataType: 'json',
type: 'PATCH',

// place for handling successful response
// showing (redirecting to) something like /thanks.html
// page could be a good idea
success: function(data) {
console.log(data);
},

// handling error response
error: function(data) {
console.log(data);
}
});
Example Result
# PATCH https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois { name: "Loo1z" }
[
{ name: 'Loo1z' }
]

# PATCH https://sheetsu.com/apis/v1.0/020b2c0f/score/43 { name: "Griffins", "score": "not so high" }
[
{ name: 'Griffins', score: 'not so high' }
{ name: 'Griffins', score: 'not so high' }
]

DELETE /:column/:value
DELETE https://sheetsu.com/apis/v1.0/020b2c0f/:column/:value

Deletes row(s) where column :column value equals :value. No body is returned, only status 204 when success.

Example Request
curl -X DELETE https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois
require 'net/http'
require 'json'
require 'pp'

uri = URI.parse('https://sheetsu.com/apis/v1.0/020b2c0f')

Net::HTTP.new(uri.host, uri.port).start do |client|
request = Net::HTTP::Delete.new(uri.path)
response = client.request(request)
pp response
end
$.ajax({
url: 'https://sheetsu.com/apis/v1.0/020b2c0f',
type: 'DELETE',
// place for handling successful response
// showing (redirecting to) something like /thanks.html
// page could be a good idea
success: function(data) {
console.log(data);
},

// handling error response
error: function(data) {
console.log(data);
}
});
Example Result
# DELETE https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois
# No body is returned, only HTTP status

# POST https://sheetsu.com/apis/v1.0/020b2c0f/score/43
# No body is returned, only HTTP status

Authentication

There's a possibility of securing your API. Right now authentication is done using HTTP Basic auth. You can turn on authentication in your API menu. To successfully query the secured API you have to provide api_key and api_secret in the HEAD of your request. If the wrong API key/secret is provided with the request 401 Unauthorized status is returned.

Remember about using secure (https) connection, so your api_key and api_secret will not be exposed to the outside world.

Example Request

# Example api_key, and api_secret provided.
# curl PATH -u 'api_key:api_secret'
curl https://sheetsu.com/apis/v1.0/020b2c0f -u 'SLpTxQDcpuPwoTjyeeai:1mfMzpN7kA5pp4rnFrDvaK97EKnxtA8QcxKTngK9'
curl -X POST -H "Content-Type: application/json" https://sheetsu.com/apis/v1.0/020b2c0f -d '{ "id": "6", "name": "Glenn", "score": "69" }' -u 'SLpTxQDcpuPwoTjyeeai:1mfMzpN7kA5pp4rnFrDvaK97EKnxtA8QcxKTngK9'
require 'net/http'
require 'json'
require 'pp'

uri = URI.parse('https://sheetsu.com/apis/v1.0/020b2c0f')

options = {
use_ssl: uri.scheme == 'https',
verify_mode: OpenSSL::SSL::VERIFY_PEER
}

Net::HTTP.new(uri.host, uri.port, options).start do |client|
request = Net::HTTP::Post.new(uri.path, { 'Content-Type' => 'application/json' })

request.body = {
'id' => '99',
'name' => 'Glenn',
'score' => '69'
}.to_json

# here put you API KEY and API SECRET
request.basic_auth 'API_KEY', 'API_SECRET'

response = client.request(request)
pp response
end
var data = {
id: "2",
name: "Loo1z",
score: "99999"
};

$.ajax({
url: 'https://sheetsu.com/apis/v1.0/020b2c0f/name/Lois',
// Your API_KEY and API_SECRET goes here
headers: {
"Authorization": "Basic " + btoa(API_KEY + ":" + API_SECRET)
},
data: data,
dataType: 'json',
type: 'PUT',

// place for handling successful response
// showing (redirecting to) something like /thanks.html
// page could be a good idea
success: function(data) {
console.log(data);
},

// handling error response
error: function(data) {
console.log(data);
}
});

GET /_keys
GET https://sheetsu.com/apis/v1.0/020b2c0f/_keys

Returns an array with all keys used in the spreadsheet.

Example Request
curl https://sheetsu.com/apis/v1.0/020b2c0f/_keys
require 'open-uri'
require 'json'
require 'pp'

uri = 'https://sheetsu.com/apis/v1.0/020b2c0f/_keys'

buffer = open(uri).read
result = JSON.parse(buffer)

pp result

The easiest way to do it is to use any of the other language, like JS. Below examples shows how to populate HTML website using Java Script and jQuery.

 <html>
<head>
<script src="https://code.jquery.com/jquery-3.0.0-alpha1.js"></script>
</head>

<body>
<ul id="characters-list"></ul>

<script>
var sheetsuUrl = "https://sheetsu.com/apis/v1.0/020b2c0f/_keys";

$.ajax({
url: sheetsuUrl,
dataType: 'json',
type: 'GET',

// place for handling successful response
success: function(data) {
console.log(data);
},

// handling error response
error: function(data) {
console.log(data);
}
});
</script>
</body>
</html>
Example Result
# GET https://sheetsu.com/apis/v1.0/020b2c0f/_keys
[
'id', 'name', 'score'
]