“Connect it to Excel” can mean importing a read-only copy, writing changes to another system or maintaining a two-way flow. These are different projects. Choose the direction and authority of each record before choosing a connector.
This is a decision and acceptance guide, not instructions for a specific Excel edition. No edition or connector configuration has been executed for this article. Use the English decision worksheet to separate expectations from observations.
Three possible routes
| Route | Suitable purpose | Boundary |
|---|---|---|
| Read with Power Query | Import information for calculation or reporting | Refresh and authentication still need checking |
| Managed export or import | Periodic exchange through an agreed file structure | Manual steps and ownership remain |
| Managed system integration | Reliable recurring transfers with recovery and monitoring | Requires more than a successful workbook import |
Microsoft documents the Power Query Web connector as a way to retrieve web data. Verify support, authentication and refresh behaviour for your actual Excel host and source. A successful retrieval on one laptop does not prove unattended operation elsewhere.
Start read-only; assess writing separately
Read-only retrieval leaves the source unchanged. Even then, stale or incomplete numbers can cause a bad business decision. Make failed refresh and the last successful retrieval visible.
Writing introduces duplicate records, overwritten values and partial batches. It needs a test environment, suitable external identifiers and a way to determine whether an action already happened. Two-way updates also require conflict rules: if the same field changes in two places, which system wins or who decides?
A rule from our own product: reserved is not paid
In Brengo, our own product piloted in Groningen, task status and payment status need to agree. Its REST API connects the application with external payment services. The source code maps card-payment statuses to the application's own outcomes: money available to collect differs from a completed payment. Cancellation and failure have separate outcomes too.
Copying a field called “successful” is therefore insufficient. Its meaning depends on the payment route and the available amount. A spreadsheet can display that value; the application still needs to decide which action is allowed next. Those rules belong to the system managing the task.
This is source-checked first-party product experience. It is not a customer case or an Excel test, and Brengo has no Excel connector in this example. It shows why field meaning and business rules come before choosing a read-only import.
Define refresh and credentials
Decide how current the data needs to be, where refresh runs and who acts on failure. Document the permitted technical identity, its owner, access renewal and revocation. Public API documentation is not proof your account has permission.
Do not store keys, tokens or passwords in cells, query text or a shared configuration tab. Workbooks are copied, emailed and backed up. Use the appropriate controlled credential mechanism and refer to its owner rather than copying the secret into the worksheet.
Test schema, pagination and counts
Check required fields, values, record totals against an independent source total and duplicate identifiers. Define the response to renamed, removed or unexpected fields. A query can appear to run while excluding records with a changed date field.
Count every request in a refresh, including pagination and verification calls. Compare the intended frequency with the source's actual limits. Do not infer that a frequent refresh is acceptable merely because one request succeeded.
Worked example: 150 expected, 148 received
A fictional atelier wants a daily read-only order list. The imagined source provides three pages of 50 orders plus a separate count check. These numbers and its .example address are invented for the exercise.
Two records use a renamed date field during a source change. The query imports 148 instead of the expected 150. The result is rejected: two missing orders and mixed schema versions. Correcting the query and retesting counts is required before daily use.
The example demonstrates a discrepancy; it is not a measured customer integration or a guarantee that this pagination approach suits your API.
Assign four kinds of ownership
Name the process owner for business correctness, data owner for field meanings, technical owner for access and queries, and operational owner for failed refreshes. One person may hold several roles, but none should be implicit. Record where the controlled workbook lives and who may change it.
Stop when the control boundary is missing
Stop self-build when writing has no test environment, access is not permitted, secrets would be exposed, limits are unsuitable or nobody owns failures. A successful import does not approve a flow for invoicing or stock changes.
For predictable transfers, start with administrative task automation. Test one safe API data flow before allowing write-back. For a maintained connection between systems, see API integration and business process automation.