Enabling the live data URL for a sheet

The live data URL is turned off by default for every Orca Scan sheet. Before the utility can read a sheet, a user with access to that sheet must enable its live data URL in the Orca Scan web app:

1. Log in to the Orca Scan web app and open the sheet that holds the asset data you want to import.
2. Click Integrations in the top menu.
3. Find the Live Data URL integration and toggle it on. The sheet’s live data URL is then displayed, in the format https://api.orcascan.com/sheets/[sheet-id].
4. Click Save.
5. Copy the [sheet-id] portion of the URL into the SheetID option of your import configuration. Alternatively, copy the whole URL into EndpointURL and leave SheetID empty.

Repeat these steps for each sheet you want to import from. The full Orca Scan instructions, including the query parameters that the OrcaScan configuration options map to, are in the Orca Scan live data guide.

{
  "LogSizeBytes": 1000000,
  "HornbillUserIDColumn": "h_user_id",
  "HornbillNotifyUsers": [
    "user",
    "admin"
  ],
  "SourceConfig": {
    "Source": "orcascan",
    "OrcaScan": {
      "EndpointURL": "https://api.orcascan.com/sheets",
      "SheetID": "sJ0KYsnp-9b7Rl7i",
      "Columns": [],
      "SortBy": "",
      "SortOrder": "asc",
      "Limit": 0,
      "History": false,
      "From": "",
      "Deltas": false,
      "ZeroDeltaBase": false,
      "DateTimeFormat": "YYYY-MM-DD HH:mm:ss",
      "Timezone": "",
      "GPS": "city, country",
      "AdditionalParams": {},
      "Headers": {},
      "MaxRetries": 3,
      "ColumnAliases": {
        "Serial Number": "SerialNumber"
      },
      "SanitizeColumnNames": true,
      "TrimValues": true
    }
  },
  "AssetTypes": [
    {
      "AssetType": "Desktop",
      "OperationType": "Both",
      "PreserveShared": false,
      "PreserveState": false,
      "PreserveSubState": false,
      "PreserveOperationalState": false,
      "OrcaScan": {
        "Columns": [
          "Barcode",
          "Name",
          "Quantity",
          "Description",
          "Location",
          "Date"
        ]
      },
      "AdditionalFilters": [
        {
          "Field": "Name",
          "Operator": "NOTEMPTY",
          "Value": ""
        }
      ],
      "AssetIdentifier": {
        "SourceColumn": "Barcode",
        "Entity": "Asset",
        "EntityColumn": "h_asset_tag"
      }
    }
  ],
  "AssetGenericFieldMapping": {
    "h_name": "{{.Name}}",
    "h_asset_tag": "{{.Barcode}}",
    "h_description": "From Orca Scan: {{.Description}}",
    "h_location": "{{.Location}}",
    "h_notes": "Quantity: {{.Quantity}}\nLast scanned: {{.Date}}"
  },
  "AssetTypeFieldMapping": {
    "h_name": "{{.Name}}",
    "h_last_logged_on": "{{.Date}}"
  }
}

How the sheet is read

• Each row in the sheet becomes one source record. The columns of the sheet become the fields that can be used in the AssetGenericFieldMapping and AssetTypeFieldMapping templates, for example {{.Barcode}}.
• Every value in the live data feed is returned by Orca Scan as text, including numbers and dates. Use the data transformation template functions described in the Configuration article if a value needs to be converted before it is written to Hornbill.
• The live data URL returns the whole sheet in a single response. It does not page through results, so use the Columns and Limit options to keep the response to the data you need.
• Orca Scan can only filter the feed by barcode on the server. To import a subset of rows based on any other column, use the AdditionalFilters option against the asset type, as shown in the example above.

Sheet columns with spaces in their names

Orca Scan sheet columns are often given display names that contain spaces, for example Serial Number. A Go template cannot reference such a column directly as {{.Serial Number}}. You have three options:

• Set SanitizeColumnNames to true. Each column is then also made available under a template-friendly name, where any run of characters that are not letters, numbers or underscores is replaced by a single underscore. Serial Number becomes {{.Serial_Number}}.
• Add an entry to ColumnAliases. The example above makes Serial Number available as {{.SerialNumber}}.
• Use the index form in the template: {{index . \"Serial Number\"}}.

The original column name is always kept, so existing mappings are not affected by enabling either option.

Dates and locations

• DateTimeFormat asks Orca Scan to return date and time columns in the given format, using Orca Scan’s own tokens (YYYY, MM, DD, HH, mm, ss). The value YYYY-MM-DD HH:mm:ss shown in the example returns dates in the exact format that Hornbill date time fields, such as h_last_logged_on, expect, so no further conversion is needed in the mapping. If DateTimeFormat is not set, dates are returned in ISO 8601 format (2017-01-01T18:38:17Z) and must be converted with the date_conversion template function before being mapped to a date time field.
• Timezone shifts the returned dates by the given UTC offset, for example +01:00 or -10:00.
• GPS controls how GPS columns are returned. Orca Scan supports the tokens lat, lng, city, country and code (country code), combined in any order, for example city, country or lat, lng. If GPS is not set, GPS columns are returned as latitude, longitude.

Revision history

When History is set to true, Orca Scan returns every recorded change to every row rather than the current values, so the feed can contain many rows for the same barcode. The utility keys source records by the AssetIdentifier SourceColumn, so where more than one row shares the same identifier the last row returned is the one that is imported. Combine History with SortBy, SortOrder and From to control which revision that is. Revision history older than the retention period of your Orca Scan subscription is not available in the feed.

Configuration options

The OrcaScan object can be provided in two places:

• SourceConfig > OrcaScan - the default options for every asset type in the configuration file.
• AssetTypes > OrcaScan - options for one asset type. Any option set here overrides the same option from SourceConfig, which allows each asset type to read a different sheet, or a different set of columns from the same sheet. Boolean options are enabled if they are set to true at either level. AdditionalParams, Headers and ColumnAliases are merged, with the asset type entries taking precedence.