#PowerPlatformTip 161: 'Clone a SharePoint List's Custom Columns to Another Site'

TL;DR: GET .../fields returns each column’s SchemaXml. Filter to custom fields (Hidden eq false and ReadOnlyField eq false and CanBeDeleted eq true), then POST that SchemaXml to .../fields/createfieldasxml on the target list to clone the columns 1:1.

As a consultant you often build a solution on a dev or demo site and then need to reproduce the exact same list schema at the customer. Rebuilding columns by hand is slow and error-prone - a single mismatched internal name breaks your Power Apps and flows. SharePoint already stores every column as XML, so you can read that blueprint from one list and stamp it onto another, fully automated.

💡 Challenge

You have a working SharePoint list with a dozen carefully configured columns (choice, lookup, calculated, formatted). You need the identical columns - same internal names, same settings - on a different site or tenant. Manually recreating them is tedious and drifts easily, which later breaks apps and flows that rely on those exact internal names.

✅ Solution

Every SharePoint field exposes a SchemaXml property - the complete definition of that column. The plan is: read all fields of the source list, filter to the ones you actually created (excluding built-in/system columns), and recreate each one on the target list by posting its SchemaXml to createfieldasxml. Everything runs through the standard Send an HTTP request to SharePoint action - no premium connector.

🔧 How It’s Done

1. Read all columns of the source list and keep only the custom ones.

🔸 Send an HTTP request to SharePoint (source site) → Method GET, Uri:

_api/web/lists/getbytitle('Projects')/fields?$select=Title,InternalName,Hidden,ReadOnlyField,CanBeDeleted,FromBaseType,SchemaXml&$filter=Hidden eq false and ReadOnlyField eq false and CanBeDeleted eq true and FromBaseType eq false

🔸 Header Accept: application/json;odata=nometadata. The $filter strips out system columns: built-in fields have CanBeDeleted = false and FromBaseType = true, while the columns you added come back with CanBeDeleted = true and FromBaseType = false. The response value array now holds only your custom columns, each with its full SchemaXml.

2. (Optional) Inspect / store the blueprint.

🔸 The array of SchemaXml strings is your portable list definition. Save it to a variable, a file, or a config list so you can redeploy it anytime - this is the reusable asset for the next customer.

3. Loop the results and recreate each column on the target list.

🔸 Apply to each over the returned value array. Inside, add a Send an HTTP request to SharePoint (target site) → Method POST, Uri:

_api/web/lists/getbytitle('Projects')/fields/createfieldasxml

🔸 Headers Accept: application/json;odata=nometadata, Content-Type: application/json;odata=verbose, Body:

{
  "parameters": {
    "__metadata": { "type": "SP.XmlSchemaFieldCreationInformation" },
    "SchemaXml": "@{replace(items('Apply_to_each')?['SchemaXml'], '"', '\"')}",
    "Options": 24
  }
}

🔸 Escape the quotes: SchemaXml contains double quotes (for example <Field Type="Text" ...>). Inserted unchanged into the JSON string above, they end the string early and the request body becomes invalid. The replace() call turns every " into \" so the body stays valid JSON.

🔸 Options is a bitwise AddFieldOptions value: AddFieldInternalNameHint (8) preserves the original internal name carried in the SchemaXml, and AddFieldToDefaultView (16) adds the column to the default view - 8 + 16 = 24. Other flags: AddToDefaultContentType = 1, AddToAllContentTypes = 4, AddFieldCheckDisplayName = 32. Use 0 for defaults only.

4. Create the target list first (if it doesn’t exist yet).

🔸 One-time POST to _api/web/lists with a SP.List body (BaseTemplate: 100 for a generic list) before the loop, so the columns have somewhere to land.

📦 Extension 1: Store the blueprint as a JSON file and import it in a second flow

If export and import must run in separate flows (or in separate tenants), turn step 2 into a real file.

🔸 Export flow: after the GET, add Create file (SharePoint connector) in a document library, for example Blueprint_Projects.json. As File Content use body('Send_an_HTTP_request_to_SharePoint')?['value'] (the array with the custom columns and their SchemaXml).

🔸 Import flow: Get file content (path to the JSON file), then Parse JSON or json(body('Get_file_content')). Loop over the resulting array and call createfieldasxml exactly as in step 3, using items('Apply_to_each')?['SchemaXml'].

