Microsoft Dynamics 365 F&O and Google Sheets integration

What moves between them

The main flow runs from Microsoft Dynamics 365 F&O into Google Sheets. ml-connector reads vendors, vendor invoices, purchase orders, main accounts, posted journal entries, customers, and products over OData and writes each into its own named tab, so the spreadsheet mirrors the ERP on the cadence you choose. Where a tab holds manually maintained rows, such as a budget or a draft expense list, ml-connector can read those rows back into Dynamics as draft journal or vendor data for review before posting. Posting and the chart of accounts stay in Dynamics, since Google Sheets has no general ledger, so ml-connector never treats a sheet as the system of record for finance.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the Dynamics side it requests a Microsoft Entra ID token with the client credentials grant, scopes it to the environment host you supply as a credential, and refreshes the token on expiry since the client credentials flow needs no refresh token. On the Sheets side it refreshes the Google OAuth token or uses a shared service account, and confirms the spreadsheet has been shared with that account, the most common cause of a 403. Each OData entity maps to a tab whose row 1 header names the columns, and the connector reads the header first so a reordered column does not break the load. Because the Sheets API has no native webhook and Drive watch channels expire within 24 hours, syncs run on a schedule rather than on push. Writes use the values endpoints; the connector tracks a last-synced cursor so an append does not duplicate rows, since append is not idempotent. Dynamics returns numbers and dates, while Sheets returns every cell as a string unless an unformatted render is requested, so types are normalized on the way through. Both sides return HTTP 429 under load, so ml-connector honors Retry-After and backs off, and Dynamics financial dimensions are written as the configured display string so journal rows resolve to valid combinations.

A real-world example

A mid-sized manufacturer with about 600 employees runs Microsoft Dynamics 365 F&O for procurement and finance, but its plant managers and category buyers track open purchase orders and supplier spend in shared Google Sheets because they do not have Dynamics licenses. Before the integration, a finance analyst exported OData query results to CSV every week and pasted them into the right tabs by hand, which meant the sheets were stale by Wednesday and the figures rarely matched the ERP. With Microsoft Dynamics 365 F&O and Google Sheets connected, the purchase order, vendor, and invoice tabs refresh on a schedule against live OData, and a budget tab the buyers maintain is read back into Dynamics as draft journal data. The weekly export is gone and the teams plan from current numbers.

What you can do

• •Push Dynamics 365 F&O vendors, invoices, purchase orders, and journal entries into named Google Sheets tabs on a schedule.
• •Map each OData entity to the columns in your sheet header row so reordering a column does not break the load.
• •Read manually maintained sheet rows back into Dynamics as draft journal or vendor data for review before posting.
• •Bridge Microsoft Entra ID OAuth on the Dynamics side and Google OAuth or a shared service account on the Sheets side.
• •Run on a schedule with Retry-After backoff, cursor-based dedup, and a full audit trail on every record.

Questions

Which direction does data move between Microsoft Dynamics 365 F&O and Google Sheets?

The main flow is Dynamics 365 F&O into Google Sheets. Vendors, invoices, purchase orders, accounts, journal entries, customers, and products are read over OData and written into named tabs. Where a tab holds manually maintained rows, ml-connector can read them back into Dynamics as draft data, but posting and the chart of accounts stay in Dynamics because Sheets has no general ledger.

Does Google Sheets push changes, or does ml-connector poll for them?

ml-connector polls on a schedule you set. The Google Sheets API has no native webhook, and the Drive watch channels that can signal changes expire within 24 hours and do not auto-renew, so scheduled polling is more reliable than managing channel lifecycle. On the Dynamics side, Business Events can act as a trigger, but the connector still reads full records over OData because event payloads only carry identifiers.

How does the integration keep appended rows from duplicating?

The Google Sheets values append operation is not idempotent, so re-running it would add the same rows again. ml-connector tracks a last-synced cursor and writes only new or changed records, and it normalizes the string values Sheets returns back into the numbers and dates Dynamics expects. Both APIs return HTTP 429 under load, so the connector honors Retry-After and backs off before retrying.