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 https address. 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
- 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 API for JavaScript on GitHub
PHP: Google Spreadsheet API for PHP 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>
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
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>
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>
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>
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>
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>
Example response:
[
{
"id": "61",
"name": "Steve",
"age": "22",
"comment": "special"
}
]
GET https://sheetdb.io/api/v1/58f61be4dda40/search?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, check out # GET - Search OR
You can search using wildcards. Asteriks (*
) 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 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 formatcasesensitive
- by default search is not case sensitive, to make it case sensitive set this parameter totrue
cast_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,age
GET - Search OR in document
curl https://sheetdb.io/api/v1/58f61be4dda40/search_or?name=Steve&age=19&casesensitive=true
<?php
$options = array(
'http' => array(
'method' => 'GET'
)
);
$result = json_decode(
file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/search_or?name=Steve&age=19&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_or?name=Steve&age=19&casesensitive=true')
.then( response => {
console.log(response.data);
});
</script>
Example response:
[
{
"id": "61",
"name": "Steve",
"age": "22",
"comment": "special"
},
{
"id": "422",
"name": "James",
"age": "19"
}
]
GET https://sheetdb.io/api/v1/58f61be4dda40/search_or?name=Steve&age=19&casesensitive=true
Similar to # GET - Search but if any parameter succeed to match, it will be listed in response.
You can search using wildcards. Asteriks *
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.
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_or?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 formatcasesensitive
- by default search is not case sensitive, to make it case sensitive set this parameter totrue
cast_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,age
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>
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.
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>
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>
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>
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 - 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>
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.
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.
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>
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>
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>
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"
- 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)
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.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}} 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:
- 2,000 requests per user (for 100 seconds)
- 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. In this case remember to set the cache for longer than you plan to poll this request.
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 30 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 have cache enabled 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.
We are here for you!
If you have any questions please feel free to ask via email or inapp chat.
- Add handlebars