QuickStart

Welcome

Welcome to Sheet Best's API Documentation.

Our documentation is available in RESTful architectural style.

Here you will find a quick guide on how to start getting your Sheet data and integrating it to your application.

How to Prepare your Sheet

Sheet Best uses the first row of your Sheet as a reference to index the data below it. The values of the following rows will always follow the structure given by the first row, so keep that in mind when preparing your Sheet.

There are no restrictions to which data can be inserted on the rows following the first one, number, text, date and even empty cells will be handled.

This way, you can turn a spreadsheet like this:

Into a JSON Response value like this:

[
  {
    "Id": "1",
    "Name": "John Doe",
    "Age": "23",
    "Created at": "8/25/2018 23:36:00"
  },
  {
    "Id": "2",
    "Name": "Jane Doe",
    "Age": "34",
    "Created at": "8/15/2018 11:10:35"
  },
  {
    "Id": "3",
    "Name": "John Smith",
    "Age": "45",
    "Created at": "8/1/2018 17:28:16"
  },
  {
    "Id": "4",
    "Name": "Jane Smith",
    "Age": "56",
    "Created at": "8/10/2018 19:31:52"
  }
]

Generating Your REST API

To generate your REST API with Google you'll need to:

Get a Connection URL
Insert it on Sheet Best

Or if you want to use your own SpreadSheet File:

Upload your SpreadSheet to SheetBest

How to get a Connection URL (Google Sheets)

Start by going to your Google Sheet and clicking the share button

Change the Sheet Permission to allow access for anyone with the link to view the Sheet

How to allow editing (Google Sheets)

If you want to use our APIs to modify the Sheet, you'll also need to provide edit access for the Sheet. This is optional and only needed for POST, DELETE, PUT, and PATCH requests

How to get a Connection URL (Google Drive Folder)

Start by right clicking a folder on your Google Drive and selecting Get Link

Right Click to Share

Change the Folder's permission to allow access to Anyone with the link and Copy the link

Inserting your Connection URL into Sheet Best

Login to Sheet Best
On Sheet Best's initial page after logging in, click + CONNECTION

Add Connection

Name your Connection on the form that have just appeared
Select your Connection Origin within the list
Paste your Connection URL
Click CONNECT button

Connect Form Fill

Click on the DETAILS button for the your new Connection.
Your Connection URL will be available for copy on CONNECTION URL section

Copy Connection URL

How to Upload your SpreadSheet file to Sheet Best

Login to Sheet Best
On Sheet Best's initial page after logging in, click + CONNECTION

Add Connection

Name your Connection on the form that have just appeared
Select File Upload from your Connection Origin within the list
Upload your SpreadSheet from the File field
Click CONNECT button

Upload Sheet

Click on the DETAILS button for the your new Connection.
Your Connection URL will be available for copy on CONNECTION URL section

Upload Sheet URL

Using your REST API

You can use your Connection URL via REST API through GET, POST, PATCH, PUT and DELETE methods. We'll briefly explain what each method does below:

GET - Read Rows

# Parsed Format
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf'

# Raw Format
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_raw=1'

# Install python "requests" module to use this
import requests

# Parsed Format
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf")

# Raw Format
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_raw=1")

// Parsed Format
fetch("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf")
  .then((response) => response.json())
  .then((data) => {
    console.log(data);
  })
  .catch((error) => {
    console.error(error);
  });

// Raw Format
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_raw=1"
)
  .then((response) => response.json())
  .then((data) => {
    console.log(data);
  })
  .catch((error) => {
    console.error(error);
  });

To read all data from the API, make GET requests to the link we've provided on Generating Your REST API.

We will parse the entire sheet and present it like mentioned in How to Prepare your Sheet.

Parsed Format

Results can come in parsed and raw formats. They will always come as parsed by default.

Parsed JSON Format

Raw Format

To get the result in raw format, add a _raw=1 query parameter at the end of the URL.

Raw JSON Format

POST - Add Rows

# Add one line to the sheet
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf' \
    -H 'Content-Type: application/json' \
    -d '{"Id":10, "Name": "Jack Doe", "Age": 97, "Created at": "2019-08-19T13:32:11.744Z"}'

