NAV
cURL PHP JavaScript Node.js

Introduction

Each spreadsheet document you want to access must be configured in the SheetDB panel as an API. After creating the API, you’ll receive a unique endpoint url. You can send RESTful requests to read, create, update or delete rows. The rest of the documentation explains how each request works and what you can expect in a response.

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

Installation

You can use one of our Libraries:

WordPress plugin: SheetDB on WordPress
JavaScript: Google Spreadsheet REST API for JavaScript on GitHub
PHP: Google Spreadsheet REST API for PHP on GitHub
Node.js Google Spreadsheet REST API for Node.js on GitHub

Example spreadsheet

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

You can also visit Google Sheets document here: google docs test document.

First row of your spreadsheet should contain the column names. Each next row will be treated as a record (according to naming from first row).

Our Example spreadsheet looks like this:

id name age comment
1 Tom 41
2 Alex 24
3 John 51
61 Steve 22 special
422 James 19

HTTP Status Codes

Each response is in JSON format. Of course, there is a status code in each response. Here is the list of available response codes:

Status Code Meaning
200 OK The request has succeeded for GET, PUT, PATCH and DELETE requests.
201 Created The request has succeeded for POST requests.
400 Bad Request API could not understand the request.
401 Unauthorized An error with authorization using a Google account or incorrect credentials for API if Basic Auth is enabled.
402 Payment Required Payment is required to process the request.
403 Forbidden Action is forbidden.
404 Not Found The server did not find anything matching the request.
429 Too Many Requests Exhausted limit requests. Check API limits
500 Internal Server Error We had a problem with our server. Try again later.
1015 Rate limit You are making too much requests, limit is 60 requests per 10 seconds. Try again in a minute. If you need more please contact us.

SheetDB API

GET - All data

# Get all data
curl https://sheetdb.io/api/v1/58f61be4dda40

# Get 10 results starting from 20
curl https://sheetdb.io/api/v1/58f61be4dda40?limit=10&offset=20

# Get all data sorted by name in ascending order
curl https://sheetdb.io/api/v1/58f61be4dda40?sort_by=name&sort_order=asc

