Microsoft Dynamics 365 Business Central and Google Sheets integration

What moves between them

Data moves in one direction, from Microsoft Dynamics 365 Business Central into Google Sheets. ml-connector polls the selected Business Central entities, typically vendors, customers, purchase invoices, sales invoices, GL accounts, and general ledger entries, and appends each new or changed record as a row in the matching spreadsheet tab. Reads run incrementally using the BC lastModifiedDateTime filter, and the export cadence is whatever you schedule, for example every 15 minutes or once nightly. Google Sheets is treated as a reporting and export target, so the connector never writes spreadsheet edits back into the ERP. The customer owns the column schema on each tab; the connector fills it.

How ml-connector handles it

ml-connector stores both credential sets encrypted and runs two separate auth flows. On the Business Central side it requests a client-credentials token from Entra ID, scoped to api.businesscentral.dynamics.com/.default, and builds the company-scoped URL from the stored environment name. On the Google side it exchanges the stored refresh token for an access token against the Google token endpoint, or uses a shared service account. Reads are incremental: the connector filters Business Central by lastModifiedDateTime greater than the last run, follows @odata.nextLink across pages, and maps each ERP field to the header names in row 1 of the target tab. Because the Sheets append call is not idempotent, the connector tracks a last-synced cursor (row count or a timestamp column) and dedupes before writing, so a retry never doubles rows. It also pads short rows because Google omits trailing empty cells. Reads use polling rather than push: Business Central webhooks expire every 3 days and carry no payload, and Google Sheets has no native webhook (only Drive watch channels that expire within 24 hours), so a schedule is the reliable path. When Business Central returns HTTP 429 or Google returns 429, the connector backs off with jitter and retries, and every record is auditable and can be replayed if a write fails.

A real-world example

A mid-market distribution company, roughly 150 employees, runs Microsoft Dynamics 365 Business Central for purchasing, inventory, and finance. The leadership team wants a weekly spend-by-vendor and open-AP view, but most managers do not have an ERP license and the finance analyst was exporting purchase invoices and vendor balances to CSV every Monday and pasting them into a shared Google spreadsheet by hand. With Business Central and Google Sheets connected, the connector refreshes the Vendors, Invoices, and GLAccounts tabs automatically each morning, appending only what changed. The managers open the same shared sheet they always used, the numbers are current, and the manual export and paste step is gone.

What you can do

• •Read Microsoft Dynamics 365 Business Central vendors, customers, invoices, GL accounts, and ledger entries and write them into named Google Sheets tabs.
• •Run incremental exports using the BC lastModifiedDateTime filter so only new or changed records are sent.
• •Map each ERP field to your spreadsheet header row and append rows on the schedule you set.
• •Bridge the Business Central Entra ID client-credentials token and the Google OAuth2 refresh token in one connection.
• •Dedupe against a last-synced cursor before appending so a retry never duplicates rows in Google Sheets.

Questions

Which direction does data move between Microsoft Dynamics 365 Business Central and Google Sheets?

Data moves one way, from Business Central into Google Sheets. The connector reads ERP entities such as vendors, invoices, and GL accounts and appends them as rows in the matching spreadsheet tab. Google Sheets is treated as a reporting and export target, so spreadsheet edits are never written back into the ERP.

Does this use webhooks or scheduled polling?

It uses scheduled polling. Business Central webhooks expire every 3 days and only signal that something changed without carrying the data, and Google Sheets has no native webhook at all, only Drive watch channels that expire within 24 hours. A schedule with incremental lastModifiedDateTime reads is the reliable approach, and you choose the cadence.

How does it avoid duplicate rows in Google Sheets?

The Google Sheets append operation is not idempotent, so re-running it would add duplicate rows. ml-connector tracks a last-synced cursor, such as the row count or a timestamp column, and dedupes before writing. It also pads short rows because Google omits trailing empty cells, which keeps the column alignment correct.