SheetDB API Documentation

Introduction

Every spreadsheet document you want to access has to be setup in SheetDB panel as an API. Once API is made you'll get an unique http address. You can send RESTful request to it to read, create, update or delete rows. The rest of this docummentation will explain how each request works and what you will get as a response.

Installation

There is no need for any setup. As long as you can make http requests and use JSON format you're good to go!

You can also use one of our Libraries.
Library for JavaScript on GitHub
Library for PHP on GitHub

Example spreadsheet

Any time you can play with our test API here: https://sheetdb.io/api/v1/58f61be4dda40

You can also go to Google Sheets and play with it: https://docs.google.com/spreadsheets/d/1mrsgBk4IAdSs8Ask5H1z3bWYDlPTKplDIU_FzyktrGk/edit. The spreadsheet resets every hour.

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

Every response is in JSON format. Of course there is a status code in every response. Here is a list of available response codes:

Status Code
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 The request could not be understood by the API.
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 request.
403 Forbidden Action is forbidden.
404 Not Found The server has not found anything matching the request.
429 Too Many Requests Exhausted limit requests. Upgrade your plan.
500 Internal Server Error Server error. You should never see that ones. If you do please .

GET - All data

Returns an array with all data from 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 - asc or desc

curl https://sheetdb.io/api/v1/58f61be4dda40 $url = 'https://sheetdb.io/api/v1/58f61be4dda40'; $options = array( 'http' => array( 'method' => 'GET' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
[
  {
    "id": "1",
    "name": "Tom",
    "age": "41"
  },
  {
    "id": "2",
    "name": "Alex",
    "age": "24"
  },
  {
    "id": "3",
    "name": "John",
    "age": "51"
  },
  {
    "id": "61",
    "name": "Steve",
    "age": "22",
    "comment": "special"
  },
  {
    "id": "422",
    "name": "James",
    "age": "19"
  }
]

GET - Keys

Returns an array with all keys from spreadsheet = values of first row inside document.

curl https://sheetdb.io/api/v1/58f61be4dda40/keys $url = 'https://sheetdb.io/api/v1/58f61be4dda40/keys'; $options = array( 'http' => array( 'method' => 'GET' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
[
  "id",
  "name",
  "age",
  "comment"
]

GET - Document name

Returns a spreadsheet document name.

curl https://sheetdb.io/api/v1/58f61be4dda40/name $url = 'https://sheetdb.io/api/v1/58f61be4dda40/name'; $options = array( 'http' => array( 'method' => 'GET' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
{
  "name": "SheetDB test document"
}

GET - List of available sheets

Returns a list of all available sheets (tabs).

curl https://sheetdb.io/api/v1/58f61be4dda40/sheets $url = 'https://sheetdb.io/api/v1/58f61be4dda40/sheets'; $options = array( 'http' => array( 'method' => 'GET' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
{
  "sheets": [
    "Sheet1",
    "Sheet2"
  ]
}

GET - Count

Returns a number of rows in a document (without first row).

curl https://sheetdb.io/api/v1/58f61be4dda40/count $url = 'https://sheetdb.io/api/v1/58f61be4dda40/count'; $options = array( 'http' => array( 'method' => 'GET' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
{
  "rows": 5
}

GET - Search OR in document

The same as # GET - Search but if any parameter succed to match, it will be listed in response

By default search is not case sensitive. However, if you specify optional parameter casesensitive to true, the search will be case sensitive.

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 something with spacebar, just repace space with %20

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 - asc or desc

curl https://sheetdb.io/api/v1/58f61be4dda40/search_or?name=Steve&age=19&casesensitive=true $url = 'https://sheetdb.io/api/v1/58f61be4dda40/search_or?name=Steve&age=19&casesensitive=true'; $options = array( 'http' => array( 'method' => 'GET' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
[
  {
    "id": "61",
    "name": "Steve",
    "age": "22",
    "comment": "special"
  },
  {
    "id": "422",
    "name": "James",
    "age": "19"
  }
]

POST - Create row

Create rows in document. JSON should have key data - it should be an array of rows. Keys inside the array 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"}]}). If succeeded, API will return the number of created rows with status code 201 Created

curl -X POST -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40 -d '{"data":[{ "name": "Scott", "age": "25" }]}' $url = 'https://sheetdb.io/api/v1/58f61be4dda40'; $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($url, false, stream_context_create($options)) );
{
  "created":1
}

PATCH/PUT - update

Update row(s) for given column and value. Similar to # GET - Search you need to specify key (column name) and value to find. Any rows that matches a condition will be updated.

PATCH requests will update only values passed in data object.

PUT requests will update entire row - some fields might get emptied.