<?php
$options = array(
  'http' => array(
    'method'  => 'GET'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    // Get all data
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40')
    .then( response => {
        console.log(response.data);
    });

    // Get 10 results starting from 20
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40?limit=10&offset=20')
    .then( response => {
        console.log(response.data);
    });

    // Get all data sorted by name in ascending order
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40?sort_by=name&sort_order=asc')
    .then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

// Read whole spreadsheet
client.read().then(function(data) {
    console.log(data);
}, function(error){
    console.log(error);
});

// Read first two rows from sheet "Sheet2"
client.read({ limit: 2, sheet: "Sheet2" }).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

Example response:

[
  {
    "id": "1",
    "name": "Tom",
    "age": "41"
  },
  {
    "id": "2",
    "name": "Alex",
    "age": "24"
  },
  {
    "id": "3",
    "name": "John",
    "age": "51"
  }
]

GET https://sheetdb.io/api/v1/58f61be4dda40

Returns an array with all data from the spreadsheet.

You can use optional parameters:

GET - Keys

# Get keys
curl https://sheetdb.io/api/v1/58f61be4dda40/keys

<?php
$options = array(
  'http' => array(
    'method'  => 'GET'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/keys', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40/keys')
    .then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

client.endpoint('keys').then(function(data) {
    console.log(data);
}, function(error){
    console.log(error);
});

Example response:

["id", "name", "age", "comment"]

GET https://sheetdb.io/api/v1/58f61be4dda40/keys

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

GET - Document name

# Get the document name
curl https://sheetdb.io/api/v1/58f61be4dda40/name

<?php
$options = array(
  'http' => array(
    'method'  => 'GET'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/name', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40/name')
    .then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

client.endpoint('name').then(function(data) {
    console.log(data);
}, function(error){
    console.log(error);
});

Example response:

{
  "name": "SheetDB test document"
}

GET https://sheetdb.io/api/v1/58f61be4dda40/name

Returns the name of the document.

GET - List of available sheets

curl https://sheetdb.io/api/v1/58f61be4dda40/sheets

<?php
$options = array(
  'http' => array(
    'method'  => 'GET'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/sheets', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40/sheets')
    .then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

client.endpoint('sheets').then(function(data) {
    console.log(data);
}, function(error){
    console.log(error);
});

Example response:

{
  "sheets": [
    "Sheet1",
    "Sheet2"
  ]
}

GET https://sheetdb.io/api/v1/58f61be4dda40/sheets

Returns a list of all available sheets (tabs).

GET - Count

curl https://sheetdb.io/api/v1/58f61be4dda40/count

<?php
$options = array(
  'http' => array(
    'method'  => 'GET'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/count', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40/count')
    .then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

client.endpoint('count').then(function(data) {
    console.log(data);
}, function(error){
    console.log(error);
});

Example response:

{
  "rows": 5
}

GET https://sheetdb.io/api/v1/58f61be4dda40/count

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

GET - Search in document

curl https://sheetdb.io/api/v1/58f61be4dda40/search?name=Steve&age=22&casesensitive=true

<?php
$options = array(
  'http' => array(
    'method'  => 'GET'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/search?name=Steve&age=22&casesensitive=true', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40/search?name=Steve&age=22&casesensitive=true')
    .then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

// Get all rows where column 'id' is 'foo' and column 'value' is 'bar'
client.read({ search: { id: "1", name: "Tom" } }).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

// Get first row where column 'player' is 'Smith',
// column 'score' is '41' from sheet named "Sheet2"
client.read({
  limit: 1,
  search: { 'player': 'Smith', 'score': 41 },
  sheet: 'Sheet2'
}).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

Example response:

[
  {
    "id": "61",
    "name": "Steve",
    "age": "22",
    "comment": "special"
  }
]

GET https://sheetdb.io/api/v1/58f61be4dda40/search?name=Steve&age=22&casesensitive=true

GET https://sheetdb.io/api/v1/58f61be4dda40/search_or?name=Steve&age=22&casesensitive=true

Returns an array of all rows matching parameters. If any query fails, the result will NOT be listed. If you want to check if any parameter is true, use the search_or endpoint (second URL above).

You can search using wildcards. Asterisk (*) can represent any string. Wildcard work only when READ and SEARCH permissions are both enabled, if only SEARCH peremission is enabled, wildcard will not work for security reasons.

If you want to exclude rows from the search results, use an exclamation mark before the value. For example, if you want all rows without name=Tom use this url: https://sheetdb.io/api/v1/58f61be4dda40/search?name=!Tom

You can use relational operators to determine if a value is greater than or less than a given number. Just start with <, >, <=, >= operator. Example urls:

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:

For more examples, check out this blog post: Search feature in SheetDB API or this youtube video: Using SheetDB search API to filter results from Google Sheets.

POST - Create row

curl -X POST -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40 -d '{"data":[{ "name": "Scott", "age": "25" }]}'

<?php
$data = http_build_query(
    [
        'data' =>
        [
          ['name' => 'Scott', 'age' => 25]
        ]
    ]
);

$options = array(
  'http' => array(
    'method'  => 'POST',
    'header'  => 'Content-type: application/x-www-form-urlencoded',
    'content' => $data
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.post('https://sheetdb.io/api/v1/58f61be4dda40',{
        "data": {"name": "Scott", "age": 25}
    }).then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

// Adds single row
client.create({ name: "William", age: 25 }).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

/// Adds bunch of rows
rows = [
  { name: "William", age: 25 },
  { name: "Jayden", age: 25 }
]
client.create(rows).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

Example response:

{
  "created": 1
}

POST https://sheetdb.io/api/v1/58f61be4dda40

Create rows in document. Your request should contain data parameter - it should be an array of rows. Keys inside the object should be a column names (see # GET - keys) and values will be values inside a spreadsheet. Rows will be added at the end of spreadsheet. If you want to add a single row, simply send an array with one item {"data": [{"id":5,"name":"Frank"}]}.

You can also add multiple rows at once using an array of objects. Example: {"data": [{"id":5,"name":"Frank"},{"id":6,"name":"Marc"}]}

You can use value INCREMENT (upper case). SheetDB will look for the biggest number in the given column and increase by 1.

If for some reason you cannot put your data into the data parameter, SheetDB will try to use the entire request data to match the spreadsheet format.

API will return the number of created rows with status code 201 Created.

POST - Import JSON

curl -X POST -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/import/json -d '{"json":[{ "name": "Scott", "age": "25" }]}'

<?php
$data = http_build_query(
    [
        'json' =>
        [
          ['name' => 'Scott', 'age' => 25]
        ]
    ]
);

$options = array(
  'http' => array(
    'method'  => 'POST',
    'header'  => 'Content-type: application/x-www-form-urlencoded',
    'content' => $data
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/import/json', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.post('https://sheetdb.io/api/v1/58f61be4dda40/import/json',{
        "json": '{"name": "Scott", "age": 25}'
    }).then( response => {
        console.log(response.data);
    });
</script>

Example response:

{
  "created": 1
}

POST https://sheetdb.io/api/v1/58f61be4dda40/import/json

If you have an empty spreadsheet, above all with a first row unfilled, you can use this endpoint to import entire JSON file. Make sure that the spreadsheet is empty or the endpoint will append new content to the first row. The first row (column names) will be generated from the first object in the array.

Required parameters:

Optional parameters:

API will return the number of created rows with status code 201 Created.

PATCH/PUT - Update

curl -X PATCH -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/id/61 -d '{"data":[{ "name": "Scott", "age": "25" }]}'

<?php
$data = http_build_query(
    [
        'data' =>
            [
              ['name' => 'Scott', 'age' => 25]
            ]
    ]
);

$options = array(
  'http' => array(
    'method'  => 'PATCH',
    'header'  => 'Content-type: application/x-www-form-urlencoded',
    'content' => $data
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/id/61', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.patch('https://sheetdb.io/api/v1/58f61be4dda40/id/61',{
        "data": {"name": "Scott", "age": 25}
    }).then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

// Update all columns where 'name' is 'Smith' to have 'score' = 99 and 'comment' = 'Griffin'
client.update(
    'name', // column name
    'Tom', // value to search for
    { 'comment': 'Updated' } // object with updates
).then(function (data) {
    console.log(data);
}, function (err) {
    console.log(err);
});

// Update all columns where 'name' is 'Smith' to have 'score' = 99 and 'comment' = 'Griffin'
// In sheet named 'Sheet2'
client.update(
    'player', // column name
    'Smith', // value to search for
    { 'score': 99, 'comment': 'Griffin' }, // object with updates
    'Sheet2'
).then(function (data) {
    console.log(data);
}, function (err) {
    console.log(err);
});

Example response:

{
  "updated": 1
}

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

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

Requests will update only values passed in data object.

It returns count of updated rows.

PATCH/PUT - Batch update

curl -X PATCH -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/batch_update -d '{"data":[{"query":"id=1", "name":"Mathew", "age":20},{"query":"id=2", "age":25}]}'

<?php
$data = http_build_query(
[
    'data' =>
    [
        ['query' => 'id=1', 'name' => 'Mathew', 'age' => 20],
        ['query' => 'id=2', 'age' => 25],
    ]
]);

$options = array(
  'http' => array(
    'method'  => 'PATCH',
    'header'  => 'Content-type: application/x-www-form-urlencoded',
    'content' => $data
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/batch_update', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.patch('https://sheetdb.io/api/v1/58f61be4dda40/batch_update', {
        data: [
            {
                "query":"id=1",
                "name":"qwes",
                "age":20
            },
            {
                "query":"id=2",
                "age":25,
            }
        ]
    }).then( response => {
        console.log(response.data);
    });
</script>

// our Node.js library does not support this method

Example response:

{
  "updated": 2
}

PATCH/PUT https://sheetdb.io/api/v1/58f61be4dda40/batch_update

Update for various queries. You have to add an data param to your request. Each object in it should have a query key with the actual query (for example, "id=5"), and the remaining keys will be updated, as in a regular PATCH / PUT request. Here is an example of the data parameter in the body of the request:

[ { "query":"id=1", "name":"Mathew", "age":20 }, { "query":"id=2", "age":25, } ]

Requests will update only values passed in data object.

It returns count of updated rows.

DELETE

curl -X DELETE -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/id/61'

<?php
$options = array(
  'http' => array(
    'method'  => 'DELETE'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/id/61', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.delete('https://sheetdb.io/api/v1/58f61be4dda40/id/61')
    .then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

// Delete all rows where 'name' equals 'Smith'
client.delete(
  'id', // column name
  '1' // value to search for
).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

Example response:

{
    "deleted": 1
}

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

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

It returns count of deleted rows.

You can use optional parameters:

DELETE - Duplicates

curl -X DELETE -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/duplicates

<?php
$options = array(
  'http' => array(
    'method'  => 'DELETE'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/duplicates', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.delete('https://sheetdb.io/api/v1/58f61be4dda40/duplicates')
    .then( response => {
        console.log(response.data);
    });
</script>

// our Node.js library does not support this method

Example response:

{
    "duplicates": 3
}

DELETE https://sheetdb.io/api/v1/58f61be4dda40/duplicates

Deletes all duplicate rows, all columns must contain exactly the same data to be deleted.

By default first sheet (tab) is selected but you can target any sheet you want using param sheet. Example: ?sheet=Sheet2

It returns count of duplicated rows.

DELETE - All content

curl -X DELETE -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/all

<?php
$options = array(
  'http' => array(
    'method'  => 'DELETE'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/all', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.delete('https://sheetdb.io/api/v1/58f61be4dda40/all')
    .then( response => {
        console.log(response.data);
    });
</script>

// our Node.js library does not support this method

Example response:

{
    "deleted": 5
}

DELETE https://sheetdb.io/api/v1/58f61be4dda40/all

Delete all rows without the first one (column names) in spreadsheet. By default first sheet (tab) is selected but you can target any sheet you want using param sheet. Example: ?sheet=Sheet2

It returns count of deleted rows.

Single cells

GET - Cells values

# Get A1 cell
curl https://sheetdb.io/api/v1/58f61be4dda40/cells/A1

<?php
$options = array(
  'http' => array(
    'method'  => 'GET'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/cells/A1', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40/cells/A1')
    .then( response => {
        console.log(response.data);
    });
</script>

// we do not support single cells in our node package :(

Example response:

{
    "A1":"id"
}

GET https://sheetdb.io/api/v1/58f61be4dda40/cells/A1

If you want to retrieve the contents of a single cell using coordinates like A1, B10 etc. you can use this endpoint. Returns an array with coordinates as key and cell contents as value.

If you want to get multiple cells just separate them with a comma. Example:

GET https://sheetdb.io/api/v1/58f61be4dda40/cells/A1,A2,B5

Multiple Sheets (tabs)

To select other then the first sheet you need to pass a sheet parameter wtih sheet name. You can add this parameter to any of our RESTful requests (GET, POST, PATCH, PUT, DELETE).

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

Here are sample URLs for different methods (for a sheet named sheet2):

GET - All data from a tab

# Get all data from "Sheet2"
curl https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2

# You can still use limit, offset and sort parameters
curl https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2&limit=10&offset=20

<?php
// define options
$options = array(
  'http' => array(
    'method'  => 'GET'
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    // Get all data
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2')
    .then( response => {
        console.log(response.data);
    });

    // You can still use limit, offset and sort parameters
    axios.get('https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2&limit=10&offset=20')
    .then( response => {
        console.log(response.data);
    });
</script>

const sheetdb = require("sheetdb-node");
const client = sheetdb({ address: '58f61be4dda40' });

// Sheet "Sheet2"
client.read({ sheet: "Sheet2" }).then(function(data) {
  console.log(data);
}, function(err){
  console.log(err);
});

Example response:

[
  {
    "player": "Smith",
    "score": "41"
  },
  {
    "player": "Martha",
    "score": "43"
  },
  {
    "player": "Craig",
    "score": "12"
  },
  {
    "player": "Michael",
    "score": "61"
  }
]

GET https://sheetdb.io/api/v1/58f61be4dda40?sheet=Sheet2

This is an example how to get data from sheet Sheet2

POST - Add a sheet to the spreadsheet (tab)

curl -X POST -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/sheet -d '{"name": "New Sheet", "first_row":["id","name"]}'

<?php
$data = http_build_query(
  [
    'name' => "New Sheet",
    'first_row' => ["id","name"]
  ]
);

$options = array(
  'http' => array(
    'method'  => 'POST',
    'header'  => 'Content-type: application/x-www-form-urlencoded',
    'content' => $data
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/sheet', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.post('https://sheetdb.io/api/v1/58f61be4dda40/sheet',{"name": "New Sheet", "first_row":["id","name"]})
    .then( response => {
        console.log(response.data);
    });
</script>

// our Node.js library does not support this method

Example response:

{
  "created":1
}

POST https://sheetdb.io/api/v1/58f61be4dda40/sheet

Creates a new sheet (tab) in the spreadsheet. 2 params are required:

DELETE - Delete a sheet (tab)

curl -X DELETE -H "Content-Type: application/json" https://sheetdb.io/api/v1/58f61be4dda40/sheet -d '{"name":"New Sheet"}'

<?php
$data = http_build_query(
  [
    'name' => "New Sheet"
  ]
);

$options = array(
  'http' => array(
    'method'  => 'DELETE',
    'header'  => 'Content-type: application/x-www-form-urlencoded',
    'content' => $data
  )
);

$result = json_decode(
  file_get_contents('https://sheetdb.io/api/v1/58f61be4dda40/sheet', false, stream_context_create($options))
);

<script src="//cdnjs.cloudflare.com/ajax/libs/axios/0.18.0/axios.js"></script>
<script>
    axios.delete('https://sheetdb.io/api/v1/58f61be4dda40/sheet',{
        "data":{
            "name":"New Sheet"
        }
    })
    .then( response => {
        console.log(response.data);
    });
</script>

// our Node.js library does not support this method

Example response:

{
  "deleted": 1
}

DELETE https://sheetdb.io/api/v1/58f61be4dda40/sheet

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

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.

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

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.

If you want to use more query strings, separate them with commas with no spaces in between. E.g data-sheetdb-query-string="id,age".

Here is the result of example code:

After entering your ID, watch what's happening in the address bar. After you submit, ?id=xxx will be added to the url. This change in the url causes this feature to work. You can also add this parameter to your links to filter data using query strings.

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

Slots

<span data-sheetdb-url="https://sheetdb.io/api/v1/58f61be4dda40"
        data-sheetdb-save="slot-example">
    {{name}}
</span>

<span data-sheetdb-slot="slot-example">
    {{id}}
</span>

If you want to re-use your data, you can use the data-sheetdb-save attribute in your sheetdb element. To re-use your data use data-sheetdb-slot. You can use the same data inside as in the parent. To match them the value of save and slot must be the same

This way, you only use 1 request instead of 2. Slots have access to the same data as the parent. You can’t change things like limit or search.

This is the result of our example (using only 1 request):

{{name}}

{{id}}

Events

window.addEventListener("sheetdb-downloaded", function() {
    // this code will be executed after all sheetdb data has been retrieved
});

After downloading all the data, the handlebars library will trigger a custom event sheetdb-downloaded. You can listen for this event to execute some javascript code after getting the data and rendering the DOM.

Update content

If you want to update the contents of SheetDB, you can use the global function sheetdb_upd(). It will force all content to be downloaded again from the spreadsheet.

Other

Value Render Option

To your GET (get and search) requests, you can add optional mode parameter. It controls how values should be rendered in the output, as described in the following:

FORMATTED_VALUE (default) - Values will be calculated & formatted in the reply according to the cell's formatting. Formatting is based on the spreadsheet's locale, not the requesting user's locale. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "$1.23".

UNFORMATTED_VALUE - Values will be calculated, but not formatted in the reply. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

FORMULA - Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "=A1".

Value Input Option

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

USER_ENTERED (default) - The input is parsed exactly as if it were entered into the Google Sheets UI, so "Mar 1 2016" becomes a date, and "=1+2" becomes a formula. Formats may also be inferred, so "$100.15" becomes a number with currency formatting.

RAW - The input is not parsed and is simply inserted as a string, so the input "=1+2" places the string "=1+2" in the cell, not a formula. (Non-string values like booleans or numbers are always handled as RAW.)

Authentication

You can add HTTP Basic Auth to each API as an additional security for your API's. You can enable it on the API Settings tab.

You will receive a login and password. You have to send it for every request that has basic authentication enabled. If the credentials are incorrect API will respond with error 401 Unauthorized.

API Limits

After you hit your rate limit, your requests will return an error: 429 Too Many Requests. You can check limits at our pricing page.

There are 2 more limits:

Both are limits imposed by Google and cannot be changed. If you need more, best solution is enabling cache. SheetDB limit will be calculated as usual, however Google limits do not apply because in the case of cache results we do not query Google servers.

Make the first request before a larger batch to prepare the cache.

Additionally, there is a limit - 60 requests per 10 seconds from one IP address. If you need more please contact us.

Caching

By default, we cache your API data for 15 seconds. If you are on paid plan you can change this period in API settings up to 7 days. Caching guarantees the fastest response time, especially for a large number of requests.

When cache is enabled, changes to the spreadsheet are not reflected in the API's until the cache expires, or until you make changes to the spreadsheet using SheetDB API. There is also an endpoint to reset cache in API settings.

Example:

Data safety

The confidentiality of your data is very important to us, which is why we do not store any of your data on our servers, other than API data for caching reasons. Also, for people without an API URL, guessing is almost impossible, but if you still need more protection, you can enable Basic Auth.

We use the best tools and services available to protect your data.

What does SheetDB have access to?

SheetDB can only access spreadsheets for which the API has been created. This means that SheetDB do not have access to your other files.

This documentation is on GitHub

If you'd like to correct or add something here, please submit a pull request.

GitHub logo Edit on GitHub

We are here for you!

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

cURL PHP JavaScript Node.js