# Add two lines to the sheet
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf' \
    -H 'Content-Type: application/json' \
    -d '[{"Id":10, "Name": "Jack Doe", "Age": 97, "Created at": "2019-08-19T13:32:11.744Z"}, {"Id":11, "Name": "John Doe", "Age": 44, "Created at": "2019-08-19T13:32:11.744Z"}]'

from datetime import datetime

import requests

# Add one line to the sheet
requests.post(
    "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf",
    json={
        'Id': '10',
        'Name': 'Jack Doe',
        'Age': '97',
        'Created at': datetime.now().isoformat(),
    },
)

# Add two lines to the sheet
requests.post(
    "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf",
    json=[{
        'Id': '10',
        'Name': 'Jack Doe',
        'Age': '97',
        'Created at': datetime.now().isoformat(),
    }, {
        'Id': '11',
        'Name': 'John Doe',
        'Age': '44',
        'Created at': datetime.now().isoformat(),
    }],
)

const data = {
  Id: 10,
  Name: "Jack Doe",
  Age: 97,
  "Created at": new Date(),
};

// Add one line to the sheet
fetch("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf", {
  method: "POST",
  mode: "cors",
  headers: {
    "Content-Type": "application/json",
  },
  body: JSON.stringify(data),
})
  .then((r) => r.json())
  .then((data) => {
    // The response comes here
    console.log(data);
  })
  .catch((error) => {
    // Errors are reported there
    console.log(error);
  });

// Add two lines to the sheet
fetch("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf", {
  method: "POST",
  mode: "cors",
  headers: {
    "Content-Type": "application/json",
  },
  body: JSON.stringify([
    {
      Id: "10",
      Name: "Jack Doe",
      Age: "97",
      "Created at": datetime.now().isoformat(),
    },
    {
      Id: "11",
      Name: "John Doe",
      Age: "44",
      "Created at": datetime.now().isoformat(),
    },
  ]),
})
  .then((r) => r.json())
  .then((data) => {
    // The response comes here
    console.log(data);
  })
  .catch((error) => {
    // Errors are reported there
    console.log(error);
  });

To write data, your can send a POST request sending the JSON data to the same URL as your GET API. This will insert a new line on your sheet with the information you've sent.

POST JSON Example

You can also send an array of objects to post add multiple rows at once:

Multiple POST JSON Example

DELETE - Delete Rows

# Basic Delete
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/2' -X DELETE

# Filtered Delete
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*Jane*' -X DELETE

import requests

# Basic Delete
requests.delete("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/2")

# Filtered Delete
requests.delete("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*Jane*")

// Basic Delete
fetch("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/2", {
  method: "DELETE",
})
  .then((response) => response.json())
  .then((data) => {
    console.log(data);
  })
  .catch((error) => {
    console.error(error);
  });

// Filtered Delete
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*Jane*",
  {
    method: "DELETE",
  }
)
  .then((response) => response.json())
  .then((data) => {
    console.log(data);
  })
  .catch((error) => {
    console.error(error);
  });

You can delete data from your Sheet by making a DELETE request to your API.

Basic Delete

Append the row number on your API URL following this pattern: https://api.sheetbest.com/sheets/<api id>/<row number> and you will delete that row from the Sheet.

Example: If your API URL is https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf, make delete calls to: https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/2 to delete the third row from your Sheet (it is 0 indexes).

Filtered Delete

You can also make Filtered Deletes to delete rows that fits in a filter.

Let's say you have a table like this one:

If you want to delete all rows that have "Jane" inside the "Name" column, you can make a DELETE request to:

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*Jane*

For more Filtered Delete options, check out our Filtering Data section, for more options, all options supported there are supported here.

PUT/PATCH - Update Rows

# Update first row setting the name to "Jack Doe"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/1' \
    -X PATCH \
    -H 'Content-Type: application/json' \
    -d '{"Name": "Jack Doe"}'

# Update the first row setting the name to "Jack Doe" and erasing other columns data
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/1' \
    -X PUT \
    -H 'Content-Type: application/json' \
    -d '{"Name": "Jack Doe"}'

# Filtered Update
# Update all rows that have "John" inside the name column and change it to "Jack Doe"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*John*' \
    -X PATCH \
    -H 'Content-Type: application/json' \
    -d '{"Name": "Jack Doe"}'

