Weekly customer sanctions rescreening digest

Build me a code workflow that re-screens our entire active customer roster against OpenSanctions every Monday at 7am and emails our compliance lead a digest of the results.

Trigger: cron, every Monday at 7am in our local time zone.

Step 1. Read the roster from Google Sheets using Get Values. The sheet has one row per customer with these columns: customer ID, legal name, country, date of birth or incorporation date, last screened at, and last verdict. Treat the first row as headers. If the roster is large, page through the sheet rather than relying on a single huge range.

Step 2. Screen every row against OpenSanctions using Match Entities (Screening) on the default dataset with threshold 0.7. Batch up to 100 query entities into a single POST /match/default call so we stay within our request budget. Build each query entity as a Person when there is a date of birth and a Company when there is an incorporation date. Use the customer ID as the query key so we can correlate results back to sheet rows. Send country and the relevant date field as properties. Remember OpenSanctions expects property values as arrays of strings, and the auth scheme is the literal word ApiKey.

Step 3. For each row, decide a verdict from the top match score. Score at or above 0.85 is a hit, score at or above 0.7 is review, and no match above threshold is clear. Capture the top match score, the dataset it came from, and the OpenSanctions URL for that match when there is one.

Step 4. Write the results back to the same sheet using Batch Update Values. For every row, update last_screened_at to today, verdict to clear, review, or hit, top match score, top match dataset, and top match URL. Same row order in and out. Do not change any other columns.

Step 5. After all rows are processed, send one Gmail digest to compliance@ourcompany.com with subject "Weekly sanctions rescreening, [date]". The body should include total customers screened, count clear, count review, count hit, and an itemized list of every non-clear customer with name, verdict, and the OpenSanctions URL so the reviewer can open each one in a single click.

Notes for you. This is a deterministic code workflow, no judgment is needed beyond the threshold and score comparisons. Every node maps to a known operation: Google Sheets Get Values, OpenSanctions Match Entities (Screening), Google Sheets Batch Update Values, Gmail Send a Message. If the roster is bigger than what one Get Values range can comfortably return, chunk through the sheet in pages instead of trying to deep-paginate, and watch out for the 9500 offset cap on OpenSanctions list endpoints, though that does not apply to /match itself. Make the spreadsheet ID, sheet tab name, compliance recipient, dataset, and match threshold configurable inputs so I can change them without editing the workflow.