Querying Data

Created by Roanne Jane Jane Jaype, Modified on Mon, 1 Aug, 2022 at 3:35 PM by Roanne Jane Jane Jaype

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

________________

Shell

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

# Only rows with "Hire Date" less than "07-01-2021"
curl 'https://sheet.best/api/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://sheet.best/api/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://sheet.best/api/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__lt(500)&Customer Email=L*'

Python

import requests

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

# Only rows with "Hire Date" less than "07-01-2021"
requests.get('https://sheet.best/api/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://sheet.best/api/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://sheet.best/api/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__lt(500)&Customer Email=L*')

Javascript

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

// Only rows with "Hire Date" less than "07-01-2021"
fetch(
  "https://sheet.best/api/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://sheet.best/api/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://sheet.best/api/sheets/cf969697-682a-40e3-bad4-d54803eeeacf/query?Amount=__lt(500)&Customer Email=L*"
);