Overview
- Goal: list every environment variable in a solution, show its value, and name the cloud flows that use it
- Output:
EnvVar_Usage_Report_<Solution>_<yyyyMMdd_HHmmss>.csvand.xlsxin the Downloads folder - Why it helps:
- Check configuration before/after a solution import (Dev → Test → Prod)
- Find unused variables before cleaning up a solution
- See which flows break if you change or delete a variable
- How (one line): 2 Dataverse List rows actions (FetchXML) → loop and match → data table → Write to CSV file → Excel Save as .xlsx
- No HTTP actions, app registrations or tokens: uses the built-in Microsoft Dataverse connector actions available in desktop flows
Prerequisites
| Item | Detail |
|---|---|
| Power Automate for desktop | Installed and signed in to the target environment (v2 schema) |
| Microsoft Dataverse connection | Used by List rows from selected environment; created from PAD's Connection references dialog |
| License | Dataverse is a premium connector: desktop flows need an Attended RPA (Premium) license for premium connector actions |
| Microsoft Excel | Only for the .xlsx output (remove the last 2 actions for CSV only) |
| A solution with environment variables | Example: EnvVar JSON Merge Demo with 4 variables |
| Permissions | Read access to environmentvariabledefinition, environmentvariablevalue, solution, solutioncomponent, workflow |
Report output
- One row per environment variable, 9 columns
| Column | Source | Example |
|---|---|---|
| Environment Name | EnvironmentName variable (line 1) | Default Environment |
| Solution Name | solution.friendlyname | EnvVar JSON Merge Demo |
| Key | schemaname | demo_ConfigApp |
| Display Name | displayname | Config App |
| Type | type (formatted label) | JSON |
| Value | Current value if set, else default value | {"app":{"name":"DemoApp",...}} |
| Value Source | Which value was used | Default value / Current value / No value |
| Used By Flows | Cloud flows whose definition references the variable | Merge JSON Environment Variables |
| Flow Count | Number of those flows | 1 |
Sample output (CSV, shortened):
Environment Name,Solution Name,Key,Display Name,Type,Value,Value Source,Used By Flows,Flow Count
Default Environment,EnvVar JSON Merge Demo,demo_ConfigApp,Config App,JSON,"{""app"":{""name"":""DemoApp""}}",Default value,Merge JSON Environment Variables,1
Default Environment,EnvVar JSON Merge Demo,demo_ConfigEmail,Config Email,JSON,"{""email"":{""support"":""support@contoso.com""}}",Default value,Merge JSON Environment Variables,1
Default Environment,EnvVar JSON Merge Demo,demo_ConfigLimits,Config Limits,JSON,"{""limits"":{""maxItems"":100}}",Default value,Merge JSON Environment Variables,1
Default Environment,EnvVar JSON Merge Demo,demo_Env_not_using_key,Env_not_using_key,String,Env_not_using_value,Default value,Not used,0- JSON values are quoted and escaped, so Excel opens the file without breaking columns
Not used+0= no cloud flow references the variable (clean-up candidate)
How it works
| Dataverse table | Used for |
|---|---|
environmentvariabledefinition | Key, display name, type, default value |
environmentvariablevalue | Current value (outer join, alias cv) |
solutioncomponent | Links variables to a solution (componenttype = 380 = Environment Variable Definition) |
solution | Filter by display name or unique name; returns display name (alias sol) |
workflow | All cloud flows (category = 5) and their definitions (clientdata) |
- Set inputs: environment name, solution name, output paths with timestamp
- Query 1 (FetchXML): variables in the solution + current value + solution display name
- Query 2 (FetchXML): every cloud flow in the environment with its definition
- Loop each variable:
- Search each flow definition for
(<schemaname>), which is how Power Automate stores a reference, e.g."Config App (demo_ConfigApp)" - Collect matching flow names and a count
- Pick current value → else default value → else "No value"
- Add a row to the data table
- Search each flow definition for
- Write to CSV file (UTF-8, headers on)
- Launch Excel with the CSV (hidden) → Close and save as .xlsx
Photo 1: Flow overview: FetchXML variables, two Dataverse List rows actions, data table and loops
Photo 2: End of the flow: insert row, write to CSV file, launch Excel, close and save as .xlsx
- The solution filter uses
<filter type="or">onfriendlynameanduniquename: pass either name - Flow usage is checked across the whole environment, so you also see flows outside the solution that depend on a variable
Step-by-step build
- Fastest way: paste the full code (end of this post) into an empty flow, then link the connection
Step 1: Create the flow
- Power Automate for desktop → New flow
- Name:
EnvVar Usage Report Desktop - Leave Power Fx enabled off (code uses classic
%variable%syntax)
Step 2: Paste the actions
- Copy the code from the PAD code section
- Click the empty Main canvas → Ctrl+V
- Result: 50 actions
Photo 3: Lines 1–6 after pasting: inputs and output file paths
Step 3: Fix the two connection errors
- The Errors list shows 2 errors on the List rows from selected environment actions (the connection ID in pasted code is tenant-specific)
- For each of the two actions:
- Double-click → Connection references
- Pick your Microsoft Dataverse connection → Ok → Save
- Environment stays (Current); the table name resolves automatically
- 2 warnings remain (Dynamic output schema couldn't be retrieved): expected because the FetchXML comes from a variable; the flow runs fine
Photo 4: Expected after paste: 2 connection errors in the Errors list
[ PHOTO 5 ]
List rows from selected environment: Environment = (Current), fix via Connection references
Photo 5: List rows from selected environment: Environment = (Current), fix via Connection references
Step 4: Set your inputs
- Line 1 →
EnvironmentName(any label, e.g. Default Environment) - Line 2 →
SolutionName: display name or unique name of your solution
Step 5: Save draft, Publish, Run
- Save (= Save draft) → Publish → Run
- Check Downloads for the .csv and .xlsx files
[ PHOTO 6 ]
Save (draft) and Publish are separate buttons: Publish is what console and cloud runs use
Photo 6: Save (draft) and Publish are separate buttons: Publish is what console and cloud runs use
Gotchas found while testing
| Problem | Cause | Fix |
|---|---|---|
| Changes don't show when run from the console or a cloud flow | With version control, Save only saves a draft; console and cloud runs use the published version | Always Save then Publish |
| Report still has old columns after pasting new code | New actions pasted without deleting the old ones; both blocks ran, the old one last | Click an action → Ctrl+A → Delete → confirm 0 Actions → paste |
| "Connection with internal id ... not found" after paste | Connection ID in copied code is tenant-specific | Re-link via Connection references on both List rows actions |
Error reading cv.value or defaultvalue | Dataverse omits empty columns from the response | Check with Contains(EnvVar, 'cv.value') before reading |
| Environment display name not available | Not stored in any Dataverse table desktop flows can query; organization.name returns the internal org name | Keep it as a variable on line 1 |
| .xlsx step fails | Excel not installed | Remove the last 2 actions, keep CSV only |
Thoughts and next steps
- Desktop vs cloud: the same report works as a cloud flow (Dataverse List rows + Create CSV table + SharePoint Create file). Desktop = local file, no SharePoint/OneDrive dependency; cloud = scheduled runs and a shareable link
- Usage detection is text-based: reliable for cloud flows, but it does not detect usage in canvas apps, Copilot Studio agents, plugins or desktop flows
- Environment-wide scan is a feature: shows dependencies from flows outside the solution, which is exactly what breaks after a rename or delete
- Large environments: List rows returns up to 5,000 rows by default; add FetchXML paging for more cloud flows
- Secrets: never write Secret-type (Azure Key Vault) values to a file; mask them
- Columns worth adding next:
- Current Value and Default Value as separate columns
- Missing Value flag
- Flow status [On]/[Off]
- Is Required and Is Managed
- Report generated date/time
- Make it an input: turn
SolutionNameinto a desktop flow input variable so a cloud flow can pass it
PAD code (with comments)
- Paste into an empty desktop flow (Main)
- Lines starting with
#are comments - The connection ID is a placeholder: re-link your own Dataverse connection after pasting (Step 3)
# ===== 1. INPUTS =====
# Label for the report (desktop flows can't read the environment display name from Dataverse)
SET EnvironmentName TO $'''Default Environment'''
# Solution display name OR unique name - both work (see the OR filter below)
SET SolutionName TO $'''EnvVar JSON Merge Demo'''
# ===== 2. OUTPUT FILE PATHS (Downloads folder + timestamp) =====
System.GetEnvironmentVariable.GetEnvironmentVariable Name: $'''USERPROFILE''' Value=> UserProfile
DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: CurrentDateTime CustomFormat: $'''yyyyMMdd_HHmmss''' Result=> RunStamp
SET CsvPath TO $'''%UserProfile%\\Downloads\\EnvVar_Usage_Report_%SolutionName%_%RunStamp%.csv'''
SET XlsxPath TO $'''%UserProfile%\\Downloads\\EnvVar_Usage_Report_%SolutionName%_%RunStamp%.xlsx'''
# ===== 3. FETCHXML QUERIES =====
# Query 1: env variable definitions in the solution (componenttype 380) + current value (alias cv) + solution display name (alias sol)
SET EnvVarFetchXml TO $'''<fetch><entity name=\"environmentvariabledefinition\"><attribute name=\"schemaname\" /><attribute name=\"displayname\" /><attribute name=\"type\" /><attribute name=\"defaultvalue\" /><order attribute=\"schemaname\" /><link-entity name=\"solutioncomponent\" from=\"objectid\" to=\"environmentvariabledefinitionid\" link-type=\"inner\"><filter><condition attribute=\"componenttype\" operator=\"eq\" value=\"380\" /></filter><link-entity name=\"solution\" from=\"solutionid\" to=\"solutionid\" link-type=\"inner\" alias=\"sol\"><attribute name=\"friendlyname\" /><filter type=\"or\"><condition attribute=\"friendlyname\" operator=\"eq\" value=\"%SolutionName%\" /><condition attribute=\"uniquename\" operator=\"eq\" value=\"%SolutionName%\" /></filter></link-entity></link-entity><link-entity name=\"environmentvariablevalue\" from=\"environmentvariabledefinitionid\" to=\"environmentvariabledefinitionid\" link-type=\"outer\" alias=\"cv\"><attribute name=\"value\" /></link-entity></entity></fetch>'''
# Query 2: all cloud flows (category 5) with their definition JSON (clientdata)
SET FlowFetchXml TO $'''<fetch><entity name=\"workflow\"><attribute name=\"name\" /><attribute name=\"clientdata\" /><filter><condition attribute=\"category\" operator=\"eq\" value=\"5\" /></filter></entity></fetch>'''
# ===== 4. RUN QUERIES (Dataverse > List rows from selected environment, Environment = Current) =====
# After pasting: re-link YOUR Microsoft Dataverse connection on both actions (Connection references)
@@organization: '(Current)'
@@entityName: 'Environment Variable Definitions'
@@connectionDisplayName: 'Microsoft Dataverse'
External.InvokeCloudConnector Connection: '00000000-0000-0000-0000-000000000000' ConnectorId: '/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps' OperationId: 'ListRecordsWithOrganization' @organization: $'''current''' @entityName: $'''environmentvariabledefinitions''' @fetchXml: EnvVarFetchXml @ListRecordsWithOrganizationResponse=> EnvVarResponse @ListRecordsWithOrganizationHeaders=> EnvVarHeaders
@@organization: '(Current)'
@@entityName: 'Processes'
@@connectionDisplayName: 'Microsoft Dataverse'
External.InvokeCloudConnector Connection: '00000000-0000-0000-0000-000000000000' ConnectorId: '/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps' OperationId: 'ListRecordsWithOrganization' @organization: $'''current''' @entityName: $'''workflows''' @fetchXml: FlowFetchXml @ListRecordsWithOrganizationResponse=> FlowResponse @ListRecordsWithOrganizationHeaders=> FlowHeaders
# ===== 5. REPORT TABLE (column headers) =====
Variables.CreateNewDatatable InputTable: { ^['Environment Name', 'Solution Name', 'Key', 'Display Name', 'Type', 'Value', 'Value Source', 'Used By Flows', 'Flow Count'] } DataTable=> ReportTable
# ===== 6. ONE ROW PER ENVIRONMENT VARIABLE =====
LOOP FOREACH EnvVar IN EnvVarResponse['value']
SET UsedBy TO $''''''
SET FlowCount TO 0
# A flow definition stores each variable it uses as "Display Name (schemaname)"
LOOP FOREACH CloudFlow IN FlowResponse['value']
IF Contains(CloudFlow['clientdata'], $'''(%EnvVar['schemaname']%)''', False) THEN
IF FlowCount > 0 THEN
SET UsedBy TO $'''%UsedBy%; '''
END
SET UsedBy TO $'''%UsedBy%%CloudFlow['name']%'''
Variables.IncreaseVariable Value: FlowCount IncrementValue: 1
END
END
IF FlowCount = 0 THEN
SET UsedBy TO $'''Not used'''
END
# Empty columns are left out of the Dataverse response, so check the key exists before reading it
# Current value wins over default value
IF Contains(EnvVar, $'''cv.value''', False) THEN
SET EnvValue TO EnvVar['cv.value']
SET ValueSource TO $'''Current value'''
ELSE IF Contains(EnvVar, $'''defaultvalue''', False) THEN
SET EnvValue TO EnvVar['defaultvalue']
SET ValueSource TO $'''Default value'''
ELSE
SET EnvValue TO $''''''
SET ValueSource TO $'''No value'''
END
# Use the readable type label (JSON, String...) when Dataverse returns it, else the option number
SET EnvType TO EnvVar['type']
IF Contains(EnvVar, $'''type@OData.Community.Display.V1.FormattedValue''', False) THEN
SET EnvType TO EnvVar['type@OData.Community.Display.V1.FormattedValue']
END
# Solution display name from Dataverse (correct even when a unique name was passed in)
SET RowSolutionName TO SolutionName
IF Contains(EnvVar, $'''sol.friendlyname''', False) THEN
SET RowSolutionName TO EnvVar['sol.friendlyname']
END
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: ReportTable RowToAdd: [EnvironmentName, RowSolutionName, EnvVar['schemaname'], EnvVar['displayname'], EnvType, EnvValue, ValueSource, UsedBy, FlowCount]
END
# ===== 7. SAVE AS CSV, THEN CONVERT TO EXCEL =====
File.WriteToCSVFile.WriteCSV VariableToWrite: ReportTable CSVFile: CsvPath CsvFileEncoding: File.CSVEncoding.UTF8 IncludeColumnNames: True IfFileExists: File.IfFileExists.Overwrite ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault
# Open the CSV in a hidden Excel and save it as .xlsx (remove these 2 actions if Excel isn't installed)
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CsvPath Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.CloseExcel.CloseAndSaveAs Instance: ExcelInstance DocumentFormat: Excel.ExcelFormat.OpenXmlWorkbook DocumentPath: XlsxPath




