Build a SharePoint Schema Builder Flow
Why I built this
A five-action Power Automate flow can hand you the full blueprint of any SharePoint list — every column's display name, internal name, type, whether it's required, and its choice values — in a table you can paste straight into Excel.
I keep running into the same project: a team has outgrown a SharePoint list and it's time to move to Dataverse. Before you build a single Dataverse table, you need an honest inventory of what's in that list. And the list settings page is a terrible place to get one.
Here's what I bumped into, over and over:
Display names lie. A column someone renamed to "Due Date" might still be called Field_7 or Deadline_x0020_Date behind the scenes. That internal name is what your migration mapping actually needs.
Choice values are buried. You have to click into each choice column, one at a time, and copy the options out by hand.
Types aren't obvious. "Person or Group", "Lookup", "Calculated" — each needs a different plan on the Dataverse side, and the settings page doesn't make it easy to see them all at once.
Hidden system columns you never created are hanging out in there, too.
Clicking through a 40-column list and typing all of this into a spreadsheet is slow, and it's easy to miss something. So I jumped in and figured out how to have SharePoint just tell me. Turns out SharePoint will happily describe itself if you ask nicely, and Power Automate (Microsoft Flow, for those of us who've been around a while) can do the asking. Now it takes about a minute per list, and the output becomes the starting point for designing the Dataverse table.
The flow at a glance
The whole thing is five steps, run from a button whenever you need it:
Manually trigger a flow — you click Run. No schedule, no automation, just on demand.
Send an HTTP request to SharePoint — asks the list to describe its columns.
Parse JSON — turns SharePoint's reply into fields you can click on in the flow designer.
Select — keeps only the five details we care about and tidies them up.
Create CSV table and Create HTML table — formats the results so you can copy them into Excel or read them in your browser.
That's it. No premium connectors, no custom code, nothing to install.
What you need before you start
Access to Power Automate (make.powerautomate.com) with a standard Microsoft 365 license.
At least read access to the SharePoint site and list you want to document.
The site address and the list's web address (more on finding those in Step 2).
Optional but recommended: build it inside a solution so you can export it and reuse it across environments. That's how I packaged mine.
Building it, step by step
For the examples below I'm using a made-up site at https://contoso.sharepoint.com/sites/ProjectHub with a list called Equipment Requests. Swap in your own.
Step 1: Start with a manual trigger
Create a new Instant cloud flow and pick Manually trigger a flow. Don't add any inputs. This is a tool you run when you need it, not something that should fire on its own.
Step 2: Ask SharePoint about the list's columns
Add the SharePoint action Send an HTTP request to SharePoint. Don't freak out about the "HTTP" part — this is a standard SharePoint action, and it uses the same connection as every other SharePoint action you've built. It just lets you ask questions the other actions don't have a button for.
Fill it in like this:
Setting | Value |
Site Address | https://contoso.sharepoint.com/sites/ProjectHub |
Method | GET |
Uri | see below |
Headers / Body | leave empty |
The Uri is the one line that does all the heavy lifting:
/_api/web/GetList('/sites/ProjectHub/Lists/Equipment%20Requests')/Fields?$select=Title,InternalName,TypeAsString,Required,Hidden,ChoicesLet's break that down in plain English:
/_api/web — "Hey SharePoint, I'm talking to this site."
GetList('/sites/ProjectHub/Lists/Equipment%20Requests') — "…and specifically this list." This is the list's address after the .sharepoint.com part. Easiest way to get it: open the list in your browser and copy that piece from the address bar. Spaces show up as %20, and that's what you want.
/Fields — "Tell me about your columns."
?$select=... — "…but only these six details." Without this, SharePoint sends back dozens of properties per column, and you'll be scrolling forever.
The six details we're asking for:
Property | What it tells you |
Title | The display name people see in the list |
InternalName | The name SharePoint uses behind the scenes (this is the one that matters for migration) |
TypeAsString | The column type: Text, Note, Choice, DateTime, User, Lookup, and so on |
Required | Whether the column must be filled in |
Hidden | Whether it's a hidden system column |
Choices | The list of options, for Choice columns |
Gotcha: use the list's web address, not its display name. If someone renamed the list, the two won't match. Pay attention to upper/lower case…it matters.
Step 3: Parse JSON
SharePoint answers in JSON, which is just structured text. On its own, the flow treats it like one big blob. Parse JSON teaches the flow what's inside, so the individual fields show up as dynamic content you can click on.
Content: the Body from the SharePoint step.
Schema: the easy way is to run the flow once with only Steps 1 and 2, copy the body from the run history, then click Use sample payload to generate schema and paste it in.
One thing I picked up along the way: paste only the body, not the whole output with headers and status code. My first version had the entire response baked into the schema, and it was a wall of header definitions that did nothing useful. If you'd rather skip the run-and-copy step, this trimmed schema does the job:
{
"type": "object",
"properties": {
"d": {
"type": "object",
"properties": {
"results": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Title": { "type": "string" },
"InternalName": { "type": "string" },
"TypeAsString": { "type": "string" },
"Required": { "type": "boolean" },
"Hidden": { "type": "boolean" },
"Choices": {
"type": "object",
"properties": {
"results": { "type": "array", "items": { "type": "string" } }
}
}
}
}
}
}
}
}
}Notice that d and results wrapper. For some reason, SharePoint wraps the real data two layers deep. You'll need to point at it in the next step.
Step 4: Select only what you need
The Select action (under Data Operations) takes a list of things and reshapes each one. Think of it as picking which columns you want in your spreadsheet and what to call them.
From: switch to the expression editor and enter body('Parse_JSON')?['d']?['results']
Map: add five rows:
Key | Value |
Title | item()?['Title'] |
InternalName | item()?['InternalName'] |
Type | item()?['TypeAsString'] |
Required | item()?['Required'] |
Choices | the expression below |
Choices is the only tricky one. SharePoint returns them as a list, and a list doesn't fit nicely in one spreadsheet cell. This expression turns it into a single line of text separated by semicolons, and leaves the cell blank for columns that aren't choices:
if(empty(item()?['Choices']?['results']), '', join(item()?['Choices']?['results'], '; '))Reading it out loud: if there are no choices, put nothing; otherwise, join them together with a semicolon and a space. A Status column comes out as New; In Review; Approved; Rejected. Easy-peasy.
Step 5: Turn it into tables
Add two more Data Operations actions, both using the Output of Select:
Create CSV table — gives you comma-separated text you can paste straight into Excel.
Create HTML table — gives you a tidy table you can read right in the run history, or drop into an email.
Leave the Columns setting on Automatic. Save, and you're done building.
Running it and using the results
Click Test, then Manually, then Run flow. When it finishes, open the run, expand Create CSV table, and copy the Output. Paste it into Excel, use Data > Text to Columns (comma-delimited) if it lands in one column, and you've got your list's blueprint.
Here's roughly what comes back for our sample list:
Title,InternalName,Type,Required,Choices
Title,Title,Text,true,
Requested By,RequestedBy,User,true,
Needed By,Deadline_x0020_Date,DateTime,false,
Status,Status,Choice,true,New; In Review; Approved; Rejected
Justification,Justification,Note,false,
Estimated Cost,EstCost,Currency,false,
Department,Department,Lookup,false,See "Needed By"? Its internal name is Deadline_x0020_Date because someone renamed it after it was created. That's exactly the kind of thing that bites you halfway through a migration.
From blueprint to Dataverse table
Add a column to your spreadsheet for the Dataverse column type, and work down the list. This is the mapping I start from — don't feel like it's the only way, but it covers most lists:
SharePoint Type | Dataverse column to consider | Watch out for |
Text | Single line of text | Check max lengths |
Note | Multiple lines of text | Rich text in SharePoint needs a rich text format column |
Number | Whole number or Decimal | Look at the data to decide |
Currency | Currency | Dataverse adds a currency lookup automatically |
DateTime | Date and time, or Date only | Pick the behavior on purpose — time zones get weird |
Boolean | Yes/No | — |
Choice | Choice | Paste the Choices cell straight into the new column's options |
MultiChoice | Choices | — |
User / UserMulti | Lookup to the User table, or text holding an email | People who left the company won't exist as users |
Lookup / LookupMulti | Lookup (or a many-to-many relationship) | Migrate the parent list first |
URL | URL format text | — |
Calculated | Formula column, or skip it | Rebuild the formula instead of copying values |
The Required column tells you which Dataverse columns should be business-required, and the InternalName column becomes the key for mapping fields in your migration, whether that's a dataflow, a flow, or an import.
Gotchas and ways to make it better
The version I use is intentionally bare-bones. A few things I've noticed, and a few upgrades worth trying:
Filter out the system columns. By default you'll get a bunch of hidden and read-only columns (things like ContentTypeId, _UIVersionString, Attachments) that you won't migrate. Add a filter to the end of the Uri: &$filter=Hidden eq false and ReadOnlyField eq false. Keep in mind that drops Created, Modified, Created By and Modified By too — if you need to carry those over, plan for them separately.
Stop editing the flow for every list. Mine has the site and list hard-coded, which means I edit two fields every time. Add two text inputs to the trigger (Site Address and List Path) and use them in the HTTP step. Now anyone can run it without opening the designer.
Get the output somewhere useful. Right now the tables only live in the run history. Add Send an email (V2) with the HTML table in the body, or Create file in OneDrive with the CSV output named something like Equipment Requests-schema.csv.
Watch the 403s. If the HTTP step fails with a permission error, the account behind the SharePoint connection can't read the list. Mine is set so the person running it uses their own connection, which means whoever clicks Run needs at least read access to that list.
Lookups need a little extra digging. The flow tells you a column is a Lookup, but not which list it points to. Add LookupList to the $select if you want the target list's ID in the output too.
And if you want to go further: loop through every list on a site and build a schema file for each one. That turns a multi-list migration into a single run.
Wrapping up
This one was fun. Five actions, no premium licensing, and a chore I used to dread became a one-minute task. More importantly, it means every Dataverse table I design starts from what's actually in the list, not what I think is in it.
If you're staring down a SharePoint-to-Dataverse migration, build this first. Jump in, break things, and tweak it to fit how you work. I hope this helps someone out there battling a similar scenario.
Try. Fail. Learn.
Comments