Keep a nightly 1Password activity log in Google Sheets

Every night at 1am, keep a retention log of 1Password account activity in a Google Sheets compliance workbook, so we have searchable history without paying for a full SIEM. Build this as a deterministic code workflow: fetch, flatten, append, count, notify. No step needs judgement.

Pull the previous day's records from two 1Password Events feeds: List Audit Events and List Sign-in Attempts. Both are cursor-based POST requests that read data and change no state. On the very first run, send a ResetCursor body with a limit between 1 and 1000 (the default is 100) plus an RFC 3339 start_time covering the previous day, and an optional end_time. On every run after that, replay the saved cursor string instead of re-reading a fixed time window. Keep paging while the response reports has_more is true, replaying the cursor returned by each response.

Store the cursor for each feed between runs and resume from it on the next run. Keep one cursor per feed, since audit events and sign-in attempts page independently. The cursor is a durable checkpoint that stays valid across sessions, and resuming from it rather than from a recomputed time window is what prevents both gaps and duplicate rows. After the final page of a run, save the newest cursor for the next night.

Flatten each record into a single row: the timestamp, the actor's name, the actor's email, the action for an audit event or the failure cause for a sign-in attempt, the client used, and the IP address. Append the rows to the compliance workbook with the Google Sheets Append Values operation, keeping audit events and sign-in attempts on separate tabs.

An Events token is scoped to a subset of auditevents, itemusages and signinattempts, so if one feed returns a 401, treat that feed as unavailable for this run, log the reason, and continue with the other feed rather than failing the whole run. Leave that feed's stored cursor untouched so it resumes cleanly once the token is regranted.

Finish by posting one line with the Slack Bot Send a Message operation, stating how many rows were written and how many sign-ins failed, so the team can see the log is current. If a feed was skipped because of a 401, note that in the same line.