🔸 For another tenant, copy the file to a library the import flow can read (download and upload it, or use a library in the target tenant). The import flow only needs the file, not access to the source site.

🔐 Extension 2 (optional): Read and transfer list permissions

Permissions are not part of the column SchemaXml. They are a separate step.

🔸 Read: .../lists/getbytitle('Projects')/roleassignments returns the role assignments of the list. To get the principal and its permission level in one call, $expand=Member,RoleDefinitionBindings is the usual approach.

🔸 Set on the target: the Microsoft Learn REST example for lists uses breakroleinheritance(true) (keeps the current assignments), roleassignments/getbyprincipalid(<id>) with X-HTTP-Method: DELETE to remove an assignment, and roleassignments/addroleassignment(principalid=<id>,roledefid=<id>) to add one. The role definition ID comes from roledefinitions/getbyname('Contribute')/id, the group ID from sitegroups/getbyname('Group A')/id. Details and the item-level version are in #PowerPlatformTip 160.

🔸 Important - IDs differ per site: principal IDs (groups and users) and, for custom permission levels, role definition IDs are site-specific. Do not reuse the numeric IDs from the source. Store the name in the blueprint (Title of the group, LoginName of the user, name of the permission level) and resolve it on the target: sitegroups/getbyname(...) for groups, _api/web/ensureuser with the LoginName for users (as in Tip 160), roledefinitions/getbyname(...) for permission levels. In another tenant, user login names usually differ as well, so you need a mapping table.

🎉 Result

The target list ends up with the same custom columns as the source - identical internal names and settings - created automatically. Your Power Apps and flows that reference those internal names keep working unchanged, and you have a reusable schema blueprint to deploy the solution at the next customer in minutes instead of hours.

🌟 Key Advantages

🔸 Portable blueprint: the SchemaXml array is a self-contained list definition you can version, store, and redeploy anywhere.

🔸 Exact fidelity: internal names and column settings are cloned 1:1, so downstream apps and flows don’t break.

🔸 Standard action only: Send an HTTP request to SharePoint - no premium connector, fully automatable.

🔸 Consultant-friendly: turn a one-off build into a repeatable deployment asset across sites and tenants.

🛠️ FAQ

1. How do I reliably tell custom columns from built-in ones?

Built-in/system columns come back with CanBeDeleted = false and usually FromBaseType = true. Columns you created have CanBeDeleted = true and FromBaseType = false. Combine both in the $filter (plus Hidden eq false and ReadOnlyField eq false) to isolate exactly the fields you added.

2. Do lookup columns clone cleanly?

Lookup fields reference a target list by ID (List="{GUID}" inside the SchemaXml), and that GUID differs on the new site. Create the referenced list first, then fix up the List (and WebId) attributes in the SchemaXml before posting - or recreate lookups in a dedicated second pass once all base lists exist.

3. What if a column with that internal name already exists on the target?

createfieldasxml will fail on a duplicate internal name. Either target a fresh list, or GET the target’s fields first and skip any InternalName that already exists so the flow is safely re-runnable.

4. Can I do this without Power Automate?

Yes - the same REST endpoints work from PnP PowerShell (Get-PnPField / Add-PnPFieldFromXml) or CSOM (AddFieldAsXml(schemaXml, addToDefaultView, options)). The flow approach just keeps it low-code and auth-free.

5. Why can’t I just copy the permission IDs from the source site?

Principal IDs (users and groups) are assigned per site, so the same group has a different ID on the target. Store names (group title, user login name, permission level name) in the blueprint and look up the IDs on the target site before calling addroleassignment.

🔗 Related Tips

• #PowerPlatformTip 160: Set SharePoint Permissions with Power Automate, breakroleinheritance and addroleassignment in detail, including how to resolve a user to a principal ID.
• #PowerPlatformTip 47: Batch Requests, send many field-creation calls in a single request for faster provisioning.
• #PowerPlatformTip 95: Optimized SharePoint Queries, get more out of $select and $filter on the SharePoint REST API.
• #PowerPlatformTip 123: Work with SharePoint IDs, understand the IDs and GUIDs you’ll remap when cloning lookups.