Introduction
Each spreadsheet document you want to access must be configured in the SheetDB panel as an API. After creating the API, you’ll receive a unique endpoint url. You can send RESTful requests to read, create, update or delete rows. The rest of the documentation explains how each request works and what you can expect in a response.
You can choose your preferred programming language in the upper right corner.
- Shell - the command line
- PHP - examples in vanilla PHP, you can also use our composer library
- JavaScript - we recommend to use Axios library or our npm library
- Node.js - we recommend to use Axios library or our npm library
- Pure HTML - to learn how to use SheetDB with only HTML go to Handlebars
Installation
You can use one of our Libraries:
WordPress plugin: SheetDB on WordPress
JavaScript: Google Spreadsheet REST API for JavaScript on GitHub
PHP: Google Spreadsheet REST API for PHP on GitHub
Node.js Google Spreadsheet REST API for Node.js on GitHub
Example spreadsheet
You can play with our test API here: https://sheetdb.io/api/v1/58f61be4dda40
You can also visit Google Sheets document here: google docs test document.
First row of your spreadsheet should contain the column names. Each next row will be treated as a record (according to naming from first row).
Our Example spreadsheet looks like this:
id | name | age | comment |
---|---|---|---|
1 | Tom | 41 | |
2 | Alex | 24 | |
3 | John | 51 | |
61 | Steve | 22 | special |
422 | James | 19 |
HTTP Status Codes
Each response is in JSON format. Of course, there is a status code in each response. Here is the list of available response codes:
Status Code | Meaning |
---|---|
200 OK | The request has succeeded for GET, PUT, PATCH and DELETE requests. |
201 Created | The request has succeeded for POST requests. |
400 Bad Request | API could not understand the request. |
401 Unauthorized | An error with authorization using a Google account or incorrect credentials for API if Basic Auth is enabled. |
402 Payment Required | Payment is required to process the request. |
403 Forbidden | Action is forbidden. |
404 Not Found | The server did not find anything matching the request. |
429 Too Many Requests | Exhausted limit requests. Check API limits |
500 Internal Server Error | We had a problem with our server. Try again later. |
1015 Rate limit | You are making too much requests, limit is 60 requests per 10 seconds. Try again in a minute. If you need more please contact us. |
SheetDB API
GET - All data
# Get all data
curl https://sheetdb.io/api/v1/58f61be4dda40
# Get 10 results starting from 20
curl https://sheetdb.io/api/v1/58f61be4dda40?limit=10&offset=20
# Get all data sorted by name in ascending order
curl https://sheetdb.io/api/v1/58f61be4dda40?sort_by=name&sort_order=asc
<?php
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
// Get all data
axios.get('https://sheetdb.io/api/v1/58f61be4dda40')
.then( response => {
console.log(response.data);
});
// Get 10 results starting from 20
axios.get('https://sheetdb.io/api/v1/58f61be4dda40?limit=10&offset=20')
.then( response => {
console.log(response.data);
});
// Get all data sorted by name in ascending order
axios.get('https://sheetdb.io/api/v1/58f61be4dda40?sort_by=name&sort_order=asc')
.then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
// Read whole spreadsheet
client.read().then(function(data) {
console.log(data);
}, function(error){
console.log(error);
});
// Read first two rows from sheet "Sheet2"
client.read({ limit: 2, sheet: "Sheet2" }).then(function(data) {
console.log(data);
}, function(err){
console.log(err);
});
Example response:
[
{
"id": "1",
"name": "Tom",
"age": "41"
},
{
"id": "2",
"name": "Alex",
"age": "24"
},
{
"id": "3",
"name": "John",
"age": "51"
}
]
GET https://sheetdb.io/api/v1/58f61be4dda40
Returns an array with all data from the spreadsheet.
You can use optional parameters:
limit
- the number of rows that should be returnedoffset
- row from which it should start (how many rows to skip)sort_by
- the column you want to sort bysort_order
- sort inasc
ordesc
order, you can also userandom
to get your data in a random ordersort_method
- if you want to sort by date, set this parameter todate
, it will automatically detect the date formatcast_numbers
- if you want to cast the value to a number, add column names separated by commas. Example: https://sheetdb.io/api/v1/58f61be4dda40?cast_numbers=id,agesingle_object
- if you want to get ony one item as an object (not in the array), set this parameter totrue
GET - Keys
# Get keys
curl https://sheetdb.io/api/v1/58f61be4dda40/keys
<?php
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/keys', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.get('https://sheetdb.io/api/v1/58f61be4dda40/keys')
.then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
client.endpoint('keys').then(function(data) {
console.log(data);
}, function(error){
console.log(error);
});
Example response:
["id", "name", "age", "comment"]
GET https://sheetdb.io/api/v1/58f61be4dda40/keys
Returns an array with all keys from the spreadsheet = values of the first row of the document.
GET - Document name
# Get the document name
curl https://sheetdb.io/api/v1/58f61be4dda40/name
<?php
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/name', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.get('https://sheetdb.io/api/v1/58f61be4dda40/name')
.then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
client.endpoint('name').then(function(data) {
console.log(data);
}, function(error){
console.log(error);
});
Example response:
{
"name": "SheetDB test document"
}
GET https://sheetdb.io/api/v1/58f61be4dda40/name
Returns the name of the document.
GET - List of available sheets
curl https://sheetdb.io/api/v1/58f61be4dda40/sheets
<?php
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/sheets', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.get('https://sheetdb.io/api/v1/58f61be4dda40/sheets')
.then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
client.endpoint('sheets').then(function(data) {
console.log(data);
}, function(error){
console.log(error);
});
Example response:
{
"sheets": [
"Sheet1",
"Sheet2"
]
}
GET https://sheetdb.io/api/v1/58f61be4dda40/sheets
Returns a list of all available sheets (tabs).
GET - Count
curl https://sheetdb.io/api/v1/58f61be4dda40/count
<?php
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/count', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.get('https://sheetdb.io/api/v1/58f61be4dda40/count')
.then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
client.endpoint('count').then(function(data) {
console.log(data);
}, function(error){
console.log(error);
});
Example response:
{
"rows": 5
}
GET https://sheetdb.io/api/v1/58f61be4dda40/count
Returns the number of rows in the document (without first row).
GET - Search in document
curl https://sheetdb.io/api/v1/58f61be4dda40/search?name=Steve&age=22&casesensitive=true
<?php
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/search?name=Steve&age=22&casesensitive=true', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.get('https://sheetdb.io/api/v1/58f61be4dda40/search?name=Steve&age=22&casesensitive=true')
.then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
// Get all rows where column 'id' is 'foo' and column 'value' is 'bar'
client.read({ search: { id: "1", name: "Tom" } }).then(function(data) {
console.log(data);
}, function(err){
console.log(err);
});
// Get first row where column 'player' is 'Smith',
// column 'score' is '41' from sheet named "Sheet2"
client.read({
limit: 1,
search: { 'player': 'Smith', 'score': 41 },
sheet: 'Sheet2'
}).then(function(data) {
console.log(data);
}, function(err){
console.log(err);
});
Example response:
[
{
"id": "61",
"name": "Steve",
"age": "22",
"comment": "special"
}
]
GET https://sheetdb.io/api/v1/58f61be4dda40/search?name=Steve&age=22&casesensitive=true
GET https://sheetdb.io/api/v1/58f61be4dda40/search_or?name=Steve&age=22&casesensitive=true
Returns an array of all rows matching parameters. If any query fails, the result will NOT be listed. If you want to check if any parameter is true, use the search_or endpoint (second URL above).
You can search using wildcards. Asterisk (*
) can represent any string.
Wildcard work only when READ and SEARCH permissions are both enabled, if only SEARCH peremission is enabled, wildcard will not work for security reasons.
If you want to exclude rows from the search results, use an exclamation mark before the value. For example, if you want all rows without name=Tom use this url: https://sheetdb.io/api/v1/58f61be4dda40/search?name=!Tom
You can use relational operators to determine if a value is greater than or less than a given number. Just start with <
, >
, <=
, >=
operator. Example urls:
- Greater than 3: https://sheetdb.io/api/v1/58f61be4dda40/search?id=>3
- Less than 3: https://sheetdb.io/api/v1/58f61be4dda40/search?id=<3
- Greater than or equal to 3: https://sheetdb.io/api/v1/58f61be4dda40/search?id=>=3
- Less than or equal to 3: https://sheetdb.io/api/v1/58f61be4dda40/search?id=<=3
You can use multiple queries for the same column, but you must use array notation ([]
at the end of the variable name), for example: https://sheetdb.io/api/v1/58f61be4dda40/search?name[]=!Tom&name[]=!Steve
If you want to search for a string with a space, just repace space with %20
If you want to include & symbol in your query, replace it with %26
You can use optional parameters:
limit
- the number of rows that should be returnedoffset
- row from which it should start (how many rows to skip)sort_by
- the column you want to sort bysort_order
- sort inasc
ordesc
ordersort_method
- if you want to sort by date, set this parameter todate
, it will automatically detect the date formatcast_numbers
- if you want to cast the value to a number, add column names separated by commas. Example: https://sheetdb.io/api/v1/58f61be4dda40?cast_numbers=id,agesingle_object
- if you want to get ony one item as an object (not in the array), set this parameter totrue
casesensitive
- by default search is not case sensitive, to make it case sensitive set this parameter totrue
For more examples, check out this blog post: Search feature in SheetDB API or this youtube video: Using SheetDB search API to filter results from Google Sheets.
POST - Create row
curl -X POST -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40 -d '{"data":[{ "name": "Scott", "age": "25" }]}'
<?php
$data = http_build_query(
[
'data' =>
[
['name' => 'Scott', 'age' => 25]
]
]
);
$options = array(
'http' => array(
'method' => 'POST',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $data
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.post('https://sheetdb.io/api/v1/58f61be4dda40',{
"data": {"name": "Scott", "age": 25}
}).then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
// Adds single row
client.create({ name: "William", age: 25 }).then(function(data) {
console.log(data);
}, function(err){
console.log(err);
});
/// Adds bunch of rows
rows = [
{ name: "William", age: 25 },
{ name: "Jayden", age: 25 }
]
client.create(rows).then(function(data) {
console.log(data);
}, function(err){
console.log(err);
});
Example response:
{
"created": 1
}
POST https://sheetdb.io/api/v1/58f61be4dda40
Create rows in document. Your request should contain data
parameter - it should be an array of rows. Keys inside the object should be a column names (see # GET - keys) and values will be values inside a spreadsheet. Rows will be added at the end of spreadsheet. If you want to add a single row, simply send an array with one item {"data": [{"id":5,"name":"Frank"}]}
.
You can also add multiple rows at once using an array of objects. Example: {"data": [{"id":5,"name":"Frank"},{"id":6,"name":"Marc"}]}
You can use value INCREMENT
(upper case). SheetDB will look for the biggest number in the given column and increase by 1.
If for some reason you cannot put your data into the data
parameter, SheetDB will try to use the entire request data to match the spreadsheet format.
API will return the number of created rows with status code 201 Created
.
POST - Import JSON
curl -X POST -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/import/json -d '{"json":[{ "name": "Scott", "age": "25" }]}'
<?php
$data = http_build_query(
[
'json' =>
[
['name' => 'Scott', 'age' => 25]
]
]
);
$options = array(
'http' => array(
'method' => 'POST',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $data
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/import/json', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.post('https://sheetdb.io/api/v1/58f61be4dda40/import/json',{
"json": '{"name": "Scott", "age": 25}'
}).then( response => {
console.log(response.data);
});
</script>
Example response:
{
"created": 1
}
POST https://sheetdb.io/api/v1/58f61be4dda40/import/json
If you have an empty spreadsheet, above all with a first row unfilled, you can use this endpoint to import entire JSON file. Make sure that the spreadsheet is empty or the endpoint will append new content to the first row. The first row (column names) will be generated from the first object in the array.
Required parameters:
json
- should contain valid json array, e.g.[{ "name": "Scott", "age": "25" }]
Optional parameters:
sheet
- to select other then the first sheet
API will return the number of created rows with status code 201 Created
.
PATCH/PUT - Update
curl -X PATCH -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/id/61 -d '{"data":[{ "name": "Scott", "age": "25" }]}'
<?php
$data = http_build_query(
[
'data' =>
[
['name' => 'Scott', 'age' => 25]
]
]
);
$options = array(
'http' => array(
'method' => 'PATCH',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $data
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/id/61', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.patch('https://sheetdb.io/api/v1/58f61be4dda40/id/61',{
"data": {"name": "Scott", "age": 25}
}).then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
// Update all columns where 'name' is 'Smith' to have 'score' = 99 and 'comment' = 'Griffin'
client.update(
'name', // column name
'Tom', // value to search for
{ 'comment': 'Updated' } // object with updates
).then(function (data) {
console.log(data);
}, function (err) {
console.log(err);
});
// Update all columns where 'name' is 'Smith' to have 'score' = 99 and 'comment' = 'Griffin'
// In sheet named 'Sheet2'
client.update(
'player', // column name
'Smith', // value to search for
{ 'score': 99, 'comment': 'Griffin' }, // object with updates
'Sheet2'
).then(function (data) {
console.log(data);
}, function (err) {
console.log(err);
});
Example response:
{
"updated": 1
}
PATCH/PUT https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}
Update row(s) for given column and value. Similar to # GET - Search you must specify a key (column name) and value to find. Any rows that match the condition will be updated.
Requests will update only values passed in data object.
It returns count of updated rows.
PATCH/PUT - Batch update
curl -X PATCH -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/batch_update -d '{"data":[{"query":"id=1", "name":"Mathew", "age":20},{"query":"id=2", "age":25}]}'
<?php
$data = http_build_query(
[
'data' =>
[
['query' => 'id=1', 'name' => 'Mathew', 'age' => 20],
['query' => 'id=2', 'age' => 25],
]
]);
$options = array(
'http' => array(
'method' => 'PATCH',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $data
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/batch_update', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.patch('https://sheetdb.io/api/v1/58f61be4dda40/batch_update', {
data: [
{
"query":"id=1",
"name":"qwes",
"age":20
},
{
"query":"id=2",
"age":25,
}
]
}).then( response => {
console.log(response.data);
});
</script>
// our Node.js library does not support this method
Example response:
{
"updated": 2
}
PATCH/PUT https://sheetdb.io/api/v1/58f61be4dda40/batch_update
Update for various queries. You have to add an data
param to your request. Each object in it should have a query
key with the actual query (for example, "id=5"), and the remaining keys will be updated, as in a regular PATCH / PUT request. Here is an example of the data
parameter in the body of the request:
[
{
"query":"id=1",
"name":"Mathew",
"age":20
},
{
"query":"id=2",
"age":25,
}
]
Requests will update only values passed in data object.
It returns count of updated rows.
DELETE
curl -X DELETE -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/id/61'
<?php
$options = array(
'http' => array(
'method' => 'DELETE'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/id/61', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.delete('https://sheetdb.io/api/v1/58f61be4dda40/id/61')
.then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
// Delete all rows where 'name' equals 'Smith'
client.delete(
'id', // column name
'1' // value to search for
).then(function(data) {
console.log(data);
}, function(err){
console.log(err);
});
Example response:
{
"deleted": 1
}
DELETE https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}
Delete row(s) for given column and value. Similar to # GET - Search you must specify a key (column name) and value to find. Any rows that match the condition will be deleted.
It returns count of deleted rows.
You can use optional parameters:
limit
- the number of rows to delete. When dealing with a large spreadsheet and a large number of rows to delete, is's recommended to use limit.
DELETE - Duplicates
curl -X DELETE -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/duplicates
<?php
$options = array(
'http' => array(
'method' => 'DELETE'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/duplicates', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.delete('https://sheetdb.io/api/v1/58f61be4dda40/duplicates')
.then( response => {
console.log(response.data);
});
</script>
// our Node.js library does not support this method
Example response:
{
"duplicates": 3
}
DELETE https://sheetdb.io/api/v1/58f61be4dda40/duplicates
Deletes all duplicate rows, all columns must contain exactly the same data to be deleted.
By default first sheet (tab) is selected but you can target any sheet you want using param sheet. Example: ?sheet=Sheet2
It returns count of duplicated rows.
DELETE - All content
curl -X DELETE -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/all
<?php
$options = array(
'http' => array(
'method' => 'DELETE'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/all', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.delete('https://sheetdb.io/api/v1/58f61be4dda40/all')
.then( response => {
console.log(response.data);
});
</script>
// our Node.js library does not support this method
Example response:
{
"deleted": 5
}
DELETE https://sheetdb.io/api/v1/58f61be4dda40/all
Delete all rows without the first one (column names) in spreadsheet. By default first sheet (tab) is selected but you can target any sheet you want using param sheet. Example: ?sheet=Sheet2
It returns count of deleted rows.
Single cells
GET - Cells values
# Get A1 cell
curl https://sheetdb.io/api/v1/58f61be4dda40/cells/A1
<?php
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/cells/A1', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.get('https://sheetdb.io/api/v1/58f61be4dda40/cells/A1')
.then( response => {
console.log(response.data);
});
</script>
// we do not support single cells in our node package :(
Example response:
{
"A1":"id"
}
GET https://sheetdb.io/api/v1/58f61be4dda40/cells/A1
If you want to retrieve the contents of a single cell using coordinates like A1, B10 etc. you can use this endpoint. Returns an array with coordinates as key and cell contents as value.
If you want to get multiple cells just separate them with a comma. Example:
GET https://sheetdb.io/api/v1/58f61be4dda40/cells/A1,A2,B5
Multiple Sheets (tabs)
To select other then the first sheet you need to pass a sheet
parameter wtih sheet name. You can add this parameter to any of our RESTful requests (GET
, POST
, PATCH
, PUT
, DELETE
).
When sheet is not found you'll get 404 error.
Here are sample URLs for different methods (for a sheet named sheet2
):
GET
https://sheetdb.io/api/v1/58f61be4dda40?sheet=sheet2POST
https://sheetdb.io/api/v1/58f61be4dda40?sheet=sheet2PATCH
https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}?sheet=sheet2DELETE
https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}?sheet=sheet2
GET - All data from a tab
# Get all data from "Sheet2"
curl https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2
# You can still use limit, offset and sort parameters
curl https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2&limit=10&offset=20
<?php
// define options
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
// Get all data
axios.get('https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2')
.then( response => {
console.log(response.data);
});
// You can still use limit, offset and sort parameters
axios.get('https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2&limit=10&offset=20')
.then( response => {
console.log(response.data);
});
</script>
const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });
// Sheet "Sheet2"
client.read({ sheet: "Sheet2" }).then(function(data) {
console.log(data);
}, function(err){
console.log(err);
});
Example response:
[
{
"player": "Smith",
"score": "41"
},
{
"player": "Martha",
"score": "43"
},
{
"player": "Craig",
"score": "12"
},
{
"player": "Michael",
"score": "61"
}
]
GET https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2
This is an example how to get data from sheet Sheet2
POST - Add a sheet to the spreadsheet (tab)
curl -X POST -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/sheet -d '{"name": "New Sheet", "first_row":["id","name"]}'
<?php
$data = http_build_query(
[
'name' => "New Sheet",
'first_row' => ["id","name"]
]
);
$options = array(
'http' => array(
'method' => 'POST',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $data
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/sheet', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.post('https://sheetdb.io/api/v1/58f61be4dda40/sheet',{"name": "New Sheet", "first_row":["id","name"]})
.then( response => {
console.log(response.data);
});
</script>
// our Node.js library does not support this method
Example response:
{
"created":1
}
POST https://sheetdb.io/api/v1/58f61be4dda40/sheet
Creates a new sheet (tab) in the spreadsheet. 2 params are required:
name
- the name of the new sheetfirst_row
- first row of spreadsheet that contain an array with column names. For example:["id","name","score"]
DELETE - Delete a sheet (tab)
curl -X DELETE -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/sheet -d '{"name":"New Sheet"}'
<?php
$data = http_build_query(
[
'name' => "New Sheet"
]
);
$options = array(
'http' => array(
'method' => 'DELETE',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $data
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/sheet', false, stream_context_create($options))
);
<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
axios.delete('https://sheetdb.io/api/v1/58f61be4dda40/sheet',{
"data":{
"name":"New Sheet"
}
})
.then( response => {
console.log(response.data);
});
</script>
// our Node.js library does not support this method
Example response:
{
"deleted": 1
}
DELETE https://sheetdb.io/api/v1/58f61be4dda40/sheet
Deletes a sheet (tab) and the content. Requires one param:
name
- the name of the sheet you want to delete
Our handlebars snippet
Handlebars Installation
Copy the following code and add it before the closing </body>
tag.
<script src="https://sheetdb.io/handlebars.js"></script>
That's it. You are ready to use our handlebars snippet.
Display data
Now you can display data from your spreadsheet.
- Add
data-sheetdb-url="SHEETDB_API_URL"
to the parent element. For example- Add handlebars
{{
and}}
and fill them with the column name you want to display.Optional parameters
<table> <thead> <tr> <td>ID</td> <td>Name</td> <td>Age</td> <td>Comment</td> </tr> </thead> <tbody data-sheetdb-url="https://sheetdb.io/api/v1/58f61be4dda40" data-sheetdb-sort-by="age" data-sheetdb-sort-order="desc"> <tr> <td>{{id}}</td> <td>{{name}}</td> <td>{{age}}</td> <td>{{comment}}</td> </tr> </tbody> </table> <script src="https://sheetdb.io/handlebars.js"></script>
- By default, you will receive data from the first sheet (tab). If you want to work with another sheet, use
data-sheetdb-sheet
and enter the name of the sheet (case sensitive). - You can limit and offset your response using
data-sheetdb-limit
anddata-sheetdb-offset
attributes - You can search for specific data in your sheet using
data-sheetdb-search
attribute. If you want to use more than one condition join them using & symbol. Example:data-sheetdb-search="name=Tom&age=15"
- If you want to use search_or endpoint, please specify
data-sheetdb-search-mode="or"
, if you want to target one column more than once, don't forget square brackets like this:data-sheetdb-search="id[]=1&id[]=2"
- You can sort the response using
data-sheetdb-sort-by
attribute - it should be a name of the column you want to sort by. You can alo specify the order usingdata-sheetdb-sort-order
- (desc or asc) - If the result is not found (e.g. in search or query string) you can use the
data-sheetdb-not-found-message
attribute and as the value give the message which will be displayed to the user.
Here is the result of right hand code:
ID Name Age Comment {{id}} {{name}} {{age}} {{comment}} Query strings
<form> <input type="text" name="id" placeholder="Enter the ID"> <input type="submit"> </form> <table> <thead> <tr> <td>ID</td> <td>Name</td> <td>Age</td> <td>Comment</td> </tr> </thead> <tbody data-sheetdb-url="https://sheetdb.io/api/v1/58f61be4dda40" data-sheetdb-query-string="id"> <tr> <td>{{id}}</td> <td>{{name}}</td> <td>{{age}}</td> <td>{{comment}}</td> </tr> </tbody> </table> <script src="https://sheetdb.io/handlebars.js"></script>
To change content dynamically you can use query strings. If you add
data-sheetdb-query-string
and specify the parameter you want to filter by, you can add query to the address bar. For example if you usedata-sheetdb-query-string="id"
and add ?id=3 to the url you will get filtered data and display only results with id=3.If you want to use more query strings, separate them with commas with no spaces in between. E.g
data-sheetdb-query-string="id,age"
.Here is the result of example code:
After entering your ID, watch what's happening in the address bar. After you submit,
?id=xxx
will be added to the url. This change in the url causes this feature to work. You can also add this parameter to your links to filter data using query strings.ID Name Age Comment {{id}} {{name}} {{age}} {{comment}} Slots
<span data-sheetdb-url="https://sheetdb.io/api/v1/58f61be4dda40" data-sheetdb-save="slot-example"> {{name}} </span> <span data-sheetdb-slot="slot-example"> {{id}} </span>
If you want to re-use your data, you can use the
data-sheetdb-save
attribute in your sheetdb element. To re-use your data usedata-sheetdb-slot
. You can use the same data inside as in the parent. To match them the value of save and slot must be the sameThis way, you only use 1 request instead of 2. Slots have access to the same data as the parent. You can’t change things like limit or search.
This is the result of our example (using only 1 request):
{{name}}
{{id}}
Events
window.addEventListener("sheetdb-downloaded", function() { // this code will be executed after all sheetdb data has been retrieved });
After downloading all the data, the handlebars library will trigger a custom event
sheetdb-downloaded
. You can listen for this event to execute some javascript code after getting the data and rendering the DOM.Update content
If you want to update the contents of SheetDB, you can use the global function
sheetdb_upd()
. It will force all content to be downloaded again from the spreadsheet.Other
Value Render Option
To your GET (get and search) requests, you can add optional
mode
parameter. It controls how values should be rendered in the output, as described in the following:FORMATTED_VALUE
(default) - Values will be calculated & formatted in the reply according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "$1.23".UNFORMATTED_VALUE
- Values will be calculated, but not formatted in the reply. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.FORMULA
- Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "=A1".Value Input Option
To your POST, PUT and PATCH (create and update) requests, you can add optional
mode
parameter. It controls whether input strings are parsed or not, as described in the following:USER_ENTERED
(default) - The input is parsed exactly as if it were entered into the Google Sheets UI, so "Mar 1 2016" becomes a date, and "=1+2" becomes a formula. Formats may also be inferred, so "$100.15" becomes a number with currency formatting.RAW
- The input is not parsed and is simply inserted as a string, so the input "=1+2" places the string "=1+2" in the cell, not a formula. (Non-string values like booleans or numbers are always handled as RAW.)Authentication
You can add HTTP Basic Auth to each API as an additional security for your API's. You can enable it on the API Settings tab.
You will receive a login and password. You have to send it for every request that has basic authentication enabled. If the credentials are incorrect API will respond with error
401 Unauthorized
.API Limits
After you hit your rate limit, your requests will return an error:
429 Too Many Requests
. You can check limits at our pricing page.There are 2 more limits:
- 100 requests per 100 seconds per user
- variable limit of requests per one spreadsheet (for 100 seconds)
Both are limits imposed by Google and cannot be changed. If you need more, best solution is enabling cache. SheetDB limit will be calculated as usual, however Google limits do not apply because in the case of cache results we do not query Google servers.
Make the first request before a larger batch to prepare the cache.
Additionally, there is a limit - 60 requests per 10 seconds from one IP address. If you need more please contact us.
Caching
By default, we cache your API data for 15 seconds. If you are on paid plan you can change this period in API settings up to 7 days. Caching guarantees the fastest response time, especially for a large number of requests.
When cache is enabled, changes to the spreadsheet are not reflected in the API's until the cache expires, or until you make changes to the spreadsheet using SheetDB API. There is also an endpoint to reset cache in API settings.
Example:
If you enable cache for 7 days and one day later you change the content in the Google Spreadsheet website, for the next 6 days, GET endpoint will return the old data! You can use purge endpoint to manually reset the cache.
If you modify a spreadsheet using SheetDB API - for example with the POST request, the cache will be cleared for you and after the change your GET requests will return new content immediately.
Data safety
The confidentiality of your data is very important to us, which is why we do not store any of your data on our servers, other than API data for caching reasons. Also, for people without an API URL, guessing is almost impossible, but if you still need more protection, you can enable Basic Auth.
We use the best tools and services available to protect your data.
What does SheetDB have access to?
SheetDB can only access spreadsheets for which the API has been created. This means that SheetDB do not have access to your other files.
This documentation is on GitHub
If you'd like to correct or add something here, please submit a pull request.
We are here for you!
If you have any questions please feel free to ask via email or inapp chat.
- Add handlebars