1Password Activity Log to Google Sheets: Nightly Retention Log

PromptCreate

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.

Example output

Nightly 1Password log updated: 412 rows added (388 audit events, 24 sign-in attempts). 3 sign-ins failed.

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?

Yes. Activity reporting is available on 1Password Business and Enterprise plans, and an owner or administrator needs to switch it on before the workflow can read anything.

Will this create duplicate rows if a run is repeated?

No. The workflow remembers exactly where it finished last time and resumes from that point, so each record gets written once even if a run is retried.

What happens if the workflow misses a night?

Nothing is lost. Because it resumes from where it stopped rather than looking at a fixed time window, the next run picks up everything that happened in the meantime.

Can this replace a security information and event management tool?

For the basics, it covers a lot of ground. You get a permanent, searchable record of who signed in and what administrators changed. It does not do alerting rules or correlation across many systems the way a full SIEM does.

What if we only have access to one of the two activity reports?

The workflow still runs. If one report is unavailable it logs the other one and notes the gap in the Slack message, rather than failing the whole run.

Related templates

Build a credential rotation checklist when someone leaves

When someone leaves, we check which shared passwords they used in their final months and post a ranked rotation list to your security channel.

+1

Agentic Task

Weekly Amazon S3 bucket security audit posted to Slack

Every Monday, check every S3 bucket for public exposure, missing encryption and weak backup settings, then get the risks ranked in Slack.

Agentic Task

Log Agorapulse social conversations to HubSpot contacts

Twice every weekday, the conversations from your social inbox land on the right HubSpot contact timelines, with a Slack recap for sales.

Agentic Task

Turn procurement portal tenders into CRM deals each morning

Every weekday at 7am, sign in to the tender portals you track, filter new notices against your bid criteria, and open a deal for the ones worth chasing.