It returns count of updated rows.

curl -X PATCH -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value} -d '{"data":[{ "name": "Scott", "age": "25" }]}' $url = 'https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}'; $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($url, false, stream_context_create($options)) );
{
  "updated":1
}

PATCH/PUT - Batch update

Note: Batch update works only on paid accounts.

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,
  }
]


PATCH requests will update only values passed in data object.

PUT requests will update entire row - some fields might get emptied.

It returns count of updated rows.

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}]}' $url = 'https://sheetdb.io/api/v1/58f61be4dda40/batch_update'; $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($url, false, stream_context_create($options)) );
{
  "updated":2
}

PUT

Update row(s) for given column and value. Similar to # GET - Search you need to specify key (column name) and value to find. Any rows that matches a condition will be updated.

It returns count of updated rows.

curl -X PUT -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value} -d '{"data":[{ "name": "Scott", "age": "25" }]}' $url = 'https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}'; $data = http_build_query( [ 'data' => [ ['name' => 'Scott', 'age' => 25] ] ] ); $options = array( 'http' => array( 'method' => 'PUT', 'header' => 'Content-type: application/x-www-form-urlencoded', 'content' => $data ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
{
  "updated":1
}

DELETE

Delete row(s) for given column and value. Similar to # GET - Search you need to specify key (column name) and value to find. Any rows that matches a condition will be deleted.

It returns count of deleted rows.

curl -X PUT -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value} -d '{"data":[{ "name": "Scott", "age": "25" }]}' $url = 'https://sheetdb.io/api/v1/58f61be4dda40/{column}/{value}'; $options = array( 'http' => array( 'method' => 'DELETE' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
{
  "deleted":1
}

DELETE - All content

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.

curl -X PUT -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/all $url = 'https://sheetdb.io/api/v1/58f61be4dda40/all'; $options = array( 'http' => array( 'method' => 'DELETE' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
{
  "deleted":5
}

Handlebars Installation

Copy the following code and add it before the closing </body> tag.

<script src="https://sheetdb.io/scripts/handlebars-1.0.3.js"></script>

That's it. You are ready to use our handlebars snippet.

Display data

Now you can display data from your spreadsheet.

  1. Add data-sheetdb-url="SHEETDB_API_URL" to the parent element. For example <div>
  2. Add handlebars {{ and }} and fill them with the column name you want to display.

Optional parameters

  • 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)

NOTE: 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>


HTML Example

<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/scripts/handlebars-1.0.3.js"></script>

Result

ID Name Age Comment
{{id}} {{name}} {{age}} {{comment}}

Multiple Sheets

You don't have to specify sheet - by default the first sheet (tab) is selected.

To select other sheets you need to pass a sheet parameter wtih sheet name to request. You can add it to any of RESTful requests.

When sheet is not found you'll get not found error.

curl https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2 $url = 'https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2'; $options = array( 'http' => array( 'method' => 'GET' ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
[
  {
    "player": "Smith",
    "score": "41"
  },
  {
    "player": "Martha",
    "score": "43"
  },
  {
    "player": "Craig",
    "score": "12"
  },
  {
    "player": "Michael",
    "score": "61"
  }
]

POST - Add a sheet to the spreadsheet (tab)

Note: Tabs requests works only on plans premium and above.

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 column names. For example: ["id","name","score"]

curl -X PUT -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/sheet -d '{"name": "New Sheet", "first_row":["id","name"]}' $url = 'https://sheetdb.io/api/v1/58f61be4dda40/sheet'; $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($url, false, stream_context_create($options)) );
{
  "created":1
}

DELETE - Delete a sheet (tab)

Note: Tabs requests works only on plans premium and above.

Deletes a sheet (tab) and the content. Requires one param:

name - the name of the sheet you want to delete

curl -X PUT -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/sheet -d '{"name":"Sheet name"}' $url = 'https://sheetdb.io/api/v1/58f61be4dda40/sheet'; $data = http_build_query( [ 'name' => "Sheet name" ] ); $options = array( 'http' => array( 'method' => 'DELETE', 'header' => 'Content-type: application/x-www-form-urlencoded', 'content' => $data ) ); $result = json_decode( file_get_contents($url, false, stream_context_create($options)) );
{
  "deleted":1
}

Value Input Option

To your POST, PUT and PATCH (create and update) requests, you can contain optional mode param. It controls whether input strings are parsed or not, as described in the following:

RAW (default) - 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.)

USER_ENTERED - 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.

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 Rate Limits

After you hit your rate limit, your requests will recive 429 Too Many Requests. You can check limits or upgrade your account in pricing page.

We are here for you!

If you have any questions please feel free to ask via email or inapp chat.