Google Sheets New Row to Dataverse Ingest

Overview

This flow brings ad-hoc Google Sheets data into the governed Microsoft data layer. On a short schedule it polls a source worksheet, validates each row, upserts valid rows into a Dataverse table (deduplicating on a key column), flags invalid rows back in the sheet, and notifies the data owner in Teams — with a run summary at the end. Why it matters: Teams collect intake data in Google Sheets, but reporting and apps live in Dataverse. Automatic, validated ingest avoids manual re-keying and keeps one trusted copy. Ships Off — going live needs only connection auth + environment-variable values.

Use Case

A team captures intake/data in a Google Sheet and needs it landed cleanly in Dataverse, with bad rows surfaced for correction and an audit trail of what was ingested.

Flow Architecture

Environment Variables

Connectors & Connections

Connector	API name	Actions used
Google Sheets	shared_googlesheet	GetItems PatchItem
Microsoft Dataverse	shared_commondataserviceforapps	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.

Configure the source

Set flowlibs_GSheetId to the spreadsheet ID and flowlibs_GSheetTab to the worksheet. The sheet needs a header row; columns are referenced by header name.

Sheet write-back columns

For invalid-row flagging, add Status and Note columns to the source worksheet.

Dedupe key

Change flowlibs_GSheetKeyColumn to whatever header uniquely identifies a row (defaults to Email).

Type coercion

Extend the validate step / record mapping to parse numbers and dates from text cells.

Quarantine tab

Instead of flagging in place, route invalid rows to a separate worksheet via PostItem.

Poll cadence

Adjust the Recurrence interval to match how quickly rows must land.

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(coalesce(items('Apply_to_each_Row')?['Name'],''),'')), not(equals(coalesce(items('Apply_to_each_Row')?[variables('varKeyColumn')],''),'')))

@concat('flowlibs_email eq ''', coalesce(items('Apply_to_each_Row')?[variables('varKeyColumn')],''), '''')

@greater(length(outputs('List_Existing_Records')?['body/value']),0)

@first(outputs('List_Existing_Records')?['body/value'])?['flowlibs_gsheetingestrecordid']

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

Customize & download