Org Chart Builder to SharePoint
Periodically pull all users and their managers via Office 365 Users, build a hierarchical org chart, and publish to SharePoint.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
FlowLibs - Org Chart Builder to SharePoint is a scheduled Power Automate cloud flow that snapshots the company's directory hierarchy each week and publishes the result to a SharePoint list. It walks the Office 365 Users connector, captures every user's display name, contact details, job title, department, office location, and resolved manager, then writes one SharePoint list row per user before sending a summary email to the operations contact.
The flow ships in the Off state so that each customer can authorize their own connections and tune the recurrence/recipients before turning it on. All user-tunable values are exposed as solution environment variables, so re-targeting the snapshot at a different SharePoint site or list requires no edits to the flow logic.
Use Case
HR and IT teams that need a recurring, point-in-time snapshot of the org structure for downstream analysis: license planning, manager-of-record audits, capacity reviews, or feeding a SharePoint web part / Power BI report with the latest people graph. The SharePoint list output makes the snapshot trivially queryable from Power BI, Power Apps, Power Automate, or a SharePoint page web part — without giving every consumer rights to call the Microsoft Graph directly.
The flow is ideal for teams that:
- Wire the SharePoint list to a Power BI report for a hierarchical org chart visual
- Add a second flow that diffs this week's snapshot against last week's to detect manager changes
- Surface the list inside a Power App as a searchable people directory
Flow Architecture
Weekly Org Chart Snapshot
RecurrenceRuns every Sunday at 06:00 UTC.
Init varSharePointSiteUrl
Initialize variableLoads the site URL from `flowlibs_SharePointSiteURL`.
Init varOrgChartListName
Initialize variableLoads the list display name from `flowlibs_OrgChartListName`.
Init varOrgChartListGuid
Initialize variableLoads the list GUID from `flowlibs_OrgChartListGuid`.
Init varRecipientEmail
Initialize variableLoads the recipient address from `flowlibs_RecipientEmail`.
Init varSnapshotDate
Initialize variableCaptures `utcNow()` so every row in the snapshot shares one timestamp.
Init varProcessedCount
Initialize variableInteger counter for the summary email.
Init varSearchTerm
Initialize variableEmpty string passed to `SearchUserV2` to return the full directory page.
Search All Active Users
Office 365 Users — SearchUserV2Pulls up to 100 active users in one call.
Apply To Each User
Foreach (concurrency 5)For each user in the search results: wraps the manager lookup in a Try Get Manager scope (Office 365 Users Manager_V2), composes the manager display name (or `"No Manager"`) and manager email (or empty string) based on the scope's status, composes a hierarchy level (`1` if the user has a manager, `0` if not), writes one row to the SharePoint org chart list via PostItem, and increments `varProcessedCount` whether the SharePoint write succeeded or failed. Runs with concurrency 5 for parallel processing.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_SharePointSiteURL | String | https://your-tenant.sharepoint.com | Site that hosts the org chart list. Set to the SharePoint site URL where the destination list lives. |
| flowlibs_OrgChartListName | String | FlowLibs - Org Chart | Display name of the SharePoint list (used in the summary email body). |
| flowlibs_OrgChartListGuid | String | <configure> | GUID of the destination SharePoint list, used by the SharePoint connector's `table` parameter. Replace with your list's actual GUID. |
| flowlibs_RecipientEmail | String | you@yourcompany.com | Address that receives the snapshot summary email. Can be a distribution list or shared mailbox. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Office 365 Users | shared_office365users | SearchUserV2 (Returns up to 100 active users in one call) Manager_V2 (Resolves the user's manager via /v1.0/users/{id}/manager) |
| SharePoint | shared_sharepointonline | PostItem (Writes one row per user to the org chart list) |
| Office 365 Outlook | shared_office365 | SendEmailV2 (Sends the snapshot summary email) |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Customization Guide
Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.
- Re-target the SharePoint site
- Update flowlibs_SharePointSiteURL and flowlibs_OrgChartListGuid to point at the new site + list. The list must have the column schema (Title, Email, JobTitle, Department, OfficeLocation, UserPrincipalName, ManagerName, ManagerEmail, HierarchyLevel, LastUpdated) before the flow runs.
- Rename or relocate the list
- Keep flowlibs_OrgChartListName aligned with the new display name; the list name is only used in the summary email body, so the GUID is what actually drives the write.
- Change the schedule
- Open the Weekly Org Chart Snapshot trigger and adjust the recurrence (frequency / weekDays / hour / time zone). The flow does not assume Sunday or 06:00 anywhere downstream.
- Add/remove user fields
- Extend the SharePoint list schema, then add matching keys inside the item object on Add User Row To SharePoint List. The SearchUserV2 response carries displayName, givenName, surname, mail, mobilePhone, businessPhones, jobTitle, department, officeLocation, userPrincipalName, and id out of the box.
- Scale beyond 100 users
- SearchUserV2 returns a skipToken when more results exist. Wrap the search in a Do Until that re-invokes with
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01Read SharePoint site URL from env var
Reads the SharePoint site URL solution environment variable.
EXPR.02Snapshot timestamp
Shared timestamp written to every row in the snapshot.
EXPR.03Current user fields in the loop
Per-iteration accessors for the user's display name, mail address, and ID inside Apply To Each User.
EXPR.04Manager display name (with fallback)
Canonical Logic Apps idiom for 'did the action inside this scope succeed?' — returns the manager's display name when the lookup succeeded, otherwise `"No Manager"`.
EXPR.05Manager email (with fallback)
Same scope-status pattern, returning the manager's email or an empty string if the Manager_V2 call returned 404.
EXPR.06Summary email subject
Builds the summary email subject with the snapshot date in `yyyy-MM-dd` form.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.