import requests

# Update first row setting the name to "Jack Doe"
requests.patch(
    "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/1",
    json={
        'Name': 'Jack Doe',
    },
)

# Update the first row setting the name to "Jack Doe" and erasing other columns data
requests.put(
    "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/1",
    json={
        'Name': 'Jack Doe',
    },
)

# Filtered Update
# Update all rows that have "John" inside the name column and change it to "Jack Doe"
requests.patch(
    "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*John*",
    json={
        'Name': 'Jack Doe',
    },
)

// Update first row setting the name to "Jack Doe"
fetch("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/1", {
  method: "PATCH",
  mode: "cors",
  headers: {
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    Name: "Jack Doe",
  }),
})
  .then((r) => r.json())
  .then(console.log)
  .catch(console.error);

// Update the first row setting the name to "Jack Doe" and erasing other columns data
fetch("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/1", {
  method: "PUT",
  mode: "cors",
  headers: {
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    Name: "Jack Doe",
  }),
})
  .then((r) => r.json())
  .then(console.log)
  .catch(console.error);

// Filtered Update
// Update all rows that have "John" inside the name column and change it to "Jack Doe"
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*John*",
  {
    method: "PATCH",
    mode: "cors",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      Name: "Jack Doe",
    }),
  }
)
  .then((r) => r.json())
  .then(console.log)
  .catch(console.error);

PUT and PATCH requests are two ways of updating existing data in your Sheet.

You can update the data on your Sheet by sending PUT and PATCH requests, with the following pattern: https://api.sheetbest.com/sheets/<api id>/<row number>.

Example: If your API URL is https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf, make update calls to: https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/2 to update the third row from your Sheet (it is 0 indexes).

PUT - Overwriting row data

When sending a PUT request, the entire row data will be replaced with the information you provide. Any columns not explicitly included in the request will be set to blank values.

This methods will return the affected records.

PATCH - Update only the data sent into the sheet

Sending a PATCH request will only update the data included in the request. This feature is useful for updating specific columns without affecting the rest of the data in the row.

This method returns the affected records, including all updated and retained information.

Filtered Update

You can also make Filtered Updates to update rows that fits in a filter.

Let's say you have a table like this one:

If you want to update all rows that have "John" inside the "Name" column, you can make a PATCH or PUT request to:

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*John*

For more Filtered Update options, check out our Filtering Data section, for more options, all options supported there are supported here.

Troubleshooting

HTTP Response Headers

In order to help you manage your sheet and quotas, we provide you a way of checking it via HTTP Headers.

Check out these response headers to manage your sheets and quotas while you're querying them.

Header	Description
X-RateLimit-Limit	A number with the maximum number of requests you have for the current month
X-RateLimit-Plan	The current plan you're into
X-RateLimit-Remaining	The remaining API calls you have for the current month, when this hits 0 you'll receive a 402 error
X-Sheet-Columns	The total number of columns your sheet has
X-Sheet-Rows	The total number of rows your sheet has

HTTP Status Codes

Sheet Best provides a RESTful API that indicates the success or failure of the API request using HTTP response codes.

Codes in the 4xx range indicate an error that failed given the information provided.

Codes in the 5xx range indicate an error with Sheet Best's server (these are rare).

Status Codes	Description
200 - OK	Everything worked as expected.
400 - Bad Request	The request was unacceptable, often due to missing a required parameter or permission to the spreadsheet.
401 - Authentication Failed	Incorrect authentication credentials
402 - Payment Required	This operation is not allowed in your current plan.
403 - Forbidden	You do not have permission to perform this action.
404 - Not Found	The requested resource doesn't exist.
405 - Method Not Allowed	When calling a endpoint using a not registered method. Ex: POST for the search endpoint
402 - Payment required	You already used all of your plan's requests this month.
500, 502, 503, 504 - Server Errors	Something went wrong on Sheet Best's end, we will fix it.

Some errors will also come with a textual error codes, in this case, you can check them in the table below:

Error Codes	Description
throttle	You already used all of your plan's requests this month.
write_error	Sheet Best could not update your sheet, you should verify its permissions.
authentication_failed	You passed incorrect authentication credentials.
not_authenticated	You did not pass the authentication credentials.
permission_denied	You do not have permission to perform this actions.

