How to get data from Orca Scan in real-time

You can set up a Live Data URL for each sheet using the Web Application. A Live Data URL allows you to pull data from an Orca Scan sheet into your system using a simple HTTP GET request, for example, https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i

Works with your favourite tools

Connect Orca Scan to the systems you love without any code:

Data Format

You can change the format of the data by appending a file extension, for example, https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i .json. The available formats are:

Extension	Format	Content/Type
.csv	CSV (Comma-separated values)	text/plain
.html	HTML (default when no extension provided)	text/html
.xml	XML (Extensible Markup Language)	text/xml
.json	JSON (JavaScript Object Notation)	application/json

History Log

You can request all historical changes by appending history=true to your Live Data URL, for example, https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i ?history=true.

Notice: Revision history beyond the time limit of your subscription will not be accessible. See your plan for more details.

Changed By	Changed On	Changed Using
user@email.com	19/05/2019, 17:25:09	Chrome 74 (Mac OS)
user@email.com	08/06/2019, 14:19:04	Thomas’s iPhone
user@email.com	07/06/2019, 15:31:07	Samsung Galaxy S9

An additional row will be added for every change, along with the three columns above to help you identify who made that change.

Limit Data

You can reduce the amount of data returned by appending the following parameters to your Live Data URL:

Parameter	Example	Description
`barcode`	barcode=5740351198754	Returns items matching the specified barcode
`columns`	columns=barcode,name,data	Returns the columns listed, in the order specified
`limit`	limit=2	Returns only specified number of records
`from`	from=2019-06-12T00:00:00	Returns data changed on or after the specified date in ISO format (must be used in conjunction with history=true).

For example https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i ?barcode=5740351198754&columns=barcode,name

Sort Data

You can sort data returned by appending the following parameters to your Live Data URL:

Parameter	Example	Description
`sortby`	sortby=barcode	Sort rows by the defined column
`sortorder`	sortorder=desc	Sort rows by the defined column in either asc (ascending) or desc (descending) order.

For example https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i ?sortby=barcode&sortorder=desc

Numeric Deltas

Appending deltas=true to the Live Data URL along with history=true will add a column containing the delta for each numerical column change, for example:

https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i ?history=true&deltas=true

Barcode	Quantity	Quantity (delta)
6344554293308	96	96
6344554293308	20	10
6344554293308	10	-86

By default, the first delta value always matches the first numeric value. You can change this to 0 by appending zerodeltabase=true to the URL.

Date Formatting

Dates are returned in ISO 8601 format, you can change the format by passing a ?datetimeformat=TOKENS query string parameter.

Date formats are a combination of tokens below, for example, https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i ?datetimeformat=DD MMM YYYY

Token	Description	Example
MM	Month in double-digit format	03
YYYY	4 digit year	2019
D	Day in single-digit format	2
YY	2 digit year	19
DD	Day in double-digit format	02
MMM	Month in 3 character format	Jan, Feb
M	Month in single-digit format	3
HH	2 digit minute	02
MMMM	Full name of the month	January, February
HH	2 digit hour	03
H	1 digit hour	3
m	m digit minute	3

Time Zones

The date and times are returned in UTC (timezone 00:00) format. You can adjust the time zone by providing a timezone offset along with datetimeformat parameter, for example, https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i ?datetimeformat=DD/MM/YY HH:mm&timezone=-10:00

The available offsets are:

Offset	Region
+12:00	Auckland, Wellington, Fiji, Kamchatka
-09:00	Alaska
+07:00	Bangkok, Hanoi, Jakarta
+08:00	Beijing, Perth, Singapore, Hong Kong
+11:00	Magadan, Solomon Islands, New Caledonia
+02:00	Kaliningrad, South Africa
-11:00	Midway Island, Samoa
+04:30	Kabul
+03:30	Tehran
-07:00	Mountain Time (US & Canada)
-05:00	Eastern Time (US & Canada), Bogota, Lima
+10:00	Eastern Australia, Guam, Vladivostok
-02:00	Mid-Atlantic
+12:45	Chatham Islands
+09:00	Tokyo, Seoul, Osaka, Sapporo, Yakutsk
+11:30	Norfolk Island
+01:00	Brussels, Copenhagen, Madrid, Paris
+05:45	Kathmandu, Pokhara
+06:30	Yangon, Mandalay
+05:00	Ekaterinburg, Islamabad, Karachi, Tashkent
+03:00	Baghdad, Riyadh, Moscow, St. Petersburg
-09:30	Taiohae
-03:30	Newfoundland
+04:00	Abu Dhabi, Muscat, Baku, Tbilisi
+05:30	Bombay, Calcutta, Madras, New Delhi
-04:00	Atlantic Time (Canada), Caracas, La Paz
+09:30	Adelaide, Darwin
+13:00	Apia, Nukualofa
+10:30	Lord Howe Island
-04:30	Caracas
-01:00	Azores, Cape Verde Islands
+08:45	Eucla
-08:00	Pacific Time (US & Canada)
+06:00	Almaty, Dhaka, Colombo
+14:00	Line Islands, Tokelau
-03:00	Brazil, Buenos Aires, Georgetown
-06:00	Central Time (US & Canada), Mexico City
-12:00	Eniwetok, Kwajalein
-10:00	Hawaii

GPS Format

GPS values are returned in Decimal degrees format (latitude, longitude). You can change the format by passing a combination of the tokens below as ?gps=TOKENS query string parameter.

https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i ?gps=city, country

Token	Description
`city`	City name of the GPS location
`country`	Country name of the GPS location
`code`	2 digit country code of GPS location
`lng`	GPS longitude
`lat`	GPS latitude

Force Download

By default, data is returned inline so it can be viewed in a browser. However, some Data Warehousing / ETL tools require data imported from a URL to trigger a download. You can enable this behaviour by appending download=true to the URL:

https://api.orcascan.com/sheets/sJ0KYsnp-9b7Rl7i.xml ?download=true