Keep a nightly 1Password activity log in Google Sheets
Every night this copies the day's 1Password sign-ins and admin activity into a spreadsheet, so you get searchable security history without a costly SIEM.
1Password
Google Sheets
Slack BotOperationsEngineeringData SyncNotifications & AlertsEvery 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.
Example output
Additional information
What does this prompt do?
- Runs every night and collects the previous day of 1Password account activity, covering both administrator actions and sign-in attempts.
- Writes each record as a spreadsheet row with the time, the person, what happened, the app they used, and the IP address, keeping the two kinds of activity on separate tabs.
- Picks up exactly where the previous night stopped, so nothing is missed and nothing gets logged twice.
- Posts one short Slack message with how many rows were added and how many sign-ins failed.
What do I need to use this?
- A 1Password account where an owner or administrator can turn on activity reporting.
- A Google account and a spreadsheet to use as your compliance log, with one tab for administrator activity and one for sign-in attempts.
- A Slack workspace and a channel where the nightly one-line summary should land.
How can I customize it?
- Change the run time. 1am is a common choice, but any nightly or hourly schedule works.
- Point it at a different spreadsheet, or rename the tabs to match a compliance workbook you already keep.
- Send the summary to a different channel, or adjust the columns you record, for example dropping IP address or adding the location a sign-in came from.
FAQs
Do I need a paid 1Password plan for this?
Will this create duplicate rows if a run is repeated?
What happens if the workflow misses a night?
Can this replace a security information and event management tool?
What if we only have access to one of the two activity reports?
Related templates
Every weekday, find every unpaid JobNimbus invoice, email each customer one reminder that gets firmer as it ages, and post a receivables summary to Slack.
Every weekday at 7am, your active courses are checked for missing work, silent logins, and slipping grades, with a ranked list sent to your advising channel.
Every hour, find the tickets closest to breaching, leave a nudge on each one, and post a ranked at-risk list to your support channel.
Every weekday afternoon, each student who is behind gets a warm, personal message in their Canvas inbox listing exactly what they owe.
Every Monday at 8am, see exactly which assignments have work waiting, sorted worst first, posted to Slack and logged to a spreadsheet.
Every weekday morning we compare your password vault against your HR records and suspend access for anyone who has already left.
Keep a searchable security log without the SIEM bill.
Set this up once and every 1Password sign-in and administrator change lands in your compliance spreadsheet, night after night.