Google Sheets to Dataverse Bid Tracker Sync
On a schedule, the flow reads new/updated rows from a shared Google Sheet (e.g., a partner-maintained bid tracker), upserts them into Dataverse keyed on a row ID, and emails a change summary. Writes a sync timestamp back to the sheet so external collaborators can see the last sync. Bridges a Google-based team into the Microsoft estate.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
This flow bridges a Google Sheets-based external team into the Microsoft estate. On a daily schedule it reads every row from a shared partner bid-tracker worksheet, upserts each row into a dedicated Dataverse table keyed on a stable Bid ID, writes a last-synced timestamp back to the sheet, and emails a change summary (new vs. updated counts) to operations.
Why it matters: Not every partner works in Microsoft 365. Rather than forcing a tool migration, this meets them where they are (a shared Google Sheet) while keeping the system of record in Dataverse - with change visibility on both sides.
Status: Built and verified (Flow Checker: 0 errors; the only warning is the intentional flow is off). Ships Off as a demo.
Use Case
A sales/operations team collaborates with an external partner who maintains a bid pipeline in Google Sheets. They want that data reliably mirrored into Dataverse for internal apps and reporting, without giving the partner M365 licenses, and with confirmation on the sheet side that each row was picked up.
Flow Architecture
Recurrence Poll Bid Sheet
Recurrence (Day / 1)Sync cadence - re-reads the worksheet on schedule.
Init CorrelationId
Initialize VariableMints one guid() batch ID, stamped on every row + the email for end-to-end tracing.
Init spreadsheet/worksheet/notify-email
Initialize VariableBind the three environment variables to working variables.
Init column-name variables
Initialize VariableConfigurable sheet header names (BidId, Title, Amount, Status, Partner, SyncStamp) so adopters map their columns without editing logic.
Init NewCount / UpdatedCount
Initialize Variable (Integer)Counters for the summary email.
Get Sheet Rows
Google Sheets GetItemsReads all rows; each keyed by header name + system __PowerAppsId__.
Apply to each Sheet Row
Foreach (sequential)Processes each row.
List Existing Bid
Dataverse ListRecordsLooks up an existing record where flowlibs_bidid matches this row's key.
Condition New Or Update
Condition (If)If no match create; else update (the upsert).
Create / Update Bid Record
Dataverse CreateRecord / UpdateRecordCreates or updates the bid record and tallies new/updated.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_BidSheetSpreadsheetId | String | <configure> | Google Sheets file (spreadsheet) ID of the bid tracker. |
| flowlibs_BidSheetWorksheet | String | Bids | Worksheet/tab name within the spreadsheet. |
| flowlibs_BidSyncNotifyEmail | String | alerts@yourcompany.com | Recipient of the change-summary email. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Google Sheets | shared_googlesheet | GetItems PatchItem |
| Microsoft Dataverse | shared_commondataserviceforapps | ListRecords CreateRecord UpdateRecord |
| Office 365 Outlook | shared_office365 | SendEmailV2 |
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.
- Cadence
- Change the Recurrence_Poll_Bid_Sheet frequency (default Day / 1) to match how often partners update the sheet.
- Column mapping
- Point the Init_*Column variables at your sheet's header names; no logic changes needed.
- Change detection
- Add a modified/hash column in the sheet and short-circuit rows whose value is unchanged to cut Dataverse writes.
- Validation
- Reject rows missing the Bid ID and report them rather than upserting bad data.
- Two-way sync
- Mirror Dataverse edits back to the sheet for true bidirectional flow (use the flowlibs_source flag to avoid echo loops).
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.
EXPR.01Upsert lookup filter
Finds the matching Dataverse record by Bid ID.
EXPR.02New-vs-update branch
True when no existing record was found (create branch).
EXPR.03Existing record id (update)
The id of the matched record for the update path.
EXPR.04Safe amount cast
Casts the amount cell to a number, treating blank as 0.
EXPR.05Write-back (preserves other columns)
Stamps the last-synced timestamp without blanking other columns.
EXPR.06Summary subject
Builds the change-summary email subject.
Customize & download
Generate a ready-to-import copy of this solution with your environment-variable values baked in — available on Base, Pro, or Team.
Upgrade to customize
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.