2026-03-28 · HashTurn Team

SharePoint & Power Automate: Real Problems, Real Solutions — A Developer's Field Guide

Power AutomateSharePointMicrosoft 365REST APICloud FlowsExternal UsersUMLColumn Formatting

Introduction

If you work with SharePoint lists and Power Automate daily, you've probably hit walls that the official Microsoft docs don't fully explain. This guide compiles real-world problems and their working solutions — from building dynamic HTML email tables, to handling deleted items, to managing external user permissions — all in one place.

1. Build a Dynamic HTML Table in Power Automate Emails

One of the most common requirements is sending a formatted HTML table via email from a Power Automate flow. The trick is splitting the table structure across two actions.

Compose Action — Table Wrapper

Place this in your Compose action. This defines the table header and references a string variable that will hold your rows:

<table style="border-collapse: collapse; width: 100%; font-family: Arial, sans-serif;">
  <thead>
    <tr style="background-color: #4472C4; color: white;">
      <th style="border: 1px solid #ccc; padding: 10px 14px; text-align: left;">File Name</th>
      <th style="border: 1px solid #ccc; padding: 10px 14px; text-align: left;">Link</th>
    </tr>
  </thead>
  <tbody>
@{variables('TableRows')}
  </tbody>
</table>

Replace TableRows with whatever your variable is actually named.

Append to String Variable — Each Row

Inside your loop or condition, use Append to string variable with this code:

<tr style="background-color: #ffffff;">
  <td style="border: 1px solid #ccc; padding: 8px 14px;">YOUR_FILENAME_HERE</td>
  <td style="border: 1px solid #ccc; padding: 8px 14px;">
    <a href="YOUR_LINK_HERE">Open File</a>
  </td>
</tr>

Replace YOUR_FILENAME_HERE and YOUR_LINK_HERE with dynamic content expressions such as items('Apply_to_each')?['Name'] and items('Apply_to_each')?['Link'].

How It Works Together

  1. Initialize a String variable (e.g. TableRows) as empty before your loop
  2. Inside your condition, append the <tr> row code to the variable
  3. After the loop, use the Compose with the full table wrapper referencing variables('TableRows')

2. Set SharePoint Date Columns to UK Format (DD/MM/YYYY)

SharePoint defaults to US date format. Here are three ways to fix it depending on your situation.

Method 1 — Regional Settings (Recommended)

This fixes all lists on the site at once:

  1. Go to your SharePoint site
  2. Click Settings → Site information → View all site settings
  3. Under Site Administration, click Regional settings
  4. Set Locale to English (United Kingdom)
  5. Click OK

⚠️ This does not automatically apply to existing subsites — you must repeat this on each subsite manually.

Method 2 — Calculated Column (Single Column Fix)

Add a calculated column with this formula and set the return type to Single line of text:

=TEXT([YourDateColumn],"dd/mm/yyyy")

Method 3 — JSON Column Formatting

Go to the column → Column settings → Format this column → Advanced mode and paste:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=toLocaleDateString(@currentField)"
}
Situation Best Method
All lists on a site Method 1 — Regional Settings
One column only Method 2 — Calculated Column
Visual override for a view Method 3 — JSON Formatting
Multiple subsites Repeat Method 1 on each

3. Remove a User's Access to a SharePoint List Item via Power Automate

This is a three-step process using the SharePoint REST API through the Send an HTTP request to SharePoint action.

Step 1 — Break Role Inheritance on the Item

Field Value
Method POST
URI _api/web/lists/getbytitle('YourListName')/items(ID)/breakroleinheritance(copyRoleAssignments=true, clearSubscopes=true)
Header: Accept application/json;odata=verbose
Header: Content-Type application/json;odata=verbose

Set copyRoleAssignments=true to preserve existing permissions before removing the user. Set to false to start completely fresh.

Step 2 — Get the User ID (External Users)

For external users, use ensureuser instead of getbyemail — it handles both internal and external Microsoft accounts:

Field Value
Method POST
URI _api/web/ensureuser
Body {"logonName": "i:0#.f|membership|user@domain.com"}

The i:0#.f|membership| prefix is required for external/Microsoft accounts. To use this dynamically from a People column:

{"logonName": "i:0#.f|membership|@{triggerOutputs()?['body/AssignedTo/Email']}"}

Parse the response with this JSON schema to extract the Id:

{
  "type": "object",
  "properties": {
    "d": {
      "type": "object",
      "properties": {
        "Id": { "type": "integer" },
        "LoginName": { "type": "string" },
        "Email": { "type": "string" }
      }
    }
  }
}

Step 3 — Remove the User's Permission

Field Value
Method POST
URI _api/web/lists/getbytitle('YourListName')/items(ID)/roleassignments/getbyprincipalid(USER_ID)/DeleteObject

Replace USER_ID with body('Parse_JSON')?['d']?['Id'] from Step 2.

4. Getting Deleted Item Data in Power Automate — What Actually Works

This is one of the most misunderstood limitations in Power Automate. Here is the honest truth:

SharePoint's "When an item is deleted" trigger fires only after the item is already gone. There is nothing left to query — not via REST, not via version history, not via the Recycle Bin API.

What the Recycle Bin API Returns

The Recycle Bin REST API (_api/web/RecycleBin) only gives you:

Field Available?
Title / file name ✅ Yes
Deleted date ✅ Yes
Deleted by (name & email) ✅ Yes
Author name & email ✅ Yes
Custom list columns ❌ No
People column values ❌ No
Lookup or choice columns ❌ No

