Paginate & Filter Data

Paginating and data filter is required in many applications to display data that is relevant to users. With Sheetson API, there are many ways to narrow down massive data.

Example data

To get started, we prepare a sheet named Cities as below:

	A	B	C	D
1	name	state	country	population
2	San Francisco	CA	USA	3314000
3	Los Angeles	CA	USA	12458000
4	Washington, D.C.	null	USA	5207000
5	Tokyo	null	Japan	37400068
6	Shanghai	null	China	25582000
7	Paris	null	France	10901000
8	São Paulo	null	Brazil	21650000
9	Madrid	null	Spain	6497000
10	Toronto	null	Canada	6082000
11	Chicago	IL	USA	8864000

Note

Please prepare the same data as above in a new (or existing) spreadsheet. You will need to use your Spreadsheet ID as descibed in Getting Started in example codes.

Search data

Search data is only available in paid plans.

We can look up data in a worksheet in several ways by using where URL parameter. The value of the where parameter should be JSON encoded. To look up cities in USA:

cURLJavascript

curl "https://api.sheetson.com/v2/sheets/Cities" \
-G --data-urlencode 'where={"country":"USA}' \
--data-urlencode 'apiKey=YOUR_API_KEY' \
--data-urlencode 'spreadsheetId=YOUR_SPREADSHEET_ID' \

const fetch = require('isomorphic-fetch');
const params = {
  where: '{"country":"USA"}',
  apiKey: "YOUR_API_KEY",
  spreadsheetId: "YOUR_SPREADSHEET_ID"
}
const url = = new URL("https://api.sheetson.com/v2/sheets/Cities");
Object.keys(params).forEach(key => url.searchParams.append(key, encodeURIComponent(params[key])));
fetch(url).then(r => r.json())
  .then(result => console.log(result))

See this lis below for all supported operations in where parameter:

| Key | Operation |-----|------------ |$lt | Less Than |$lte | Less Than or Equal To |$gt | Greater Than |$gte | Greater Than or Equal To |$eq | Equal To |$neq | Not Equal To |$text| Search for specific text (not case-sensitive)

For example, to retrieve cities with population between 10,000,000 and 30,000,000:

cURLJavascript

curl "https://api.sheetson.com/v2/sheets/Cities" \
-G --data-urlencode 'where={"population": {"$gte": 10000000, "$lte": 30000000}}' \
--data-urlencode 'apiKey=YOUR_API_KEY' \
--data-urlencode 'spreadsheetId=YOUR_SPREADSHEET_ID' \

const fetch = require('isomorphic-fetch');
const params = {
  where: '{"population": {"$gte": 10000000, "$lte": 30000000}}',
  apiKey: "YOUR_API_KEY",
  spreadsheetId: "YOUR_SPREADSHEET_ID"
}
const url = = new URL("https://api.sheetson.com/v2/sheets/Cities");
Object.keys(params).forEach(key => url.searchParams.append(key, encodeURIComponent(params[key])));
fetch(url).then(r => r.json())
  .then(result => console.log(result))

Order data

Order data is only available in paid plans.

By default, rows are returned by order displayed in a worksheet. We can use the order parameter to specify a field to sort by. Prefixing with a negative sign reverses the order. In order to retrieve cities by population ascending order:

cURLJavascript

curl "https://api.sheetson.com/v2/sheets/Cities" \
-G --data-urlencode 'order=population' \
--data-urlencode 'apiKey=YOUR_API_KEY' \
--data-urlencode 'spreadsheetId=YOUR_SPREADSHEET_ID' \

const fetch = require('isomorphic-fetch');
const params = {
  order: "population",
  apiKey: "YOUR_API_KEY",
  spreadsheetId: "YOUR_SPREADSHEET_ID"
}
const url = = new URL("https://api.sheetson.com/v2/sheets/Cities");
Object.keys(params).forEach(key => url.searchParams.append(key, encodeURIComponent(params[key])));
fetch(url).then(r => r.json())
  .then(result => console.log(result))

To get cities by population in descending order:

cURLJavascript

curl "https://api.sheetson.com/v2/sheets/Cities" \
-G --data-urlencode 'order=-population' \
--data-urlencode 'apiKey=YOUR_API_KEY' \
--data-urlencode 'spreadsheetId=YOUR_SPREADSHEET_ID' \

const fetch = require('isomorphic-fetch');
const params = {
  order: "-population",
  apiKey: "YOUR_API_KEY",
  spreadsheetId: "YOUR_SPREADSHEET_ID"
}
const url = = new URL("https://api.sheetson.com/v2/sheets/Cities");
Object.keys(params).forEach(key => url.searchParams.append(key, encodeURIComponent(params[key])));
fetch(url).then(r => r.json())
  .then(result => console.log(result))

Paginate data

By default, each time we request multiple rows, the maximum number of rows to return is 24. We can also increase this upto 100 by specifying limit parameter in the URL. Combining with skip, we can paginate between set of rows.

cURLJavascript

curl "https://api.sheetson.com/v2/sheets/Cities" \
-G \
--data-urlencode 'skip=100' \
--data-urlencode 'limit=100' \
--data-urlencode 'apiKey=YOUR_API_KEY' \
--data-urlencode 'spreadsheetId=YOUR_SPREADSHEET_ID' \

const fetch = require('isomorphic-fetch');
const params = {
  skip: 100,
  limit: 100,
  apiKey: "YOUR_API_KEY",
  spreadsheetId: "YOUR_SPREADSHEET_ID"
}
const url = = new URL("https://api.sheetson.com/v2/sheets/Cities");
Object.keys(params).forEach(key => url.searchParams.append(key, encodeURIComponent(params[key])));
fetch(url).then(r => r.json())
  .then(result => console.log(result))

Tip

Along with results, we also include hasNextPage field in response data in order to let you know if there's more data after current set. This field always returns either true or false.

Restrict fields returned

To save bandwidth, we can choose to return only needed fields by using keys parameter.

cURLJavascript

curl "https://api.sheetson.com/v2/sheets/Cities" \
-G \
--data-urlencode 'keys=name,country' \
--data-urlencode 'apiKey=YOUR_API_KEY' \
--data-urlencode 'spreadsheetId=YOUR_SPREADSHEET_ID' \

const fetch = require('isomorphic-fetch');
const params = {
  keys: "name,country",
  apiKey: "YOUR_API_KEY",
  spreadsheetId: "YOUR_SPREADSHEET_ID"
}
const url = = new URL("https://api.sheetson.com/v2/sheets/Cities");
Object.keys(params).forEach(key => url.searchParams.append(key, encodeURIComponent(params[key])));
fetch(url).then(r => r.json())
  .then(result => console.log(result))