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 http 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 will receive in response.

You can choose your preferred programming language in the upper right corner.

• Shell - the command line
• PHP - examples in pure 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

No configuration needed. As long as you can send HTTP requests and use the JSON format, you're good to go!

You can also 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. Upgrade your plan.
500 Internal Server Error	We had a problem with our server. Try again later.

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 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 search by date, set this parameter to date, it will automatically detect the date format

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

If you want to search for a string with a space, 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 - sort in asc or desc order
• sort_method - if you want to search 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

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.

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

If you want to search for a string with a space, 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 - sort in asc or desc order
• sort_method - if you want to search 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

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

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.

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

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.

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.

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

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

f# Our handlebars snippet

Handlebars Installation

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

<script src="https://sheetdb.io/scripts/sheetdb-handlebars-1.0.6.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
2. 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/scripts/sheetdb-handlebars-1.0.6.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)

Here is the result of right hand code:

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

Data safety

How do we protect your data

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 on the market 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.

Other

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:

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 in pricing page.

We are here for you!

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