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:
- Scan barcodes into an Excel spreadsheet
- Scan barcodes into a Google spreadsheet
- Build dashboards using Google Data Studio
- Visualise barcode data using Microsoft Power BI
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 |
|---|---|---|
| .html | HTML (default when no extension provided) | text/html |
| .csv | CSV (Comma-separated values) | text/plain |
| .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 |
|---|---|---|
columns |
columns=barcode,name,data | Returns the columns listed, in the order specified |
limit |
limit=2 | Returns only specified number of records |
barcode |
barcode=5740351198754 | Returns items matching the specified barcode |
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 | 10 | -86 |
| 6344554293308 | 20 | 10 |
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 |
|---|---|---|
| YYYY | 4 digit year | 2019 |
| MM | Month in double-digit format | 03 |
| YY | 2 digit year | 19 |
| MMM | Month in 3 character format | Jan, Feb |
| DD | Day in double-digit format | 02 |
| D | Day in single-digit format | 2 |
| 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 |
|---|---|
| +11:00 | Magadan, Solomon Islands, New Caledonia |
| -09:00 | Alaska |
| +07:00 | Bangkok, Hanoi, Jakarta |
| +08:00 | Beijing, Perth, Singapore, Hong Kong |
| +12:00 | Auckland, Wellington, Fiji, Kamchatka |
| +02:00 | Kaliningrad, South Africa |
| -11:00 | Midway Island, Samoa |
| +04:30 | Kabul |
| +03:30 | Tehran |
| +06:30 | Yangon, Mandalay |
| -07:00 | Mountain Time (US & Canada) |
| +05:00 | Ekaterinburg, Islamabad, Karachi, Tashkent |
| -05:00 | Eastern Time (US & Canada), Bogota, Lima |
| +09:00 | Tokyo, Seoul, Osaka, Sapporo, Yakutsk |
| +11:30 | Norfolk Island |
| +10:00 | Eastern Australia, Guam, Vladivostok |
| +01:00 | Brussels, Copenhagen, Madrid, Paris |
| +05:45 | Kathmandu, Pokhara |
| -09:30 | Taiohae |
| +03:00 | Baghdad, Riyadh, Moscow, St. Petersburg |
| -02:00 | Mid-Atlantic |
| +12:45 | Chatham Islands |
| -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 |
| -12:00 | Eniwetok, Kwajalein |
| -10:00 | Hawaii |
| -06:00 | Central Time (US & Canada), Mexico City |
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
Setup Live Data Url
You can find the data URL on any of your sheets.
1. Go to Integrations settings
2. Activate the URL and copy the link
3. Save the updates
Next steps
What is the rate limit?
You can send a maximum of 15 requests per second, exceeding this limit will result in a HTTP 503 Service Unavailable response.
Questions about the Live Data URL?
We’re happy to help you troubleshoot any issues, chat with us live or drop us an email.