At a glance
- Pick the integration pattern — API, file drop, mailbox ingestion, spreadsheet connector, or browser automation — by answering four questions in order, not by habit.
- A comparison table rates all five patterns on reliability, permissions needed, failure modes, and maintenance burden, so you can use it on its own.
- Validate every parsed field against its required type, its totals, and your own master data before it reaches the ERP, not after.
- We design around a sender changing layout as an expected event, not an edge case; flag it with a layout fingerprint instead of trusting fixed coordinates.
- A shared workbook stops scaling on version control and simultaneous edits, not row count — move it once that happens, not before.
Handoffs are where work waits
A lot of operational work doesn't fail; it waits. An invoice sits in an inbox until someone remembers to open it. A vendor's PDF statement gets forwarded, downloaded, and retyped into a spreadsheet before it reaches the ERP. A browser-only portal gets checked once a week because nobody built a feed for it. None of these need judgment — they need a hand to move data from where it landed to where it belongs.
The handoff, not the task, is where a process can quietly break down. An API call returns a result or a visible error. A mailbox attachment or a spreadsheet can look fine and still carry a layout change, a missing field, or stale data that nothing catches until the ERP rejects it, or worse, accepts it.
- An emailed PDF invoice, retyped by hand into a spreadsheet before posting.
- A forwarded Excel workbook that several people edit before anyone reconciles it.
- A browser-only vendor or bank portal with no export, checked on a schedule.
This article is a decision tool for those three cases: how to pick the right pattern, what to validate before data reaches the ERP, and when to leave a handoff alone.
The selection flow
Work down these four questions in order; each yes rules out everything below it, on purpose: an API beats a file drop, a file drop beats a shared mailbox, and a shared mailbox beats a screen.
- Is there an API?
If the system or sender can expose a REST or similar API — their ERP, their bank, their own mail platform — use it first. An API gives you a defined, versioned contract instead of a document to parse, and an error comes back immediately instead of silently.
- Is there a scheduled export or file drop?
If an API isn't available but the sender can produce a file on a schedule (SFTP, a secure file share, a nightly export), that's the next-most-reliable option: structured data on a predictable cadence, with no inbox or screen involved.
- Is there a shared mailbox, or really a shared spreadsheet?
If the data only ever arrives by email, read the shared mailbox through its provider's API rather than forwarding messages by hand. Check first whether the real handoff is actually a shared workbook attached to those emails — if so, connect to the file directly instead of parsing the message around it.
- Is the only way in a screen?
If none of the above exist, and a person today logs into a portal to read or download something, browser automation is the last resort, not the first choice. Go in accepting its higher maintenance burden; the comparison table below shows why.
Integration patterns compared
The flow above walks through four questions, with the fifth pattern — a spreadsheet connector — fitting wherever step three turns up a shared workbook. The table compares all five on what matters once you've picked one: reliability, permissions, failure modes, and upkeep.
| Pattern | Reliability | Permissions needed | Failure modes & maintenance | When it wins |
|---|---|---|---|---|
| API | High: a defined, versioned contract with explicit errors | An API key, OAuth client, or app registration scoped to one mailbox, account, or object type | A breaking change lands on the vendor's own schedule; upkeep is low — watch deprecation notices and renew credentials | The sender or system can expose an API and you can get it provisioned |
| SFTP / file drop | High: a file is either present and well-formed, or it isn't | A dedicated account or key with access limited to one directory | A late, missing, or partial file is the main risk; upkeep is low, mostly watching for a missed drop | The sender already produces, or can schedule, a structured export |
| Mailbox ingestion | Depends on the sender keeping the same subject pattern and attachment format | Delegated access to one shared mailbox; application permissions can reach any user's mailbox in a tenant[2], so prefer delegated access. Gmail lists "Read-only mail extraction, indexing, and backup" as a use case[1]. | A sender changes the subject line or attachment naming a parser keys off; upkeep is moderate as senders shift habits | There's no API or export, but the sender will keep emailing to one address |
| Spreadsheet connector | Depends entirely on the workbook's structure staying put | Read, and sometimes write, access to one file or shared drive location | A renamed tab, inserted column, or reordered row breaks the mapping silently; upkeep is moderate to high | People already maintain the data by hand in one shared workbook |
| Browser automation | Lowest: depends on a UI the vendor can change without notice | A dedicated, least-privilege login scoped to that account's role; no API-style token scopes | Page elements found by CSS or XPath selectors "can break when the DOM structure changes"[4]; upkeep is highest, since a UI update anywhere in the flow can silently break it. | There's no API, export, or shared mailbox, and the only way in is a screen |
Browser automation's limitation goes beyond selector choice. Playwright's own documentation recommends "prioritizing user-facing attributes and explicit contracts such as page.getByRole()" to make a locator less fragile[4], but even a well-built, role-based locator still depends on a vendor-controlled UI that can change on the vendor's own schedule, not yours — the reason this pattern carries the highest maintenance burden, whatever locator strategy you choose.
A connector platform such as Zapier or Power Automate and a custom build are two ways to implement any of these five patterns. We recommend a connector platform while the flow stays a handful of steps and its built-in checks are enough, and a custom build once validation needs your own reference data, version-controlled rules, or exception handling the platform makes hard to maintain. Both tools are named here only as examples of connector platforms, not as integrations either is certified for.
Parsing PDFs and spreadsheets reliably
Gmail and Microsoft 365 both expose mailbox access through an API: the Gmail API is "a RESTful API that lets you access Gmail mailboxes and send mail"[1], and Microsoft Graph "lets your app get authorized access to a user's Outlook mail data in a personal or organization account"[2]. Email's MIME standard allows multi-part message bodies[3], so a PDF can travel as its own part of the message, letting an ingestion step pull it out cleanly before a parser reads its fields and writes them into a named schema.
- Extracts fields by their label and position, not by memorizing one document's exact layout.
- Writes every extracted value into a field with its own data type, so a date is a date and an amount is a number.
- Flags a document it can't confidently read instead of guessing, and routes it to a person.
Plan for a sender to change their invoice or statement layout: we design around that as an expected event, not a rare edge case, so validate before data reaches the ERP, not after. Build the parser to find fields by their surrounding labels and position ranges rather than fixed coordinates, and compare each new document's layout fingerprint against the last one from that sender. When the fingerprint changes materially, hold the document for a person to re-map rather than guessing at a new format.
Validate before the ERP sees anything, not after it has already posted. Check that required fields are present, that amounts and dates parse into the right type, that line items reconcile with the stated total, and that the record matches an expected vendor, account, or customer in your own master data.
- Required fields are present and in a plausible range: no negative quantities, no invoice dates in the future.
- Line items sum to the stated total, within a stated tolerance.
- The vendor, account, or customer code matches an existing master-data record, not a close guess.
- Nothing posts to the ERP without passing every check; a failed check routes to a person with the reason attached, not a silent default.
Permissions and security for each pattern
Permissions follow the pattern you picked. With delegated or application mail permissions, Microsoft Graph lets an app access the mail data of the signed-in user or any user in a tenant[2], and it supports shared and primary mailboxes. The Gmail API lists "Read-only mail extraction, indexing, and backup" among its common use cases[1], so a read-only integration is a documented pattern, not a workaround. Browser automation has no API-level scopes: use a dedicated, least-privilege bot account instead of a person's login.
- Use a service account, app registration, or a dedicated least-privilege bot login for every pattern — never a named person's own login.
- Grant the narrowest permission the platform allows for the pattern: one mailbox, one directory, or one workbook, not the whole tenant or drive.
- Record every credential issued and a revocation date before go-live, the same as any other privileged access grant.
- Review access on a schedule, especially for a shared mailbox or a browser-automation login that's easy to forget exists.
When to leave the handoff manual
Not every handoff is worth automating, and some are worth automating later, once a sender's format stabilizes or the volume justifies the build. Three signals point the other way.
- The volume is low enough that a person doing it by hand costs less than building and keeping up a parser or script.
- The sender's format changes often enough that validation and re-mapping would run every month, not every year.
- The decision inside the handoff genuinely needs judgment — an exception, a dispute, an unusual approval — not just data entry.
A shared workbook stops scaling once more than one person edits it at the same time, once a formula or macro is the only record of a calculation, or once nobody can say which version fed the last posting. None of that is about row count: a workbook with one editor can stay reliable for a long time, while one with several editors and no version control degrades much faster. Move to an API, a file drop, or a validated spreadsheet connector once version history matters more than convenience.
Frequently asked questions
How do I auto-extract data from emailed PDFs?
Read the mailbox through its provider's API rather than forwarding messages by hand — Gmail and Microsoft Graph both support authorized mailbox access. MIME lets a PDF travel as its own part of the message, so an ingestion step can pull it out for a parser that reads fields by label and position, not fixed coordinates.
What happens when a sender changes layout?
We design around a sender changing layout as an expected event, not a rare edge case. A parser that finds fields by their labels and position ranges, with a layout fingerprint compared between runs, flags the change and holds the document for a person instead of silently extracting the wrong value into a field that still looks filled in.
How do I validate parsed data before the ERP?
Check that required fields exist and parse into the right type, that line items reconcile with the stated total, and that the vendor, account, or customer matches an existing master-data record. Anything that fails a check routes to a review queue with the reason attached; nothing posts to the ERP on a guess or a silent default.
Zapier, Power Automate, or custom?
A connector platform and a custom build are two ways to implement any of these five patterns. We recommend a connector platform while the flow stays a handful of steps and its built-in checks are enough, and a custom build once validation needs your own reference data, version-controlled rules, or exception handling the platform makes hard to maintain.
When does Excel stop scaling?
Not at a row count. A shared workbook stops scaling once more than one person edits it at once, once a formula or macro is the only record of a calculation, or once no one can say which version fed the last posting. Move to an API or a validated spreadsheet connector once version history matters more than convenience.
Related reading
- For the broader integration options once a legacy system can't be replaced outright, see legacy finance workflow automation.
- Automating legacy systems without migration covers the same connect-without-replacing problem at the system level, for the data layer these handoffs eventually feed.
- A worked example of a recurring mailbox-and-spreadsheet handoff is treasury rate sheet automation.
Sources
- Google, Overview | Gmail API (accessed 2026-09-30)
- Microsoft, Use the Outlook mail REST API - Microsoft Graph v1.0 (accessed 2026-09-30)
- IETF (RFC Editor), RFC 2045: Multipurpose Internet Mail Extensions (MIME) Part One (accessed 2026-09-30)
- Playwright (Microsoft), Locators (accessed 2026-09-30)
More in Legacy integration
AI Agents vs. RPA in Banking Operations: A Workflow Decision Table
A vendor-neutral decision table for bank and credit union operations leaders: which core-banking workflows fit RPA, an AI agent, or both together.
Community BanksEDI vs. AI Order Entry: Which Customer Orders Belong in Each Lane
EDI vs. AI order entry compared: a routing matrix for which customer orders go to EDI, AI entry, or a portal, plus onboarding cost and acknowledgments.
ManufacturingRPA vs. AI Agents for Order Processing: A Decision Table for ERP Owners
RPA vs. AI agents for order processing, compared channel by channel: a decision table by input variability, system access, and exception rate for ERP owners.
Manufacturing