Automated weekly bank statement reconciliation in Google Sheets

Build me an agent workflow that reconciles last week's bank statements every Monday at 7am local time.

Trigger: cron, every Monday at 07:00.

Step 1. Use Google Drive 'List Files' on a designated 'Bank Statements' folder (folder ID is configurable) to find every PDF whose modifiedTime is within the last 7 days. Use a query like modifiedTime > 'YYYY-MM-DD' and '<folderId>' in parents and mimeType = 'application/pdf'. Collect the file IDs and download URLs.

Step 2. For each statement, decide whether it is a scanned image PDF or a digital text PDF. If it looks scanned, run PDF.co 'Make PDF Searchable' first to OCR it. Otherwise skip that step.

Step 3. Extract transactions. If a PDF.co Document Parser template ID is configured (make this a workflow parameter), call PDF.co 'Document Parser' with that template. If no template is configured, fall back to PDF.co 'Convert PDF to JSON with AI' and have the agent map the AI output into a normalized list of transactions with date, merchant, memo, and amount.

Step 4. For each transaction, the agent assigns a category based on merchant name and memo. Default categories: payroll, SaaS, travel, meals, marketing, refunds, transfers, taxes, and uncategorized. Make the category list configurable so users can add their own.

Step 5. Anomaly detection. The agent flags a transaction if any of these are true: it duplicates another transaction in the same week (same merchant and same amount), it is a round-number transfer above a configurable threshold (default $1000), the merchant has not appeared in any prior row of the ledger, or the amount is above a configurable single-charge limit. Each flagged transaction gets a short reason.

Step 6. Append every categorized transaction as a new row to a Google Sheets ledger using 'Append Values'. Columns: date, statement file name, merchant, memo, amount, category, flagged (yes/no), flag reason. The spreadsheet ID and sheet name are workflow parameters.

Step 7. Post a Slack summary to a configurable channel using 'Send a Message'. The message should include: number of statements processed, total transactions, totals per category for the week, and a bulleted list of flagged transactions with merchant, amount, and reason. Link the Google Sheet at the bottom.

The agent's value is the categorization judgement and anomaly detection, so let it reason over the transactions rather than hard-coding regex rules. If no new PDFs are found, post a short 'no new statements this week' message to Slack and exit cleanly.

Additional information

What does this prompt do?

Pulls every bank statement PDF that landed in a designated Google Drive folder over the last 7 days.
Reads each statement (including scanned ones) and pulls out every transaction with date, merchant, memo, and amount.
Categorizes each transaction (payroll, SaaS, travel, refunds, transfers, and so on) using the merchant name and memo line, and flags duplicates, round-number transfers, and unfamiliar vendors for review.
Appends the categorized rows to your Google Sheets ledger and posts a Slack summary with weekly totals per category and a list of flagged items for the finance lead.

What do I need to use this?

A Google account with access to the Drive folder where bank statements get saved and the Google Sheets ledger you want to update.
A PDF.co account for reading the statements (it handles both digital and scanned PDFs).
A Slack workspace and the channel where the finance lead should receive the weekly summary.

How can I customize it?

Change the schedule. Monday at 7am is the default; pick any day or time that lines up with your close cadence.
Adjust the categories. Add tags like inventory, contractor payouts, or refunds so the breakdown matches your chart of accounts.
Tune the flagging rules. Raise or lower the dollar threshold for round-number transfers, add a watchlist of vendors that always need review, or have the agent flag any single charge above a set amount.
Swap the destination. Post the summary to a different Slack channel, DM the finance lead directly, or write to a different ledger sheet per entity.

Frequently asked questions

Does this work with scanned bank statements, or only digital PDFs?

Both. If a statement is a scanned image, the agent runs it through OCR first so the text becomes readable, then extracts the transactions. Digital PDFs are read directly.

Will it work with statements from any bank?

Yes. The agent reads the text on the page rather than relying on a bank-specific template, so it handles whatever format your bank sends. If you have a fixed template you want followed, you can wire that up in PDF.co and the agent will use it.

How does the agent decide what counts as unusual?

Out of the box it flags duplicate charges, round-number transfers above a threshold, and vendors it has not seen before. You can add your own rules in plain English, like flagging anything over $10,000 or any wire transfer.

Where do the categorized transactions end up?

They get appended as new rows to a Google Sheets ledger you pick. Each row has the date, merchant, memo, amount, category, and a flag column. You can build pivot tables or charts on top of it like any other sheet.

What if the finance lead wants the summary somewhere other than Slack?

Swap the final step for email, a Notion page, or a Microsoft Teams channel. The categorization and ledger writes stay the same.