Scan Sheets¶

A scan sheet is a reconciliation aid for a transfer manifest. Each row is one package. One column holds the tag Metrc says should be in the shipment; the column next to it is left empty for a warehouse worker to fill in with a barcode scanner as they physically scan each package.

The sheet colours itself as they go, so a worker can see at a glance whether every expected tag was scanned exactly once — without leaving the spreadsheet and without anything being recalculated server-side.

If you are looking for the version built into the Chrome extension, see Scan Sheets. This page covers the API, which produces the same sheet from a single URL — so it can be scheduled, scripted, or emailed.

Asking for one¶

A scan sheet is not a separate endpoint. It is a transform applied to a transfer manifest report, because it loads exactly the same data that report already loads:

https://api.trackandtrace.tools/v2/transfers/incoming/manifest/report
  ?secretKey=YOUR_SECRET_KEY
  &licenseNumber=EX-00001
  &transform=scanSheet
  &contentType=googleSheets

That returns a 302 redirect to a new Google Sheet. Swap contentType=xlsx to download a workbook instead. Those are the only two formats — see Restrictions.

One endpoint per transfer direction:

Each sheet covers one direction. The Transfer Type column is still there — so the layout is the same everywhere and you can stack exports — but within a single sheet it is constant.

Filtering to the manifests you care about¶

Without a filter you get every active transfer for the license, which is rarely what you want at a loading dock. Filter by manifest number, repeating filter once per manifest and setting filterLogic=or:

https://api.trackandtrace.tools/v2/transfers/incoming/manifest/report
  ?secretKey=YOUR_SECRET_KEY
  &licenseNumber=EX-00001
  &transform=scanSheet
  &contentType=googleSheets
  &filter=manifestNumber__eq:0000123456
  &filter=manifestNumber__eq:0000123457
  &filterLogic=or

filterLogic=or is required here. The default is and, and no transfer has two different manifest numbers — so leaving it off returns an empty sheet rather than an error.

The filter selects transfers, not packages. Every package on a matched manifest appears on the sheet; there is no way to filter within a manifest, and a field that belongs to a package rather than a transfer (packagedDate, say) is rejected with a 400 listing the fields you can filter on.

In Python:

import requests

MANIFESTS = ["0000123456", "0000123457"]

response = requests.get(
    "https://api.trackandtrace.tools/v2/transfers/incoming/manifest/report",
    params=[
        ("secretKey", SECRET_KEY),
        ("licenseNumber", "EX-00001"),
        ("transform", "scanSheet"),
        ("contentType", "googleSheets"),
        ("filterLogic", "or"),
        *[("filter", f"manifestNumber__eq:{m}") for m in MANIFESTS],
    ],
    allow_redirects=False,
)

print(response.json()["sheetUrl"])

The body carries the same URL as the Location header, so you do not have to follow the redirect to get the link.

To download the workbook instead:

response = requests.get(
    "https://api.trackandtrace.tools/v2/transfers/incoming/manifest/report",
    params={
        "secretKey": SECRET_KEY,
        "licenseNumber": "EX-00001",
        "transform": "scanSheet",
        "contentType": "xlsx",
    },
)

with open("scan-sheet.xlsx", "wb") as handle:
    handle.write(response.content)

Or have it emailed, which has no request timeout and is the right choice for a large sheet:

&delivery=email&[email protected]

The columns¶

The colours¶

All of it is driven by COUNTIF formulas comparing the two tag columns. Nothing is computed when the sheet is generated — it updates live as tags are typed or scanned in.

An empty cell is never coloured. Every rule above is additionally conditioned on its own cell having a value, so the untouched Scanned Tags column and the blank rows below the data stay plain until something is entered. Without that, the empty cells would all count as matching each other and a brand-new sheet would arrive already highlighted.

So a fresh sheet is entirely red down the Package Tag column with a blank Scanned Tags column beside it, and a correctly received shipment ends up entirely green. Anything left red at the end is missing; anything orange arrived that should not have.

The colouring extends about 200 rows past the last package, so a tag scanned into empty space below the data still turns orange rather than sitting there unremarked.

Restrictions¶

• contentType must be xlsx or googleSheets. csv and json return a 400. Neither can carry cell formatting, so a CSV scan sheet would have the right columns and do nothing at all — which is worse than an error, because it looks like it worked.
• columns cannot be combined with transform. The scan sheet defines its own columns, and its formulas are anchored to their positions. Sending both returns a 400 rather than silently discarding your column list.
• metadata cannot be combined with transform. Metadata columns are appended to the right of a report's own — which is exactly where Scanned Tags sits.
• prependCsvMetadata=true cannot be combined with transform. The metadata preamble would push the header off the first row and break every formula. It is omitted automatically; only an explicit true is an error.
• 10,000 rows maximum. Rows are packages, not transfers. This is checked after the data loads, so it does not make the request cheaper — it exists because a scan sheet that large is a forgotten filter. Filter by manifest number.

Two notes if you are moving from the Chrome extension¶

• One sheet per direction. The extension builds a single sheet spanning incoming, outgoing and rejected transfers together. The API produces one per endpoint. To reconcile more than one direction, generate more than one sheet.
• Manifest numbers keep their leading zeros. The extension writes values in a mode that lets Google Sheets reinterpret them, so 0000123456 arrives as 123456. The API writes them as text, so they match what Metrc shows. This also means a product name beginning with = is displayed rather than executed as a formula.

Cost¶

A scan sheet is free if you have already run the manifest report. It loads the same data, so the two share a cached result for five minutes — asking for the report and then the scan sheet with the same filters costs one trip to Metrc, not two.

The underlying manifest reports do load packages and transporters for every transfer automatically, which is not cheap. See Reports and Spreadsheet Sync for what that costs and where the limits bind.