Monday, June 22, 2026

Send Emails via Microsoft Graph API Using PowerShell and App Registration

Send Emails via Microsoft Graph API Using PowerShell and App Registration

Microsoft Graph API is the unified gateway to Microsoft 365 data and services. One of the most common automation scenarios is sending emails programmatically — without relying on a logged-in user, Outlook, or SMTP. In this post, we'll walk through how to register an Azure AD app, grant it the right permissions, and use a single end-to-end PowerShell script to send emails and query SharePoint via Graph API using client credentials (app-only auth).


Why Use Graph API for Sending Emails?

  • No user sign-in required — works great for background jobs, bots, and automation flows
  • Works from anywhere — PowerShell scripts, Power Automate Desktop, Azure Functions, etc.
  • Scalable and auditable — full control over sending identity and logging

Step 1: Register an App in Microsoft Entra ID (Azure AD)

Before writing any code, you need an app registration that represents your script or automation.

  1. Go to https://portal.azure.com
  2. Navigate to Microsoft Entra ID → App registrations → New registration
  3. Give it a meaningful name (e.g., GraphAPI-MailSender)
  4. Leave Redirect URI blank — not needed for client credentials flow
  5. Click Register

Once created, note down:

  • Application (client) ID → used as $clientId in the script
  • Directory (tenant) ID → used in the token endpoint URL

Step 2: Create a Client Secret

  1. Go to Certificates & secrets → New client secret
  2. Add a description and set an expiry (e.g., 12 months)
  3. Copy the Value immediately — it won't be shown again
  4. Store it securely (Azure Key Vault is recommended for production)

Step 3: Grant API Permissions

Your app needs the following Application permissions (not Delegated):

Permission Purpose
Mail.Send Send email as any mailbox in the tenant
Sites.Read.All Read SharePoint site data

Steps:

  1. Go to API permissions → Add a permission → Microsoft Graph → Application permissions
  2. Add Mail.Send and Sites.Read.All
  3. Click Grant admin consent — required for application permissions

⚠️ Mail.Send as an application permission allows sending mail as any user in the tenant. Use application access policies to restrict it to specific mailboxes in production.


Step 4: Full PowerShell Script

The script below handles everything in sequence:

  1. Acquires a token using client credentials
  2. Sends an email via Graph API
  3. Reuses the token to query the SharePoint root site
# ============================================================
# Microsoft Graph API — Send Email + Query SharePoint Root Site
# Using App-Only (Client Credentials) Authentication
# ============================================================

# ── Configuration ──────────────────────────────────────────
$clientId     = "<YOUR_CLIENT_ID>"       # Application (client) ID
$clientSecret = "<YOUR_CLIENT_SECRET>"   # Client secret value
$tenantId     = "<YOUR_TENANT_ID>"       # Directory (tenant) ID
$senderUPN    = "sender@yourdomain.com"  # Mailbox used to send email
$recipientUPN = "recipient@yourdomain.com" # Target recipient

# ── Part 1: Acquire Access Token ────────────────────────────
Write-Output "Acquiring access token..."

$tokenBody = @{
    client_id     = $clientId
    client_secret = $clientSecret
    scope         = "https://graph.microsoft.com/.default"
    grant_type    = "client_credentials"
}

$tokenResponse = Invoke-RestMethod `
    -Uri    "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" `
    -Method POST `
    -Body   $tokenBody

$token = $tokenResponse.access_token
Write-Output "Token acquired successfully."

# ── Part 2: Send Email via Graph API ────────────────────────
Write-Output "Sending email..."

$mailPayload = @{
    message = @{
        subject = "Test Email from Graph API"
        body    = @{
            contentType = "Text"
            content     = "This is a test email sent via Microsoft Graph API using PowerShell with app-only authentication."
        }
        toRecipients = @(
            @{
                emailAddress = @{
                    address = $recipientUPN
                }
            }
        )
    }
} | ConvertTo-Json -Depth 10

$headers = @{
    Authorization  = "Bearer $token"
    "Content-Type" = "application/json"
}

try {
    Invoke-RestMethod `
        -Uri         "https://graph.microsoft.com/v1.0/users/$senderUPN/sendMail" `
        -Method      POST `
        -Headers     $headers `
        -Body        $mailPayload `
        -ContentType "application/json"

    Write-Output "✅ Email sent successfully to $recipientUPN"
}
catch {
    Write-Error "❌ Failed to send email: $_"
}

# ── Part 3: Query SharePoint Root Site ──────────────────────
Write-Output "`nQuerying SharePoint root site..."