Advanced Usage

Retrieving different data formats on GET

# Records
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=records'

# Dict
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=dict'

# List
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=list'

# Series
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=series'

# Split
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=split'

# Index
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=index'

import requests

# Records
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=records")

# Dict
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=dict")

# List
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=list")

# Series
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=series")

# Split
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=split")

# Index
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=index")

// Records
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=records"
);

// Dict
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=dict"
);

// List
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=list"
);

// Series
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=series"
);

// Split
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=split"
);

// Index
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_format=index"
);

You can pass a optional querystring parameter to our API called _format, that will format your data to several different formats for your convenience. We format data using records format by default.

Format	Mapping
records	[{column -> value}, … , {column -> value}]
dict	{column -> {index -> value}}
list	{column -> [values]}
series	{column -> Series(values)}
split	{‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values]}
index	{index -> {column -> value}}

Records Format

Dict Format

List Format

Series Format

Split Format

Index Format

Authentication - Protect your API with Keys

# Display data from the "Admin" tab
curl -H 'X-Api-Key: <Your API Key>' 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf'

import requests

requests.get(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf",
  headers={
    'X-Api-Key': '<Your API Key>'
  }
)

fetch("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf", {
  headers: {
    "X-Api-Key": "<Your API Key>",
  },
});

You can optionally protect your API using API Keys, to enable this functionality you can edit your Connection by clicking the "Edit" button on the top right corner of the connection details page:

Edit Connection

Then go to Advanced Settings and enable the API Key functionality

Protect Your API

You can then make requests using the X-Api-Key, like the examples by the side.

Working with different Tabs

# Display data from the "Admin" tab
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin'

# Writing data to "Admin" tab
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin' \
    -H 'Content-Type: application/json' \
    -d '[{"Name": "Jack Doe", "Age": 97, "Created at": "2019-08-19T13:32:11.744Z"}]'

import requests

# Display data from the "Admin" tab
requests.get("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin")

# Writing data to "Admin" tab
requests.post(
    "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin",
    json=[{
        'Name': 'Jack Doe',
        'Age': '97',
        'Created at': datetime.now().isoformat(),
    }],
)

// Display data from the "Admin" tab
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin"
)
  .then((response) => response.json())
  .then((data) => {
    console.log(data);
  })
  .catch((error) => {
    console.error(error);
  });

const data = [
  {
    Name: "Jack Doe",
    Age: 97,
    "Created at": new Date(),
  },
];

// Writing data to "Admin" tab
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin",
  {
    method: "POST",
    mode: "cors",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify(data),
  }
)
  .then((r) => r.json())
  .then((data) => {
    // The response comes here
    console.log(data);
  })
  .catch((error) => {
    // Errors are reported there
    console.log(error);
  });

By default we will always work with the first tab of the Sheet for all operations, but you can also work with different tabs by using the suffix: /tabs/<Tab Name>.

Tab operations will work with all basic and operations.

Filtering Data

# Only rows with "Jane Doe" as "Name"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/Jane Doe'

# Only rows with "John" inside "Name"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*John*'

# Only rows with "John" inside "Name" and with "Age" as "56"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/search?Name=*John*&Age=56'

