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>

[
  {
    "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 returned
offset - row from which it should start (how many rows to skip)
sort_by - the column you want to sort by
sort_order - sort in asc or desc order
sort_method - if you want to sort by date, set this parameter to date, it will automatically detect the date format
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 - 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>

["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>

{
  "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>

{
  "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>

{
  "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>

[
  {
    "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 returned
offset - row from which it should start (how many rows to skip)
sort_by - the column you want to sort by
sort_order - sort in asc or desc order
sort_method - if you want to sort by date, set this parameter to date, it will automatically detect the date format
casesensitive - by default search is not case sensitive, to make it case sensitive set this parameter to true
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>

[
  {
    "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.

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 returned
offset - row from which it should start (how many rows to skip)
sort_by - the column you want to sort by
sort_order - sort in asc or desc order
sort_method - if you want to sort by date, set this parameter to date, it will automatically detect the date format
casesensitive - by default search is not case sensitive, to make it case sensitive set this parameter to true
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>

{
  "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 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>

{
  "updated": 1
}

PATCH/PUT https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}

Replace {column} and {value} with your values (without braces). Ex. https://sheetdb.io/api/v1/58f61be4dda40/id/61

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>

{
  "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>

{
    "deleted": 1
}

DELETE https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}

Replace {column} and {value} with your values (without braces). Ex. https://sheetdb.io/api/v1/58f61be4dda40/id/61

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>

{
    "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>

[
  {
    "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>

{
  "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 sheet
first_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>

{
  "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 and data-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 using data-sheetdb-sort-order - (desc or asc)

If you are using Vue.js, you have to use v-pre attribute on elements that contain our snippets, like following: <div v-pre>{{name}}</div>

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 use data-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.