try {
    $rootSite = Invoke-RestMethod `
        -Uri     "https://graph.microsoft.com/v1.0/sites/root" `
        -Method  GET `
        -Headers $headers

    Write-Output "✅ SharePoint root site retrieved:"
    Write-Output ($rootSite | ConvertTo-Json -Depth 3)
}
catch {
    Write-Error "❌ Failed to query SharePoint root site: $_"
}

How to Run the Script

  1. Open PowerShell (or Windows PowerShell ISE / VS Code)
  2. Replace the four placeholder values at the top of the script:
    • <YOUR_CLIENT_ID>
    • <YOUR_CLIENT_SECRET>
    • <YOUR_TENANT_ID>
    • sender@yourdomain.com and recipient@yourdomain.com
  3. Save as Send-GraphEmail.ps1
  4. Run:
.\Send-GraphEmail.ps1

Expected Output

Acquiring access token...
Token acquired successfully.
Sending email...
✅ Email sent successfully to recipient@yourdomain.com

Querying SharePoint root site...
✅ SharePoint root site retrieved:
{
  "id": "yourtenant.sharepoint.com,xxxxxxxx-...",
  "displayName": "Communication site",
  "name": "root",
  "webUrl": "https://yourtenant.sharepoint.com"
}

Script Breakdown

Section What it does
Configuration block Centralises all credentials and addresses at the top — easy to maintain
Token acquisition Posts to the OAuth 2.0 token endpoint using client credentials
Email sending Constructs the mail JSON payload and calls /users/{sender}/sendMail
SharePoint query Reuses the same token to call /sites/root — no second token needed
try/catch blocks Catches errors per operation so one failure doesn't stop the rest

Security Best Practices

Practice Recommendation
Never hardcode secrets Move $clientSecret to Azure Key Vault or environment variables
Rotate secrets regularly Set reminders before expiry; automate via Key Vault rotation
Restrict Mail.Send scope Use application access policies to limit to specific mailboxes
Prefer certificates Certificate-based auth (New-SelfSignedCertificate) is more secure than secrets
Audit permissions Review app registrations and permissions in Entra ID quarterly

Summary

In this post, we covered:

  • Registering an app in Microsoft Entra ID and creating a client secret
  • Granting Mail.Send and Sites.Read.All application permissions with admin consent
  • A complete PowerShell script that acquires a token, sends an email, and queries SharePoint — all using app-only auth
  • Error handling with try/catch per operation
  • Security recommendations for production use

This pattern is perfect for Power Automate Desktop flows, scheduled PowerShell jobs, or any scenario where no interactive user is present. The same token works across multiple Graph API endpoints — making it efficient to chain operations in a single script.


Tags: MicrosoftGraph, PowerShell, AzureAD, AppRegistration, Microsoft365, EmailAutomation, GraphAPI, EntraID, PowerAutomate, SharePoint


Part 2: End-to-End in Power Automate Desktop (PAD)

The same logic — get a token, send an email, query SharePoint — can run entirely inside a Power Automate Desktop flow without any external PowerShell script. PAD has built-in HTTP actions and a Run PowerShell script action, giving you two clean approaches.


Approach A: Using the "Run PowerShell Script" Action (Quickest Way)

This embeds the entire PowerShell script directly inside a PAD flow. Best when you already have the script and just want to automate it.

PAD Flow Structure

Flow: Send Email via Graph API
│
├── [1] Set Variable — clientId
├── [2] Set Variable — clientSecret
├── [3] Set Variable — tenantId
├── [4] Set Variable — senderUPN
├── [5] Set Variable — recipientUPN
├── [6] Run PowerShell Script
│       └── Script: (full script using %clientId%, %clientSecret%, etc.)
│       └── Output: PowershellOutput
├── [7] IF PowershellOutput contains "successfully"
│       └── Display Message — "Flow completed successfully"
│   ELSE
│       └── Display Message — "Flow encountered an error"
└── [8] (Optional) Write output to text file or log

Step-by-Step in PAD Designer

Step 1 — Set your variables

Add one Set variable action for each credential. Using variables (rather than hardcoding) keeps the script clean and makes future updates easy.

Variable Name Value
clientId Your Application (client) ID
clientSecret Your client secret value
tenantId Your Directory (tenant) ID
senderUPN sender@yourdomain.com
recipientUPN recipient@yourdomain.com

In PAD, go to Actions panel → Variables → Set variable, set Name and Value for each.

Step 2 — Add "Run PowerShell Script" action

Search for Run PowerShell script in the Actions panel (under Scripting). Paste the script below into the script body. PAD variables are referenced using %variableName% syntax inside the script block.

# ============================================================
# Graph API — Send Email + Query SharePoint Root Site
# Running inside Power Automate Desktop
# ============================================================

$clientId     = "%clientId%"
$clientSecret = "%clientSecret%"
$tenantId     = "%tenantId%"
$senderUPN    = "%senderUPN%"
$recipientUPN = "%recipientUPN%"

# ── Part 1: Acquire Token ────────────────────────────────────
$tokenBody = @{
    client_id     = $clientId
    client_secret = $clientSecret
    scope         = "https://graph.microsoft.com/.default"
    grant_type    = "client_credentials"
}

$tokenResponse = Invoke-RestMethod `
    -Uri    "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" `
    -Method POST `
    -Body   $tokenBody

$token = $tokenResponse.access_token

# ── Part 2: Send Email ───────────────────────────────────────
$mailPayload = @{
    message = @{
        subject = "Test Email from Graph API via PAD"
        body    = @{
            contentType = "Text"
            content     = "This email was sent via Microsoft Graph API running inside a Power Automate Desktop flow."
        }
        toRecipients = @(
            @{ emailAddress = @{ address = $recipientUPN } }
        )
    }
} | ConvertTo-Json -Depth 10

$headers = @{
    Authorization  = "Bearer $token"
    "Content-Type" = "application/json"
}

try {
    Invoke-RestMethod `
        -Uri         "https://graph.microsoft.com/v1.0/users/$senderUPN/sendMail" `
        -Method      POST `
        -Headers     $headers `
        -Body        $mailPayload `
        -ContentType "application/json"

    Write-Output "Email sent successfully to $recipientUPN"
}
catch {
    Write-Output "ERROR sending email: $_"
}

# ── Part 3: Query SharePoint Root Site ──────────────────────
try {
    $rootSite = Invoke-RestMethod `
        -Uri     "https://graph.microsoft.com/v1.0/sites/root" `
        -Method  GET `
        -Headers $headers

    Write-Output "SharePoint root site: $($rootSite.webUrl)"
}
catch {
    Write-Output "ERROR querying SharePoint: $_"
}

In the action settings:

  • Script to run → paste the script above
  • PowerShell script output → save to variable PowershellOutput
  • Script error output → save to variable ScriptError

Step 3 — Handle output with an IF condition

Add an If action:

  • First operand: %PowershellOutput%
  • Operator: Contains
  • Second operand: successfully

Inside the If block → add Display message: "✅ Email sent and SharePoint queried successfully"
Inside the Else block → add Display message: "❌ Flow error — check ScriptError variable"

Step 4 — (Optional) Log output to a file

Add a Write text to file action after the If block:

  • File path: C:\PAD-Logs\GraphAPI-Run-%CurrentDateTime%.txt
  • Text to write: %PowershellOutput%%NewLine%%ScriptError%
  • If file exists: Append

Approach B: Using PAD HTTP Actions (No PowerShell Required)

PAD has native HTTP actions under Web → Invoke web service that can call REST APIs directly — no PowerShell needed. This is the cleanest approach for PAD-native flows.

PAD Flow Structure

Flow: Send Email via Graph API (HTTP Native)
│
├── [1]  Set Variable — clientId
├── [2]  Set Variable — clientSecret  
├── [3]  Set Variable — tenantId
├── [4]  Set Variable — senderUPN
├── [5]  Set Variable — recipientUPN
│
├── [6]  Invoke web service — POST token endpoint
│        └── Saves response → TokenResponse (JSON)
│
├── [7]  Get JSON key — extract access_token
│        └── Saves → AccessToken
│
├── [8]  Invoke web service — POST sendMail
│        └── Uses Bearer %AccessToken% in header
│        └── Saves response → MailResult
│
├── [9]  Invoke web service — GET sites/root
│        └── Uses Bearer %AccessToken% in header
│        └── Saves response → SharePointResult
│
└── [10] Display message — show results

Step-by-Step: HTTP Actions in PAD

Step 1 — Set variables (same as Approach A)

Step 2 — Get Access Token (Invoke web service)

Action: Web → Invoke web service

Field Value
URL https://login.microsoftonline.com/%tenantId%/oauth2/v2.0/token
Method POST
Accept application/json
Content type application/x-www-form-urlencoded
Custom headers (leave blank)
Request body client_id=%clientId%&client_secret=%clientSecret%&scope=https://graph.microsoft.com/.default&grant_type=client_credentials
Save response into TokenResponse

Step 3 — Extract the access token

Action: Variables → Get JSON key value
(or use Convert JSON to custom object then dot-notation)

  • JSON: %TokenResponse%
  • Key: access_token
  • Save into: AccessToken

Alternatively, use a Run PowerShell script just for this one-liner:

$json = '%TokenResponse%' | ConvertFrom-Json
Write-Output $json.access_token

Save output into AccessToken.

Step 4 — Send Email (Invoke web service)

Action: Web → Invoke web service

Field Value
URL https://graph.microsoft.com/v1.0/users/%senderUPN%/sendMail
Method POST
Accept application/json
Content type application/json
Custom headers Authorization: Bearer %AccessToken%
Request body (see JSON below)
Save response into MailResult

Request body (paste as-is, update addresses via variables):

{
  "message": {
    "subject": "Test Email from PAD via Graph API",
    "body": {
      "contentType": "Text",
      "content": "This email was sent using Power Automate Desktop with Microsoft Graph API HTTP actions."
    },
    "toRecipients": [
      {
        "emailAddress": {
          "address": "%recipientUPN%"
        }
      }
    ]
  }
}

Step 5 — Query SharePoint Root Site (Invoke web service)

Action: Web → Invoke web service

Field Value
URL https://graph.microsoft.com/v1.0/sites/root
Method GET
Accept application/json
Custom headers Authorization: Bearer %AccessToken%
Save response into SharePointResult

Step 6 — Display results

Action: Message boxes → Display message
Message: SharePoint site: %SharePointResult%


Approach Comparison

Approach A (Run PowerShell) Approach B (HTTP Actions)
Complexity Low — paste and run Medium — multiple actions
PAD native No (calls PowerShell) Yes (pure PAD)
Debugging Via script output variable Per-action response variables
Best for Existing PowerShell scripts New PAD-native flows
Token parsing Built into the script Needs extra step in PAD
Error handling try/catch in script On block error / IF conditions

Recommendation: Use Approach A if you already have the PowerShell script. Use Approach B if you want a fully PAD-native flow with no external dependencies.


Tips for PAD Flows with Graph API

  • Store secrets in PAD Input variables marked as Sensitive — they are masked in logs and not stored in plain text in the flow definition
  • Add On block error handlers around each Invoke web service action to gracefully catch HTTP failures (401 Unauthorized, 403 Forbidden, etc.)
  • Token expiry: The client credentials token is valid for 1 hour. For long-running flows, re-acquire the token mid-flow if needed
  • Test in PAD debugger using the step-through (F10) to inspect each variable value before running end-to-end

Full Flow Summary

App Registration (Entra ID)
        │
        ▼
Client Credentials → OAuth 2.0 Token Endpoint
        │
        ▼
Access Token (Bearer)
        │
        ├──▶ POST /users/{sender}/sendMail   → Email delivered ✅
        │
        └──▶ GET  /sites/root               → SharePoint data ✅

All three entry points — standalone PowerShell, PAD via PowerShell action, and PAD via HTTP actions — use the exact same app registration, permissions, and token. The only difference is where and how the script runs.


Tags: MicrosoftGraph, PowerShell, PowerAutomateDesktop, PAD, AzureAD, AppRegistration, Microsoft365, EmailAutomation, GraphAPI, EntraID, SharePoint, RPA

test

 $body = @{

    client_id = "0b80c03e-de45-43c7-be0a-1e1adc86d792"
    client_secret = "fmt8Q~4nG1JrM50nipPp0YMdBpr.RXnAWagYsbRJ"
    scope = "https://graph.microsoft.com/.default"
    grant_type = "client_credentials"
}
$tokenResponse = Invoke-RestMethod -Uri "https://login.microsoftonline.com/bb55f134-02f1-478e-bc6a-82bf54373c6d/oauth2/v2.0/token" -Method POST -Body $body
$token = $tokenResponse.access_token

$mailBody = @{
    message = @{
        subject = "Test Email from GraphAPI_Flow"
        body = @{
            contentType = "Text"
            content = "This is a test email sent via Microsoft Graph API from Power Automate Desktop."
        }
        toRecipients = @(
            @{ emailAddress = @{ address = "sreekanth@vkn2k.onmicrosoft.com" } }
        )
    }
} | ConvertTo-Json -Depth 10

$headers = @{
    Authorization = "Bearer $token"
    "Content-Type" = "application/json"
}

$sendResult = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/users/sreekanth@vkn2k.onmicrosoft.com/sendMail" -Method POST -Headers $headers -Body $mailBody -ContentType "application/json"
Write-Output "Email sent successfully"

Wednesday, June 17, 2026

Power Automate Desktop (PAD)

Power Automate Desktop (PAD)

A complete learning reference for Power Automate Desktop, covering concepts from beginner to expert level, plus 100 real-world scenarios with practical answers.


Part A — Concept Questions

Beginner (Q1–Q30)

# Question Short Answer
1 What is Power Automate Desktop (PAD)? A low-code RPA tool from Microsoft used to automate repetitive desktop and web tasks by recording or building flows visually.
2 What is RPA? Robotic Process Automation — software "robots" that mimic human actions on a UI to automate rule-based tasks.
3 Difference between Power Automate (cloud) and PAD? Cloud flows run on triggers/connectors in the cloud; PAD runs on the desktop to automate UI, files, and legacy apps.
4 What is a desktop flow? A sequence of actions built in PAD that automates a task on a Windows machine.
5 What are actions in PAD? Pre-built building blocks (like "Click", "Read from Excel") that you drag into the flow designer.
6 What is the Actions pane? The left-hand panel listing all available actions grouped by category.
7 What is a variable in PAD? A named container that stores data (text, number, list, datatable, object) used during a flow.
8 How do you create a variable? Use "Set variable" action, or variables are auto-produced as outputs of other actions.
9 What variable data types does PAD support? Text, Numeric, Boolean, List, Datatable, Datarow, Custom object, Datetime, and others.
10 What is the percent notation %variable%? The syntax PAD uses to reference or evaluate a variable or expression inside a field.
11 How do you run a flow in PAD? Click the Run button in the flow designer or trigger it from the console.
12 What is the PAD console? The home screen listing all your flows, where you can run, edit, or schedule them.
13 What is the recorder in PAD? A feature that captures your mouse and keyboard actions and converts them into flow steps.
14 Can PAD automate web browsers? Yes, via browser automation actions using extensions for Edge, Chrome, and Firefox.
15 What is a UI element? A captured reference to an on-screen control (button, field, link) that PAD interacts with.
16 Where are UI elements stored? In the UI elements repository of the specific flow.
17 How do you add a comment in a flow? Use the "Comment" action to document logic for readability.
18 What is the "Send keys" action? Sends keystrokes to the active window to simulate typing or shortcuts.
19 What is the "Wait" action? Pauses the flow for a fixed number of seconds.
20 How do you display a message to a user? Use the "Display message" action to show a dialog box.
21 What is "Input dialog" used for? To prompt the user to enter a value during flow execution.
22 How do you read data from Excel in PAD? Launch Excel, then use "Read from Excel worksheet" action.
23 How do you write to Excel? Use "Write to Excel worksheet" action specifying cell or column/row.
24 What is a Datatable? A variable type that holds rows and columns, similar to a table or grid.
25 What is a Datarow? A single row from a datatable.
26 How do you launch an application? Use "Run application" action with the executable path.
27 What file actions does PAD offer? Copy, move, delete, rename files; read/write text files; get file info.
28 How do you take a screenshot in a flow? Use the "Take screenshot" action.
29 Difference between Run and Debug? Run executes fully; Debug lets you step through with breakpoints and inspect variables.
30 Is PAD free? A free version is available for Windows 11/10 (attended); paid plans unlock cloud-connected and unattended automation.

Intermediate (Q31–Q60)

# Question Short Answer
31 What is a conditional action? "If", "Else if", "Else" actions that branch logic based on conditions.
32 What is a "Switch" action? Evaluates one value against multiple cases, like a multi-branch if.
33 What loops does PAD support? For each, Loop (fixed count), Loop condition (while), and the loop control actions.
34 Difference between "Loop" and "For each"? "Loop" runs a set number of times; "For each" iterates over items in a list or datatable.
35 What does "Exit loop" do? Breaks out of the current loop immediately.
36 What does "Next loop" do? Skips to the next iteration of the loop.
37 How do you handle errors on a single action? Open the action's "On error" tab to set retry, continue, or go-to behavior.
38 What is a "Block error" / "On block error"? A way to wrap multiple actions and define error handling for the whole block.
39 What is a subflow? A reusable group of actions within a flow that can be called like a function.
40 How do you call a subflow? Use the "Run subflow" action.
41 Why use subflows? To organize logic, avoid repetition, and improve maintainability.
42 What are input and output variables? Variables marked to receive data into a flow or return data out, enabling parameter passing.
43 How does PAD pass data to a cloud flow? Through input/output variables when the desktop flow is invoked by a cloud flow.
44 Difference between attended and unattended automation? Attended runs with a logged-in user present; unattended runs without supervision, often scheduled.
45 How do you schedule a desktop flow? Trigger it from a cloud flow using a schedule, since PAD itself has no native scheduler.
46 What is image-based automation? Locating and clicking UI by matching screenshots instead of structured UI elements.
47 When would you use image-based clicking? When stable UI selectors aren't available (e.g., Citrix, virtual desktops, images).
48 What is OCR in PAD? Optical Character Recognition to extract text from images or screens.
49 Which OCR engines does PAD support? Windows OCR engine and Tesseract.
50 How do you extract data from a web page? Use "Extract data from web page" to capture tables, lists, or specific elements.
51 Difference between "Populate text field" and "Send keys"? Populate sets a field's value directly via the UI element; Send keys simulates raw keystrokes.
52 How do you handle pop-ups or dialogs? Detect them with UI elements/window actions and click or dismiss accordingly.
53 How do you convert text to a number? Use "Convert text to number" action or expression functions.
54 How do you manipulate text in PAD? Actions like trim, replace, split, pad, get subtext, and change case.
55 What is the "Get subtext" action? Extracts a portion of a string by position and length.
56 How do you split a string into a list? Use "Split text" with a delimiter to produce a list.
57 How do you parse a date? Use "Convert text to datetime" with a format specifier.
58 How do you handle CSV files? Read with "Read from CSV file" and write with "Write to CSV file" actions.
59 What is a custom object / JSON in PAD? A structured key-value object; PAD can convert JSON to/from custom objects.
60 How do you call a web API in PAD? Use the "Invoke web service" (HTTP) action with method, URL, headers, and body.

Advanced (Q61–Q85)

# Question Short Answer
61 What strategies make UI selectors more robust? Use stable attributes, avoid index-only selectors, leverage anchors, and use wildcards for dynamic values.
62 How does PAD identify elements (selectors)? Through a hierarchy of UI element attributes (control type, name, class) forming a selector path.
63 How do you make a selector dynamic? Edit the UI element to insert a variable into an attribute (e.g., %itemName%).
64 How do you handle dynamic or changing web pages? Add waits for element existence, use relative selectors, and reduce reliance on fixed indexes.
65 What is the "Wait for" family of actions? Actions that pause until a UI element, window, image, or condition appears/changes.
66 Why prefer "Wait for element" over fixed "Wait"? It waits only as long as needed, making flows faster and more reliable than hard-coded delays.
67 How do you implement retry logic? Use the "On error" retry policy, or a loop with a counter and try/catch-style block error.
68 How can you run PowerShell or scripts from PAD? Use "Run PowerShell script", "Run VBScript", "Run JavaScript", or "Run Python script" actions.
69 How do you run a SQL query in PAD? Use the database actions: open SQL connection, execute SQL statement, close connection.
70 How do you work with COM/automation in legacy apps? Use window/UI automation actions or scripts; PAD can drive apps exposing accessible UI.
71 How do you secure credentials in PAD? Store secrets in Azure Key Vault or environment variables; avoid hardcoding passwords.
72 What is the role of the on-premises data gateway? Not used by PAD directly; PAD connects via the machine/machine groups for cloud-triggered runs.
73 How is a desktop flow triggered from the cloud? A cloud flow uses the "Run a flow built with Power Automate Desktop" action targeting a registered machine.
74 What is a machine in Power Automate? A registered Windows device with the PAD runtime that can execute desktop flows.
75 What is a machine group? A pool of registered machines that distribute desktop flow runs for load balancing and resilience.
76 Difference between direct and gateway connectivity? Direct connectivity registers the machine to the cloud directly; the older model relied on a gateway.
77 How do you handle exceptions globally? Wrap logic in block errors, log failures, capture screenshots, and route to a clean-up subflow.
78 How do you log flow execution? Write to a file, database, or send results back to a cloud flow; use screenshots for diagnostics.
79 How do you debug a failing flow? Use breakpoints, run from a specific action, inspect variable values, and check the UI element selectors.
80 How do you optimize a slow desktop flow? Replace fixed waits with conditional waits, minimize UI hops, batch data ops, and reduce screenshots.
81 Difference between picture-in-picture and full desktop run? Picture-in-picture runs attended automations in an isolated session so you can keep working.
82 How do you reuse logic across many flows? Use subflows within a flow, or copy/standardize patterns; share via solutions in the cloud.
83 What are solutions in Power Platform? Containers that package flows and components for ALM (move dev → test → prod).
84 How do you move a desktop flow between environments? Add it to a solution and export/import, or use pipelines for ALM.
85 How do you handle sensitive input variables? Mark them as sensitive so their values are masked in logs and run history.

Expert (Q86–Q100)

# Question Short Answer
86 How do you design for unattended automation at scale? Use machine groups, idempotent flows, robust error handling, queueing, and centralized logging/monitoring.
87 How do you implement a work-queue pattern in PAD? Store items in Dataverse/SharePoint/SQL, have flows pick pending items, lock, process, then mark status.
88 How do you ensure idempotency in automation? Check state before acting, use unique keys, and design steps so re-runs don't duplicate effects.
89 What governance considerations apply to RPA? DLP policies, environment strategy, credential vaulting, naming standards, monitoring, and CoE oversight.
90 What is the Center of Excellence (CoE) Starter Kit? A Microsoft toolkit to govern, monitor, and scale Power Platform adoption across an organization.
91 How do you apply DLP to desktop flows? Define data loss prevention policies in the admin center to control connector usage and data movement.
92 How do you handle concurrency and locking? Use machine groups for parallelism plus record-level locking in the data store to avoid double processing.
93 How do you monitor desktop flow runs? Use run history, the monitoring portal, machine/machine-group dashboards, and CoE analytics.
94 How do you implement CI/CD for PAD flows? Use solutions with Power Platform Pipelines or Azure DevOps/GitHub to promote across environments.
95 How do you secure unattended run credentials? Store and retrieve secrets from Azure Key Vault; use service accounts with least privilege.
96 Common reasons selectors break in production? App updates, locale changes, dynamic IDs, layout changes, and resolution/DPI differences.
97 How do you make flows resilient to UI changes? Use stable attributes, anchors, OCR/image as fallback, defensive waits, and centralized selector updates.
98 How do you decide between attended and unattended? Attended for human-in-the-loop tasks; unattended for high-volume, scheduled, fully rule-based processes.
99 How do you measure RPA ROI? Track hours saved, error reduction, throughput, and cost per transaction versus manual processing.
100 When is RPA the wrong solution? When a proper API/integration exists, the process changes constantly, or the UI is too unstable to automate reliably.

Part B — Scenario Questions

Beginner Scenarios (Q1–Q25)

# Scenario Short Answer
1 A user must copy 50 rows from one spreadsheet into a web form daily. How do you start? Map the manual steps first, capture the form's UI elements, then build a loop that reads each row and fills the form.
2 Your recorded flow clicks the wrong button after the app window resizes. Likely cause? The recorder captured a position-based selector; recapture using a stable attribute instead of coordinates.
3 A teammate's flow works on their PC but not yours. First thing to check? Confirm the app version, screen resolution, browser extension, and that the same UI elements exist on your machine.
4 You need the flow to greet the user by name at start. How? Show an input dialog to capture the name, store it in a variable, then display it in a message.
5 A flow must pause until a slow report finishes loading. How to avoid guessing the time? Use a "wait for element" on something that only appears when the report is done, instead of a fixed wait.
6 The same five actions appear in three places in your flow. What do you do? Move them into a subflow and call it from each place to reduce duplication.
7 A flow should only email a summary if at least one record was processed. How? Track a counter, then use an If condition to send the email only when the count is greater than zero.
8 You want to test a flow without sending real emails. How? Disable or comment the send action and use a display-message in its place during testing.
9 A flow needs to open a file whose name changes daily (date-stamped). How? Build the filename dynamically from the current date and pass that variable to the open-file action.
10 The user accidentally closes the target app mid-run. How to recover? Detect the missing window, relaunch the app, and resume or restart from a safe checkpoint.
11 You must skip blank rows in an Excel sheet. How? Inside the loop, use an If condition to continue to the next iteration when the key cell is empty.
12 A flow keeps typing before the page is ready. Fix? Add a wait-for-element-ready or wait-for-page-load before the typing action.
13 You need to confirm a flow ran successfully each morning. Simplest approach? Write a timestamped success line to a log file at the end of the flow.
14 A flow must handle both .xls and .xlsx files in a folder. How? List files with a wildcard pattern, then loop through and process each matching file.
15 The business wants the flow to ask "Are you sure?" before deleting records. How? Use a message box with Yes/No buttons and branch on the user's choice.
16 A flow should stop early if a required file is missing. How? Check if the file exists; if not, show a message and end the flow gracefully.
17 You need to rename hundreds of downloaded files consistently. How? Loop the files and apply a rename action using a built pattern (prefix + index/date).
18 The flow must copy text from a desktop app field into Excel. How? Capture the field as a UI element, read its value into a variable, then write it to the worksheet.
19 A flow runs too fast for a legacy app to keep up. Quick fix? Insert short waits or wait-for-element steps between actions to let the app catch up.
20 You want non-technical users to launch the flow easily. How? Pin it in the PAD console or surface it via a simple trigger so they just click Run.
21 A flow must extract today's date in yyyy-MM-dd format. How? Get the current datetime and convert it to text with that format specifier.
22 The flow should clear a field before typing into it. How? Focus the field, select all and delete (or set the field value to empty) before populating.
23 You need to verify a login succeeded before continuing. How? Wait for a post-login element (e.g., dashboard) to appear, and branch to error handling if it doesn't.
24 A flow must total a numeric column from Excel. Approach? Loop the rows accumulating a sum variable, or read the range and compute the total.
25 The user wants a popup at the end showing how long the flow took. How? Capture start and end datetimes, compute the difference, and display it in a message.

Intermediate Scenarios (Q26–Q55)

# Scenario Short Answer
26 A flow occasionally fails because a popup appears randomly. How to handle it? Before the main step, check for the popup element and dismiss it if present, then continue.
27 You must process 10,000 rows but the flow is slow. What do you optimize? Minimize per-row UI interactions, read/write in bulk where possible, and remove unnecessary waits/screenshots.
28 A web element is found but the click does nothing. Likely fix? The element may be obscured or not interactive yet; wait for it to be enabled/visible or scroll it into view first.
29 A flow needs to retry a flaky network call up to three times. How? Wrap it in a loop with a counter and a try/catch-style block error, breaking on success.
30 You must extract line items from PDF invoices. Approach? Use PDF extraction actions to pull text/tables, then parse the fields into a datatable.
31 The same flow must run for three different clients with different settings. How? Externalize settings into a config file/table and load the right set based on an input parameter.
32 A flow downloads a file but the next step runs before download completes. Fix? Wait until the file exists and its size stops changing before proceeding.
33 You need to compare two Excel sheets and flag mismatches. Approach? Read both into datatables, loop one keyed by a unique column, look up the other, and record differences.
34 A flow must send different emails based on order status. How? Use a Switch on the status value with a case for each path, or chained If/Else branches.
35 The target website shows a CAPTCHA. What's the right stance? Don't try to bypass it; redesign around an API/authorized channel or insert a human-in-the-loop attended step.
36 A flow throws "element not found" intermittently. How to stabilize it? Add wait-for-element with a timeout, improve the selector, and add retry on the action.
37 You must mask a password that appears in run logs. How? Mark the input variable as sensitive so its value is hidden in history and logs.
38 A flow needs data from a REST API and then enters it into an app. Approach? Invoke the web service, parse the JSON into a custom object, then drive the app UI with those values.
39 Two flows occasionally edit the same Excel file and collide. Fix? Serialize access (a lock/flag), or have one flow own the file and queue requests.
40 A long flow fails near the end and you don't want to redo everything. Design fix? Add checkpoints/state so the flow can resume from the last completed stage.
41 You need to validate an email format before submitting. How? Apply a regex/pattern check and branch to an error path if it doesn't match.
42 A flow must loop through rows but stop when it hits a "STOP" marker. How? Inside the loop, test the cell value and use exit-loop when it equals the marker.
43 The app sometimes opens in a different window position. How to click reliably? Use UI-element selectors (not coordinates) and bring the window to focus before interacting.
44 A flow needs to keep only unique values from a list with duplicates. How? Build a result list, and before adding each item, check it isn't already present.
45 You must capture an error screenshot only when something fails. How? In the block error / on-error handler, take a screenshot and save it with a timestamped name.
46 A flow reads a date as text but needs to do date math. How? Convert the text to a datetime using the source format, then add/subtract as needed.
47 The business wants a daily run but PAD has no scheduler. How? Trigger the desktop flow from a scheduled cloud flow.
48 A flow must handle a dropdown that loads options dynamically. How? Wait for the options to populate, then select by visible text rather than index.
49 You need to move processed files to an "archive" folder. How? After processing each file successfully, move it to the archive path; leave failures in place.
50 A flow's output must feed a Power Automate cloud flow. How? Define output variables in the desktop flow; the cloud flow reads them after the run.
51 The same selector breaks whenever the record ID changes. Fix? Parameterize the selector with a variable so it adapts to the current ID.
52 You must extract a number embedded in a sentence. Approach? Use regex/text actions to isolate the digits, then convert to a number.
53 A flow needs to send a Teams/email alert on failure. How? In the error handler, call a notification step (cloud flow or email action) with the error details.
54 A flow processes a folder that may be empty some days. How? Check the file count first and exit gracefully with a "nothing to process" log if zero.
55 You want to reuse one error-handling routine across many steps. How? Centralize cleanup/logging in a subflow and call it from each on-error handler.

Advanced Scenarios (Q56–Q85)

# Scenario Short Answer
56 An unattended flow fails at 2 AM with no one watching. How to make it safe? Add full error handling, logging, failure notifications, and a clean exit so the machine isn't left in a bad state.
57 A flow works attended but fails unattended. Common cause? The unattended session has no interactive desktop / locked-screen issues; ensure proper machine/session setup and no UI prompts.
58 Selectors break after a vendor app update. How to minimize future pain? Centralize selectors, use stable attributes/anchors, and add a fallback (OCR/image) for fragile elements.
59 Two machines in a group sometimes grab the same work item. Fix? Implement record-level locking/status in the data store so an item is claimed before processing.
60 You must process 50,000 items within a window using multiple bots. Design? Use a work queue plus a machine group, with each bot pulling and locking pending items.
61 A flow must run as a specific service account with vaulted secrets. How? Run unattended under that account and retrieve credentials from Azure Key Vault at runtime.
62 Throughput drops when many flows hit one database. What do you change? Add connection pooling/batching, reduce round-trips, and stagger or throttle concurrent runs.
63 A regulator needs an audit trail of every action a bot takes. Approach? Log each step (who/what/when, inputs/outputs) to a durable store, with sensitive data masked.
64 A flow must continue past a non-critical error but stop on critical ones. How? Classify errors in the handler: continue/log for non-critical, raise/abort for critical.
65 You need to promote a flow from Dev to Prod safely. Approach? Package it in a solution and move via Pipelines/ALM rather than manually rebuilding.
66 A long-running flow hangs and never times out. Fix? Set timeouts on waits and actions, and add a watchdog/overall time limit that aborts cleanly.
67 The same flow must support English and another locale's date format. How? Detect or parameterize locale and parse/format dates using the correct culture.
68 A bot must hand off to a human for approval mid-process. Design? Pause and route to an approval (cloud flow/Teams), then resume based on the decision.
69 You suspect a memory/resource leak over long runs. How to investigate? Monitor the machine's resource usage, close apps/handles you open, and split very long runs.
70 A flow must process items in priority order. How? Sort or query the work queue by priority before the processing loop.
71 Production fails but you can't reproduce it in Dev. Approach? Add detailed logging/screenshots in prod, compare environment/data differences, and replay with prod-like data.
72 A flow runs on Citrix/virtual desktop where UI elements aren't exposed. How? Fall back to image-based automation and OCR since structured selectors aren't available.
73 You need zero data loss if a bot crashes mid-batch. Design? Make each item idempotent and commit progress per item so a restart resumes safely.
74 A team wants standardized flows across many developers. How? Establish templates, naming conventions, shared subflows, and code-review/CoE standards.
75 A flow must not run two copies at once on the same machine. How? Use a lock/flag (file, mutex, or queue status) so a second instance exits if one is active.
76 The business changes the process weekly. Is RPA still right? Often no — high-change processes break UI bots; recommend an API/integration or stabilize the process first.
77 You must securely pass an API token to a flow without hardcoding. How? Pull it from Key Vault or an environment variable at runtime; never store it in the flow.
78 A bot must reconcile data between two systems and report drift. Approach? Pull both datasets, key-match them, compute differences, and output a reconciliation report.
79 A flow's run history shows random failures only under load. Likely area? Timing/race conditions — strengthen waits, add retries, and reduce contention on shared resources.
80 You need to roll back a faulty flow version quickly. How? Keep versioned solutions/exports so you can re-import the last known good version.
81 A flow must respect a maintenance window and not run during it. How? Gate execution with a time/window check (or schedule logic) that exits if inside the blackout.
82 Sensitive PII flows through a bot. What controls apply? Mask in logs, encrypt at rest/in transit, limit access, and apply DLP policies on connectors.
83 A flow must scale to more bots next quarter. What do you prepare? A queue-based architecture, a machine group, idempotent items, and centralized monitoring.
84 A vendor portal logs you out after inactivity. How to handle long jobs? Keep the session alive with periodic activity, or chunk work and re-login between batches.
85 You inherit an undocumented flow that breaks often. First steps? Map its logic, add logging, identify fragile selectors, and refactor into subflows with error handling.

Expert Scenarios (Q86–Q100)

# Scenario Short Answer
86 Leadership asks whether to automate a process at all. How do you decide? Assess volume, stability, rule-clarity, and ROI; automate stable high-volume rule-based work, defer the rest.
87 You're designing an enterprise RPA platform from scratch. Key pillars? Governance/CoE, environment strategy, queue architecture, credential vaulting, monitoring, and ALM pipelines.
88 Citizen developers are creating ungoverned bots. How to regain control? Stand up a CoE, apply DLP and environment policies, inventory existing flows, and set standards/training.
89 Two business units want conflicting changes to a shared bot. How to manage it? Parameterize/config-drive the shared logic, branch via solutions, and govern changes through a review process.
90 A critical bot must meet an SLA with high availability. Design? Use a machine group across hosts, health checks, automatic failover/retries, and proactive monitoring/alerting.
91 You must estimate capacity for a new automation program. Approach? Model transaction volume × handle time vs. bot hours, then size machines/groups with headroom for peaks.
92 Auditors require proof that bots only access permitted data. How? Enforce least-privilege service accounts, DLP policies, access logging, and documented data-flow diagrams.
93 A flagship process spans web, desktop, and APIs across teams. How to architect it? Orchestrate with a cloud flow calling modular desktop flows, each owning one system, with a shared queue/state.
94 Costs are rising with bot count. How to optimize spend? Consolidate flows, right-size machines, retire low-ROI bots, and prefer APIs where they replace UI automation.
95 You need a disaster-recovery plan for the automation estate. What's in it? Versioned exports, documented configs/secrets locations, machine rebuild steps, and tested restore procedures.
96 A merger doubles the systems your bots touch overnight. Strategy? Inventory and prioritize by ROI, reuse modular subflows, standardize patterns, and phase rollout by risk.
97 The board wants metrics on automation value. What do you report? Hours saved, error reduction, throughput, SLA adherence, cost per transaction, and adoption trends.
98 A bot caused a costly error in production. How to respond and prevent recurrence? Contain/rollback, root-cause the failure, add validation/guardrails and tests, and update the runbook.
99 You must choose between RPA and a proper system integration. How to frame it? Prefer integration when a stable API exists and volume/longevity justify it; use RPA for legacy/no-API or short-term needs.
100 Adoption stalled after early wins. How to scale sustainably? Build a CoE, reusable assets, training, a prioritized pipeline, executive sponsorship, and clear value tracking.

Friday, June 12, 2026

Create a Dataverse Table With Every Common Column Type Using Power Automate

Create a Dataverse Table With Every Common Column Type Using Power Automate

Create a Dataverse Table With Every Common Column Type Using Power Automate

If you have ever clicked through the maker portal to build a Dataverse table column by column, you know how slow it gets. There is a faster, repeatable way: send one HTTP request to the Dataverse Web API and let it build the whole table — primary column, choices, currency, dates, the lot — in a single shot.

In this post I will walk through a Power Automate cloud flow that does exactly that. It creates a demo table called Sample Product with one column of every common type, so you can see how each column type is defined in the Web API.


The idea in one line

Dataverse exposes a metadata endpoint. POST a JSON definition of your table to it, and Dataverse creates the table and all the columns you described.

POST https://yourorg.crm.dynamics.com/api/data/v9.2/EntityDefinitions

Replace yourorg with your own environment URL. You can find it in the Power Platform Admin Center under your environment's settings, or in the address bar when you open any model-driven app.


The flow at a glance

The flow has just two steps after the trigger:

Step Action What it does
Trigger Manually trigger a flow Lets you run it on demand with a button
1 Compose Holds the full table definition as JSON
2 HTTP request POSTs that JSON to the EntityDefinitions endpoint

Keeping the definition in a Compose action makes the flow easy to read and easy to tweak. The HTTP action just points at the Compose output.

Connector note: This example uses the HTTP with Microsoft Entra ID (preauthorized) connector (the "Invoke an HTTP request" action). It is a premium connector, but it handles authentication to Dataverse for you, so you do not have to manage tokens by hand.


Step 1 — Compose the table definition

The Compose action holds an EntityMetadata object. The top part describes the table itself; the Attributes array describes each column.

Here is the table-level part:

{
  "@@odata.type": "Microsoft.Dynamics.CRM.EntityMetadata",
  "SchemaName": "new_SampleProduct",
  "DisplayName": {
    "@@odata.type": "Microsoft.Dynamics.CRM.Label",
    "LocalizedLabels": [
      { "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Sample Product", "LanguageCode": 1033 }
    ]
  },
  "DisplayCollectionName": {
    "@@odata.type": "Microsoft.Dynamics.CRM.Label",
    "LocalizedLabels": [
      { "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Sample Products", "LanguageCode": 1033 }
    ]
  },
  "OwnershipType": "UserOwned",
  "IsActivity": false,
  "HasActivities": false,
  "HasNotes": false,
  "Attributes": [ ... ]
}

A few things worth knowing here:

Property Meaning
SchemaName The internal name. new_ is the default publisher prefix — change it to match your own solution publisher.
DisplayName The singular label shown in the UI ("Sample Product").
DisplayCollectionName The plural label ("Sample Products").
OwnershipType UserOwned means rows belong to a user or team. Use OrganizationOwned for shared reference data.
LanguageCode: 1033 The locale ID for English (United States). Use your own LCID if needed.

Why the double @@?

You will notice @@odata.type instead of @odata.type. This is a Power Automate quirk, not a Dataverse one. In Power Automate the @ symbol starts an expression, so to send a literal @ you have to double it. When the flow runs, @@odata.type is sent to Dataverse as @odata.type. If you copy this JSON somewhere outside Power Automate, drop one of the @ signs.


The column types, explained

Every column lives in the Attributes array and needs a matching @odata.type. Pick the wrong type and you get a 400 Bad Request. Here is each column type used in the demo table:

Column @odata.type Dataverse type Notes
Product Name StringAttributeMetadata Single line of text The primary name column. IsPrimaryName: true, MaxLength: 100. Every table needs exactly one.
Description MemoAttributeMetadata Multiple lines of text Format: TextArea, MaxLength: 2000.
Quantity IntegerAttributeMetadata Whole number Set MinValue / MaxValue to bound it.
Serial Number BigIntAttributeMetadata Big integer For very large whole numbers.
Weight DecimalAttributeMetadata Decimal number Has Precision (decimal places) and min/max.
Rating DoubleAttributeMetadata Float Floating-point number; also has Precision.
Price MoneyAttributeMetadata Currency Uses PrecisionSource (0 = no decimals, 1 = currency precision, 2 = pricing decimal precision).
Is Active BooleanAttributeMetadata Yes/No Needs an OptionSet with a TrueOption and FalseOption.
Received Date DateTimeAttributeMetadata Date and time Use Format: DateAndTime, or DateOnly for a date-only column.
Category PicklistAttributeMetadata Choice (single) A local choice set. Option values start at 100000000.
Tags MultiSelectPicklistAttributeMetadata Choices (multi) Note its AttributeType is Virtual, not MultiSelectPicklist.

The primary name column (the one you cannot skip)

{
  "@@odata.type": "Microsoft.Dynamics.CRM.StringAttributeMetadata",
  "AttributeType": "String",
  "AttributeTypeName": { "Value": "StringType" },
  "SchemaName": "new_ProductName",
  "IsPrimaryName": true,
  "MaxLength": 100,
  "FormatName": { "Value": "Text" },
  "RequiredLevel": { "Value": "None", "CanBeChanged": true, "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings" },
  "DisplayName": {
    "@@odata.type": "Microsoft.Dynamics.CRM.Label",
    "LocalizedLabels": [
      { "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Product Name", "LanguageCode": 1033 }
    ]
  }
}

If you forget IsPrimaryName: true, or include zero (or more than one) of them, the request fails. This is the most common mistake when building tables this way.

A choice column (single select)

A local choice set lives right inside the column definition. Each option needs a numeric Value and a label:

{
  "@@odata.type": "Microsoft.Dynamics.CRM.PicklistAttributeMetadata",
  "AttributeType": "Picklist",
  "AttributeTypeName": { "Value": "PicklistType" },
  "SchemaName": "new_Category",
  "OptionSet": {
    "@@odata.type": "Microsoft.Dynamics.CRM.OptionSetMetadata",
    "IsGlobal": false,
    "OptionSetType": "Picklist",
    "Options": [
      { "Value": 100000000, "Label": { "@@odata.type": "Microsoft.Dynamics.CRM.Label", "LocalizedLabels": [ { "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Electronics", "LanguageCode": 1033 } ] } },
      { "Value": 100000001, "Label": { "@@odata.type": "Microsoft.Dynamics.CRM.Label", "LocalizedLabels": [ { "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Apparel", "LanguageCode": 1033 } ] } }
    ]
  }
}

Set IsGlobal: true if you want a global choice set that other tables can reuse. The multi-select column ("Tags") follows the same shape — the only real difference is the @odata.type and that its AttributeType is reported as Virtual.


Step 2 — The HTTP request

The second action posts the Compose output to Dataverse:

Setting Value
Method POST
URL https://yourorg.crm.dynamics.com/api/data/v9.2/EntityDefinitions
Body @outputs('Compose_Table_Definition')

And the headers:

Content-Type: application/json; charset=utf-8
OData-MaxVersion: 4.0
OData-Version: 4.0
Accept: application/json

On success Dataverse returns HTTP 204 No Content, with an OData-EntityId header pointing at your new table. No body comes back — the empty 204 is the success signal.


Two improvements worth adding

The base flow works, but two small additions make it production-friendly.

1. Put the table in a solution. As written, the new table lands in the Default Solution, which is an ALM anti-pattern — it makes the table hard to move between environments. Add this header to the HTTP action so the table is created inside your own unmanaged solution:

MSCRM.SolutionUniqueName: YourSolutionUniqueName

Use the solution's unique name, not its display name.

2. Read it back with a strong-consistency header. Metadata is cached, so if you immediately query the new table it might return a 404 because the cache has not caught up. When you read straight after creating, add:

Consistency: Strong

Wrap-up

With one Compose action and one HTTP request you can stand up a full Dataverse table — primary column, numbers, currency, dates, and both flavours of choice — without touching the maker portal. Because the whole definition is just JSON, you can version it, parameterise it, or drive it from a CSV or SharePoint list to build tables on demand.

The same EntityDefinitions endpoint also handles updates (PUT) and lets you add columns to an existing table later (POST to its Attributes collection), so this is a solid foundation for any metadata-as-code approach on the Power Platform.


Quick reference: column type to @odata.type

Column type you want @odata.type to use
Single line of text StringAttributeMetadata
Multiple lines of text MemoAttributeMetadata
Whole number IntegerAttributeMetadata
Big integer BigIntAttributeMetadata
Decimal number DecimalAttributeMetadata
Float DoubleAttributeMetadata
Currency MoneyAttributeMetadata
Yes/No BooleanAttributeMetadata
Date and time DateTimeAttributeMetadata
Choice (single) PicklistAttributeMetadata
Choices (multi) MultiSelectPicklistAttributeMetadata

Full flow JSON

Here is the complete flow, scrubbed of environment-specific values. Before you use it, replace two placeholders:

  • yourorg.crm.dynamics.com in the HTTP action URL with your own environment URL.
  • The connection details (connectionName, connectionReferenceLogicalName) will be set automatically when you add your own HTTP with Microsoft Entra ID connection — the placeholder values below are only there to keep the JSON valid.

Note: the @@odata.type double-@ is correct for this Power Automate definition (it is how the editor escapes a literal @). Keep it as-is when pasting into the flow editor's code view. If you ever send the body straight to Dataverse outside Power Automate, use a single @odata.type.

{
  "$schema": "https://power-automate-tools.local/flow-editor.json#",
  "connectionReferences": {
    "shared_webcontents": {
      "connectionName": "shared-webcontents-00000000-0000-0000-0000-000000000000",
      "connectionReferenceLogicalName": "new_sharedwebcontents_xxxxx",
      "source": "Invoker",
      "id": "/providers/Microsoft.PowerApps/apis/shared_webcontents",
      "displayName": "HTTP with Microsoft Entra ID (preauthorized)",
      "iconUri": "https://conn-afd-prod-endpoint-bmc9bqahasf3grgk.b01.azurefd.net/releases/v1.0.1800/1.0.1800.4648/webcontents/icon.png",
      "brandColor": "",
      "tier": "Premium",
      "apiName": "webcontents",
      "isProcessSimpleApiReferenceConversionAlreadyDone": false
    }
  },
  "definition": {
    "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
    "contentVersion": "undefined",
    "parameters": {
      "$authentication": {
        "defaultValue": {},
        "type": "SecureObject"
      },
      "$connections": {
        "defaultValue": {},
        "type": "Object"
      }
    },
    "triggers": {
      "manual": {
        "type": "Request",
        "kind": "Button",
        "inputs": {
          "schema": {
            "type": "object",
            "properties": {},
            "required": []
          }
        }
      }
    },
    "actions": {
      "Compose_Table_Definition": {
        "runAfter": {},
        "type": "Compose",
        "inputs": {
          "@@odata.type": "Microsoft.Dynamics.CRM.EntityMetadata",
          "SchemaName": "new_SampleProduct",
          "DisplayName": {
            "@@odata.type": "Microsoft.Dynamics.CRM.Label",
            "LocalizedLabels": [
              {
                "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                "Label": "Sample Product",
                "LanguageCode": 1033
              }
            ]
          },
          "DisplayCollectionName": {
            "@@odata.type": "Microsoft.Dynamics.CRM.Label",
            "LocalizedLabels": [
              {
                "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                "Label": "Sample Products",
                "LanguageCode": 1033
              }
            ]
          },
          "Description": {
            "@@odata.type": "Microsoft.Dynamics.CRM.Label",
            "LocalizedLabels": [
              {
                "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                "Label": "A demo table that shows every common column type.",
                "LanguageCode": 1033
              }
            ]
          },
          "OwnershipType": "UserOwned",
          "IsActivity": false,
          "HasActivities": false,
          "HasNotes": false,
          "Attributes": [
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.StringAttributeMetadata",
              "AttributeType": "String",
              "AttributeTypeName": {
                "Value": "StringType"
              },
              "SchemaName": "new_ProductName",
              "IsPrimaryName": true,
              "MaxLength": 100,
              "FormatName": {
                "Value": "Text"
              },
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Product Name",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Primary name column (Single line of text).",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.MemoAttributeMetadata",
              "AttributeType": "Memo",
              "AttributeTypeName": {
                "Value": "MemoType"
              },
              "SchemaName": "new_Description",
              "Format": "TextArea",
              "MaxLength": 2000,
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Description",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Multiple lines of text.",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.IntegerAttributeMetadata",
              "AttributeType": "Integer",
              "AttributeTypeName": {
                "Value": "IntegerType"
              },
              "SchemaName": "new_Quantity",
              "Format": "None",
              "MinValue": 0,
              "MaxValue": 1000000,
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Quantity",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Whole number.",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.BigIntAttributeMetadata",
              "AttributeType": "BigInt",
              "AttributeTypeName": {
                "Value": "BigIntType"
              },
              "SchemaName": "new_SerialNumber",
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Serial Number",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Big integer (large whole number).",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.DecimalAttributeMetadata",
              "AttributeType": "Decimal",
              "AttributeTypeName": {
                "Value": "DecimalType"
              },
              "SchemaName": "new_Weight",
              "MinValue": 0,
              "MaxValue": 100000,
              "Precision": 2,
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Weight",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Decimal number.",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.DoubleAttributeMetadata",
              "AttributeType": "Double",
              "AttributeTypeName": {
                "Value": "DoubleType"
              },
              "SchemaName": "new_Rating",
              "MinValue": 0,
              "MaxValue": 1000000,
              "Precision": 2,
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Rating",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Float (floating point number).",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.MoneyAttributeMetadata",
              "AttributeType": "Money",
              "AttributeTypeName": {
                "Value": "MoneyType"
              },
              "SchemaName": "new_Price",
              "PrecisionSource": 2,
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Price",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Currency.",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.BooleanAttributeMetadata",
              "AttributeType": "Boolean",
              "AttributeTypeName": {
                "Value": "BooleanType"
              },
              "SchemaName": "new_IsActive",
              "DefaultValue": false,
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "OptionSet": {
                "OptionSetType": "Boolean",
                "TrueOption": {
                  "Value": 1,
                  "Label": {
                    "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                    "LocalizedLabels": [
                      {
                        "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                        "Label": "Yes",
                        "LanguageCode": 1033
                      }
                    ]
                  }
                },
                "FalseOption": {
                  "Value": 0,
                  "Label": {
                    "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                    "LocalizedLabels": [
                      {
                        "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                        "Label": "No",
                        "LanguageCode": 1033
                      }
                    ]
                  }
                }
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Is Active",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Yes/No.",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.DateTimeAttributeMetadata",
              "AttributeType": "DateTime",
              "AttributeTypeName": {
                "Value": "DateTimeType"
              },
              "SchemaName": "new_ReceivedDate",
              "Format": "DateAndTime",
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Received Date",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Date and time. Use Format DateOnly for date only.",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.PicklistAttributeMetadata",
              "AttributeType": "Picklist",
              "AttributeTypeName": {
                "Value": "PicklistType"
              },
              "SchemaName": "new_Category",
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "OptionSet": {
                "@@odata.type": "Microsoft.Dynamics.CRM.OptionSetMetadata",
                "IsGlobal": false,
                "OptionSetType": "Picklist",
                "Options": [
                  {
                    "Value": 100000000,
                    "Label": {
                      "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                      "LocalizedLabels": [
                        {
                          "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                          "Label": "Electronics",
                          "LanguageCode": 1033
                        }
                      ]
                    }
                  },
                  {
                    "Value": 100000001,
                    "Label": {
                      "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                      "LocalizedLabels": [
                        {
                          "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                          "Label": "Apparel",
                          "LanguageCode": 1033
                        }
                      ]
                    }
                  },
                  {
                    "Value": 100000002,
                    "Label": {
                      "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                      "LocalizedLabels": [
                        {
                          "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                          "Label": "Grocery",
                          "LanguageCode": 1033
                        }
                      ]
                    }
                  }
                ]
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Category",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Choice (single select).",
                    "LanguageCode": 1033
                  }
                ]
              }
            },
            {
              "@@odata.type": "Microsoft.Dynamics.CRM.MultiSelectPicklistAttributeMetadata",
              "AttributeType": "Virtual",
              "AttributeTypeName": {
                "Value": "MultiSelectPicklistType"
              },
              "SchemaName": "new_Tags",
              "RequiredLevel": {
                "Value": "None",
                "CanBeChanged": true,
                "ManagedPropertyLogicalName": "canmodifyrequirementlevelsettings"
              },
              "OptionSet": {
                "@@odata.type": "Microsoft.Dynamics.CRM.OptionSetMetadata",
                "IsGlobal": false,
                "OptionSetType": "Picklist",
                "Options": [
                  {
                    "Value": 100000000,
                    "Label": {
                      "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                      "LocalizedLabels": [
                        {
                          "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                          "Label": "New",
                          "LanguageCode": 1033
                        }
                      ]
                    }
                  },
                  {
                    "Value": 100000001,
                    "Label": {
                      "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                      "LocalizedLabels": [
                        {
                          "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                          "Label": "Featured",
                          "LanguageCode": 1033
                        }
                      ]
                    }
                  },
                  {
                    "Value": 100000002,
                    "Label": {
                      "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                      "LocalizedLabels": [
                        {
                          "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                          "Label": "On Sale",
                          "LanguageCode": 1033
                        }
                      ]
                    }
                  }
                ]
              },
              "DisplayName": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Tags",
                    "LanguageCode": 1033
                  }
                ]
              },
              "Description": {
                "@@odata.type": "Microsoft.Dynamics.CRM.Label",
                "LocalizedLabels": [
                  {
                    "@@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel",
                    "Label": "Choices (multi select).",
                    "LanguageCode": 1033
                  }
                ]
              }
            }
          ]
        }
      },
      "Invoke_an_HTTP_request": {
        "runAfter": {
          "Compose_Table_Definition": [
            "Succeeded"
          ]
        },
        "type": "OpenApiConnection",
        "inputs": {
          "parameters": {
            "request/method": "POST",
            "request/url": "https://yourorg.crm.dynamics.com/api/data/v9.2/EntityDefinitions",
            "request/headers": {
              "Content-Type": "application/json; charset=utf-8",
              "OData-MaxVersion": "4.0",
              "OData-Version": "4.0",
              "Accept": "application/json"
            },
            "request/body": "@outputs('Compose_Table_Definition')"
          },
          "host": {
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_webcontents",
            "operationId": "InvokeHttp",
            "connectionName": "shared_webcontents"
          },
          "retryPolicy": {
            "type": "none"
          }
        }
      }
    }
  }
}

Featured Post

Send Emails via Microsoft Graph API Using PowerShell and App Registration

Send Emails via Microsoft Graph API Using PowerShell and App Registration Microsoft Graph API is the unified gateway to Microsoft 365 data ...

Popular posts