Google Sheets Two-Way Sync with Dataverse

Overview

Google Sheets Two-Way Sync with Dataverse keeps a Google Sheets worksheet and a Dataverse mirror table in sync both directions, loop-safely. Sheet edits upsert into Dataverse; Dataverse (app-side) changes write back to the matching sheet row. Each row is keyed on a shared SyncId column, and a Source flag breaks the echo loop so a write in one system doesn't bounce back forever.

Why it matters: A business team prefers working in Google Sheets, while apps, automation, and reporting run on Dataverse. A loop-safe two-way sync gives both sides an authoritative, current copy of the data without double entry.

This is a two-flow solution: a scheduled poll for the Sheet to Dataverse direction (Google Sheets has no change trigger) and a Dataverse change-trigger flow for the Dataverse to Sheet direction. Both ship Off.

Use Case

A team maintains operational data (action items, inventory, a project tracker, etc.) in a Google Sheet that must stay consistent with a Dataverse-backed model-driven app. Edits made in either tool should appear in the other within a polling cycle, without manual re-keying and without infinite update loops.

Flow Architecture

Environment Variables

Connectors & Connections

Connector	API name	Actions used
Google Sheets	shared_googlesheet	GetItems GetItem PatchItem
Microsoft Dataverse	shared_commondataserviceforapps	SubscribeWebhookTrigger ListRecords CreateRecord UpdateRecord
Microsoft Teams	shared_teams	PostMessageToConversation

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Point at your data

Set flowlibs_GSheetId to your spreadsheet id and flowlibs_GSheetWorksheet to the tab. The worksheet needs at least the columns named in flowlibs_IdColumn (SyncId), flowlibs_TitleColumn (Title), and flowlibs_StatusColumn (Status).

Conflict policy

As shipped this is last-writer-wins with the sheet poll as the authority. For true bidirectional authority, add Dataverse logic that stamps flowlibs_source = dataverse on app edits so Flow B writes those edits back.

New rows

Generate a unique SyncId for fresh sheet rows. A Dataverse row created app-side with no flowlibs_gsheetrowid is not written back; extend Flow B with a Google Sheets PostItem (append) branch.

Deletes

The demo does not propagate deletions. Prefer a soft-delete pattern (a status value) over removing rows.

Field map

Add columns by extending the item/... mappings in Flow A and the setProperty(...) chain in Flow B; keep both sides in step.

Polling frequency

Adjust the Recurrence interval (15 min default) to balance freshness vs. API calls.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

@and(not(equals(variables('varRowSource'),'sheet')), not(equals(variables('varGSheetRowId'),'')))

@concat('flowlibs_syncid eq ''', coalesce(items('Apply_to_each_Sheet_Row')?[variables('varIdColumn')],''), '''')

items('Apply_to_each_Sheet_Row')?[variables('varIdColumn')]

@setProperty(setProperty(body('Get_Target_Sheet_Row'), variables('varTitleColumn'), coalesce(triggerOutputs()?['body/flowlibs_title'],'')), variables('varStatusColumn'), coalesce(triggerOutputs()?['body/flowlibs_status'],''))

@string(items('Apply_to_each_Sheet_Row')?['__PowerAppsId__'])

Customize & download