# Only rows with "Arthur" inside "Name" inside "Admin" tab
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin/Name/*Arthur*'

import requests

# Only rows with "Jane Doe" as "Name"
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/Jane Doe')

# Only rows with "John" inside "Name"
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*John*')

# Only rows with "John" inside "Name" and with "Age" as "56"
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/search?Name=*John*&Age=56')

# Only rows with "Arthur" inside "Name" inside "Admin" tab
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin/Name/*Arthur*')

// Only rows with "Jane Doe" as "Name"
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/Jane Doe"
);

// Only rows with "John" inside "Name"
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/Name/*John*"
);

// Only rows with "John" inside "Name" and with "Age" as "56"
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/search?Name=*John*&Age=56"
);

// Only rows with "Arthur" inside "Name" inside "Admin" tab
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin/Name/*Arthur*"
);

Data can be filtered using exact values and wildcard matching ( * ).

Filtering by Column

Column filtering can be done in a simple way by appending /<Column Name>/<Column Value> suffix into the URL.

You can filter all rows with Jane Doe exact value inside Name column by appending the suffix /Name/Jane Doe.

You can use wildcard matching by using the * character inside the suffix.

You can filter all rows with Jane inside the Name column by appending the suffix /Name/*Jane*.
You can filter all rows that starts with Jane inside the Name column by appending the suffix /Name/Jane*.
You can filter all rows that ends with Jane inside the Name column by appending the suffix /Name/*Jane.
You can enable case-insensitive filtering by including the query parameter search_ci with the value true.

Filtering multiple columns

You can filter multiple columns by using the /search suffix, combined with the query params following the pattern <Column Name>=<Column Value>.

Wildcard matching works when filtering multiple columns too.

You can filter all rows with Jane inside the Name column and 21 in the Age column by appending the suffix: /search?Name=*Jane*&Age=21

Combining Filters with Tabs

You can filter other tabs data by appending /tabs/<Tab Name> before the filter suffixes:

You can filter all rows with Mick inside the Name column and 21 in the Age column with using the suffix: /tabs/Admin/search?Age=21&Name=*Mick*

Querying Data

# Only rows with "Amount" greater than "5.75"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__gt(5.75)'

# Only rows with "Hire Date" less than "07-01-2021"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Hire Date=__lt(7/01/2021)'

# Only rows with "Amount" greater than or equal to "1000.00" and with "Age" less than or equal to "56"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__gte(1000)&Age=__lte(56)'

# Only rows with "Amount" less than "500.00" and with "Customer Email" starts with "L"
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__lt(500)&Customer%20Email=L*'

import requests

# Only rows with "Amount" greater than "5.75"
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__gt(5.75)')

# Only rows with "Hire Date" less than "07-01-2021"
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Hire Date=__lt(7/01/2021)')

# Only rows with "Amount" greater than or equal to "1000.00" and with "Age" less than or equal to "56"
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__gte(1000)&Age=__lte(56)')

# Only rows with "Amount" less than "500.00" and with "Customer Email" starts with "L"
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__lt(500)&Customer%20Email=L*')

// Only rows with "Amount" greater than "5.75"
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__gt(5.75)"
);

// Only rows with "Hire Date" less than "07-01-2021"
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Hire Date=__lt(7/01/2021)"
);

// Only rows with "Amount" greater than or equal to "1000.00" and with "Age" less than or equal to "56"
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__gte(1000)&Age=__lte(56)"
);

// Only rows with "Amount" less than "500.00" and with "Customer Email" starts with "L"
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__lt(500)&Customer%20Email=L*"
);

Data can be queried using the /query suffix and operators.

Query Operators

We offer the following operators you may query against numbers and dates:

__eq() - equals operator, i.e. find row with Id of 1 /query?Id=__eq(1).
__ne() - not equal operator, i.e. find rows with Id not equal to 5 /query?Id=__ne(5).
__lt() - less than operator, i.e. find Amount less than 5.75 /query?Amount=__lt(5.75).
__lte() - less than or equals operator, i.e. find Amount less than or equal to 5.75 /query?Amount=__lte(5.75).
__gt() - greater than operator, i.e. find Created Date greater than 8/01/2021 /query?Created Date=__gt(8/01/2021).
__gte() - greater than or equals operator, i.e. find Created Date of 8/01/2021 /query?Created Date=__gte(8/01/2021).

Querying multiple columns

You can query multiple columns by using the /query suffix, combined with the query operators following the pattern <Column Name>=<Operator(Column Value)>.

Wildcard matching works when querying multiple columns too.

You can find all rows with Jane inside the Name column and greater than or equals to 21 in the Age column and dates less than 07/01/2020 in the Hire Date column by appending the suffix: /query?Name=*Jane*&Age=__gte(21)&Hire Date=__lt(7/01/2020)

Querying empty values

You can query rows that with empty values on specific columns by using the /query suffix and not specifying any query value after the column name, following the pattern <Column Name>=.

You can find all rows with empty names inside the Name column by appending the suffix: /query?Name=

Alternatively, if the column is evaluate as a number or date, you can specify None, following the pattern <Column Name>=None.

You can find all rows with empty names inside the Created at column by appending the suffix: /query?Created%20at=None

Pagination - Limits and Offsets

# Get 2 rows, skipping the first row
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_limit=2&_offset=1'

# Get the first row
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/0'

import requests

# Get 2 rows, skipping the first row
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_limit=2&_offset=1')

# Get the first row
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/0')

// Get 2 rows, skipping the first row
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_limit=2&_offset=1"
);

// Get the first row
fetch("https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/0");

There are two special parameters called _limit and _offset, they allow you to paginate the results from the API.

Parameter	Description
_limit	Limits the number of rows returned
_offset	Skips a number of rows before start reading the sheet

Example:

Querying https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_limit=2&_offset=1 will skip the first row, and return the next 2 rows.

Shorthand Alternative

If you want to return a single row, you can use the /<Row Index> suffix.

This is similar to using the _limit=1&offset=<Row Index> queryingstring.

Example of equivalent requests:

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_limit=1&_offset=2
https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/2

You can also use: /<Row Index Start>:<Row Index End> instead of explicitly telling the _limit and _offset.

Example of equivalent requests:

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/2:4
https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf?_limit=2&_offset=2

Aggregating Data

# Get the maximum value for each column in the sheet
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/max'

# Get the minimum value for each column in the sheet
curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/min'

import requests

# Get the maximum value for each column in the sheet
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/max')

# Get the minimum value for each column in the sheet
requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/min')

// Get the maximum value for each column in the sheet
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/max"
);

// Get the minimum value for each column in the sheet
fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/min"
);

You can make aggregations to columns using the /agg/<operation> suffix.

This can be a useful way to extract data directly from our API, without having to calculate it yourself.

Operation	Description
sum	Sum the value for each column
prod	Multiply the value for each column
max	Extract the maximum value for each column
min	Extract the minimum value for each column
mean	Extract the mean/average value for each column
median	Extract the median value for each column
std	Extract the standard deviation value for each column
var	Extract the variance value for each column
any	True if any row contain a a value, False otherwise
all	True if all rows contain a a value, False otherwise

Maximum column value extraction

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/max

Max Aggregation Example

Sum all columns

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/sum

Values that cannot be summed are not displayed

Sum Aggregation Example

Average/Mean for all columns

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/agg/mean

Values that cannot be averaged are not displayed

Average/Mean Aggregation Example

Column Info

curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/info'

import requests

requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/info')

fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/info"
);

You can extract sheet column info using the /info suffix.

This will return a dictionary/object with some information from the sheet, like the example below:

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/info

Info Example

The table below explains what each extracted data means:

Data	Description
count	Number of entries of this column
mean	Mean/Average number of this column
std	Standard deviation of this column
min	Minimum number of this column
25%	25% percentile of this column
50%	50% percentile of this column
75%	75% percentile of this column
max	Maximum percentile of this column

Pivoting Data

curl 'https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/pivot/Name?_agg=max'

import requests

requests.get('https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/pivot/Name?_agg=max')

fetch(
  "https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/pivot/Name?_agg=max"
);

You can also pivot your sheet to extract deper information with the /pivot/<Column Names> suffix.

There are also the _columns and _agg querystring parameters to help you pivot data.

Parameter	Description
_columns	Will split your pivoted groups using these columns
_agg	The type of aggregation that your table will be pivoted into, these are the sames types as seen on Aggregating Data.

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/pivot/Name?_agg=max

Pivot Name, Aggregating by Max

https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/pivot/Name,Age?_agg=max

Pivot Name and Age, Aggregating by Max

CheatSheet

As a summary for all our functionalities, you can go to us Cheat Sheet

URL	Method	Data	Response
/api/sheets/<id>	GET		Get all rows from the sheet
/api/sheets/<id>?raw=1	GET		Get all rows raw data from the sheet
/api/sheets/<id>	POST	{ "email": "[email protected]" }	Add a new row with to the Sheet
/api/sheets/<id>/2	DELETE		Delete the third row from the Sheet (it is 0 indexed)
/api/sheets/<id>/2	PUT	{ "email": "[email protected]" }	Overwrite the third row from the Sheet (it is 0 indexed)
/api/sheets/<id>/2	PATCH	{ "email": "[email protected]" }	Update the third row from the Sheet (it is 0 indexed)
/api/sheets/<id>?_format=	GET		Retrieve the data in different output formats
/api/sheets/<id>/tabs/OtherTab	GET		Retrieve the rows from "OtherTab" tab
/api/sheets/<id>/Name/John	GET		Retrieve the rows that have "John" inside "Name" column
/api/sheets/<id>/2:4	GET		Retrieve only the rows 3 and 4 from the Sheet (it is 0 indexed)
/api/sheets/<id>/agg/max	GET		Retrieve the data from Sheet aggregating by the maximum of each column
/api/sheets/<id>/info	GET		Retrieve the column information from the Sheet

HTML Templates

We provide our users Sheet Best Templates, an entirely free SDK to build HTML pages, no configuration required. This is a very fast way to build websites out of a Sheet.

It allows you to write templates like this one, using the same API that Sheet Best provides.

<div data-sheet-best="https://api.sheetbest.com/sheet/cf969697-682a-40e3-bad4-d54803eeeacf">
  <div>
    <h2>{{ Name }}</h2>
    <div>
      <span>Id: <b>{{ Id }}</b></span>
      <span>Age: <b>{{ Age }}</b></span>
    </div>
  </div>
</div>
<script src="https://sbt.0soft.dev/sheet-best-templates.min.js"></script>

Sheet Best Templates

Getting Started

First of all, include our tiny script that will parse Sheet Best API data into HTML in your application.

It should ideally be located at the bottom of your <body> HTML tag.

<script src="https://sbt.0soft.dev/sheet-best-templates.min.js"></script>

After this, you will unlock all the possibilities of Sheet Best Templates enable you.

Including:

Displaying data directly from the Sheet to HTML
Inserting posted data from a <form> into a Sheet

Displaying Data

To display data you just need to add data-sheet-best attribute to any HTML tag

<div data-sheet-best="https://api.sheetbest.com/sheet/cf969697-682a-40e3-bad4-d54803eeeacf"></div>

Then you will be able to just use {{ }} notation to add the insertion points of your data into the HTML.

<div data-sheet-best="https://api.sheetbest.com/sheet/cf969697-682a-40e3-bad4-d54803eeeacf">
  <div>
    <h2>{{ Name }}</h2>
    <div>
      <span>Id: <b>{{ Id }}</b></span>
      <span>Age: <b>{{ Age }}</b></span>
    </div>
  </div>
</div>

Assuming your table looks like this:

Your HTML will be changed into this:

<div data-sheet-best="https://api.sheetbest.com/sheets/cf969697-682a-40e3-bad4-d54803eeeacf">
  <div>
    <h2>John Doe</h2>
    <div>
      <span>Id: <b>1</b></span>
      <span>Age: <b>23</b></span>
    </div>
  </div>
  <div>
    <h2>Jane Doe</h2>
    <div>
      <span>Id: <b>2</b></span>
      <span>Age: <b>34</b></span>
    </div>
  </div>
  <div>
    <h2>John Smith</h2>
    <div>
      <span>Id: <b>3</b></span>
      <span>Age: <b>45</b></span>
    </div>
  </div>
  <div>
    <h2>John Smith</h2>
    <div>
      <span>Id: <b>4</b></span>
      <span>Age: <b>56</b></span>
    </div>
  </div>
</div>

You can also use data from other tabs or filters by inserting them in the data-sheet-best attribute

<div data-sheet-best="https://api.sheetbest.com/sheet/cf969697-682a-40e3-bad4-d54803eeeacf/tabs/Admin">
  <div>
    <h2>{{ Name }}</h2>
    <div>
      <span>Id: <b>{{ Id }}</b></span>
      <span>Age: <b>{{ Age }}</b></span>
    </div>
  </div>
</div>

Inserting Data

To insert data you just need to add data-sheet-best attribute to a <form> tag, and make sure your Sheet has edit access (check the Generating Your REST API section for more details)

Every time someone submit data, it will be added to the end of the Sheet.

<form data-sheet-best="https://api.sheetbest.com/sheet/cf969697-682a-40e3-bad4-d54803eeeacf">
  <input type="text" name="Id"/>
  <input type="text" name="Name"/>
  <button type="submit">Submit</button>
</form>