The Solutions That Actually Work

Option 1 — Log List Pattern (Most Reliable)

Create a mirror "Log List" that stores item data whenever items are created or modified. When deletion triggers, query the Log List by item ID.

Option 2 — Soft Delete Pattern

Add an IsDeleted Yes/No column. Instead of deleting, users set it to Yes. Your flow triggers on column change and has full access to every field, including user email.

Option 3 — Capture Before Deletion

Trigger a flow on status change (e.g. Status = "To Be Deleted"), save all fields, then delete.

SharePoint List Rules cannot trigger Power Automate flows — they only send notification emails. This is a common misconception.

5. Format a Yes/No Column as a Delete Button (JSON Formatting)

To make your IsDeleted Yes/No column look like a red Delete button when Yes and a green Active badge when No, go to Column settings → Format this column → Advanced mode and paste:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "display": "flex",
    "align-items": "center"
  },
  "children": [
    {
      "elmType": "div",
      "style": {
        "display": "=if(@currentField == 1, 'flex', 'none')",
        "align-items": "center",
        "justify-content": "center",
        "background-color": "#D13438",
        "color": "white",
        "padding": "5px 14px",
        "border-radius": "4px",
        "font-size": "13px",
        "font-weight": "600",
        "cursor": "pointer",
        "gap": "6px"
      },
      "children": [
        {
          "elmType": "span",
          "attributes": { "iconName": "Delete" }
        },
        {
          "elmType": "span",
          "txtContent": "Delete"
        }
      ]
    },
    {
      "elmType": "div",
      "style": {
        "display": "=if(@currentField == 1, 'none', 'flex')",
        "align-items": "center",
        "background-color": "#EAF3DE",
        "color": "#27500A",
        "padding": "5px 14px",
        "border-radius": "4px",
        "font-size": "13px",
        "font-weight": "600",
        "gap": "6px"
      },
      "children": [
        {
          "elmType": "span",
          "attributes": { "iconName": "CheckMark" }
        },
        {
          "elmType": "span",
          "txtContent": "Active"
        }
      ]
    }
  ]
}

Yes (1) → Red Delete button with trash icon
No (0) → Green Active badge with checkmark

6. Can External Users Access Your Power App?

Yes — but with specific requirements:

  • External users must have a Microsoft Work or School account — personal Gmail or Outlook.com accounts will not work
  • The guest user must have a Power Apps license assigned
  • Guest users cannot be licensed directly in Admin Center — create an Entra security group, add guests to it, and assign the license to the group
  • Sharing a canvas app with a guest does NOT automatically grant access to data sources — you must grant SharePoint list permissions to external users separately

7. UML Diagrams for Your SharePoint + Power Automate System

Class Diagram (PlantUML)

@startuml

note as N1 #orange
  Power Automate Flows:
  - Department Limit Enforcer
  - Folder + Permission Creator
  - Role Change Propagator
end note

class WorkforceMember {
  + memberId : string
  + department : string
  + role : string
  + isActive : boolean
  + changeRole()
}

class Customer {
  + customerId : string
  + name : string
  + email : string
  + accessLevel : string
  + requestAccess()
}

class SharePointList {
  + listId : string
  + items : ListItem[]
}

N1 .. Customer
N1 .. WorkforceMember
WorkforceMember --> SharePointList : writes to
Customer --> SharePointList : reads from
WorkforceMember ..> Customer : linked to

@enduml

Use Case Diagram (PlantUML)

@startuml
left to right direction

actor Admin
actor "External user" as EU
actor "Power Automate" as PA

rectangle "SharePoint + Power Automate System" {
  usecase "Manage item access" as UC1
  usecase "Break role inheritance" as UC2
  usecase "Remove user permission" as UC3
  usecase "Trigger flow on item delete" as UC4
  usecase "Log item data" as UC5
  usecase "Notify user via email" as UC6
  usecase "Format list column (JSON)" as UC7
}

Admin --> UC1
Admin --> UC2
Admin --> UC3
Admin --> UC7
EU --> UC4
EU --> UC6
PA --> UC4
PA --> UC5
PA --> UC6

UC1 .> UC2 : <<include>>
UC2 .> UC3 : <<include>>
UC4 .> UC5 : <<extend>>
UC4 .> UC6 : <<extend>>

@enduml

Paste these into PlantUML Online or DotUML to render them.

Key Takeaways

  • Use Compose + Append to Variable to build dynamic HTML tables in Power Automate emails
  • Fix all SharePoint date formats at once via Regional Settings → English (United Kingdom)
  • Remove item permissions with three HTTP REST calls: break inheritance → ensureuser → DeleteObject
  • Use i:0#.f|membership|email prefix for external users — getbyemail fails silently for guests
  • The delete trigger cannot return item column data — use a Log List or Soft Delete pattern instead
  • SharePoint List Rules cannot trigger Power Automate flows
  • External users need a Power Apps license via an Entra security group
  • Format Yes/No columns as visual buttons using JSON column formatting

Related Topics

  • Power Automate HTTP actions with SharePoint REST API
  • Managing guest/external user access in Microsoft 365
  • SharePoint column formatting with JSON
  • PlantUML diagrams for Microsoft 365 systems

Ran into a different variation of these problems? Drop a comment below — happy to help troubleshoot your specific setup.

Want to automate your business? Book a Free Strategy Call