Use a Scheduled cloud flow → HTTP → Parse JSON → Excel Online (Business) chain. Keep the API host and response fields explicit, store the key as a protected flow value, and write source, market_session, and data_updated_at beside each rate so the workbook does not lose freshness context.
FxRates in OneDrive for Business or SharePoint.Prepare the Excel workbook
Create an .xlsx workbook in OneDrive for Business or a SharePoint document library. Add a worksheet table named FxRates with these columns: CheckedAtUtc, Base, EUR, GBP, JPY, EURSource, GBPSource, JPYSource, MarketSession, DataUpdatedAt, EUREffectiveAt, GBPEffectiveAt, and JPYEffectiveAt. Power Automate’s Excel Online (Business) connector writes rows to a named table, not an arbitrary range.
Create the scheduled cloud flow
In Power Automate, choose Create → Scheduled cloud flow. Set the recurrence to the reporting cadence and choose the correct timezone. Add an HTTP action with GET and this URL:
Keep the key out of the URL. For a production flow, use a secret environment variable or approved connection-reference pattern, and enable secure inputs/outputs on actions that carry the value. The downloadable flow recipe contains the exact action names, Parse JSON schema, and Excel mappings without pretending to be an environment-ready import package.
Parse the response before writing
Add Parse JSON with the HTTP body as its content. Require result, base, rates, sources, effective_at, market_session, and data_updated_at. A successful response has one rates object and a per-currency sources object; do not infer freshness from the flow’s run time.
Append one row to Excel
Add Excel Online (Business) → Add a row into a table. Select the workbook and FxRates, then map rates.EUR, rates.GBP, and rates.JPY to their rate columns. Map each sources entry, each effective_at entry, and the envelope’s market_session and data_updated_at as well. data_updated_at is the oldest contributing observation for compatibility; use the per-currency effective_at columns when judging an individual rate. Use utcNow() only for CheckedAtUtc; it is when the flow ran, not when the market observation occurred.
FxRates, and do not run overlapping refreshes.Handle HTTP and Excel failures
Add a failure branch that records the run link and HTTP status in an operations table or sends an internal alert. Do not append a row after Parse JSON fails, and do not replace a missing rate with zero. For 429, respect the response’s retry guidance and reduce the schedule or requested symbols before adding more retries. Set recurrence concurrency to 1 and cap retry attempts. One daily request uses about 30 API requests in a 30-day month; hourly uses 720, before retries. Multiple requested symbols stay in the same request, while Power Automate actions have their own licensing limits.
Verify licensing and connector limits
Microsoft’s Power Automate licensing FAQ says Free and Microsoft 365 plans access standard connectors, while premium connectors require the appropriate Premium or Process context. Check the HTTP action card and your tenant policy in the Power Automate designer before promising that this flow is deployable; this recipe makes no free-plan promise. The Excel Online (Business) reference documents throttling, 25 MB workbook size, and concurrency limitations. Azure Logic Apps is a separate product with its own hosting and billing model; its pricing does not determine Power Automate entitlement.