Stop Creating SharePoint Site Columns Manually – Use Power Automate Flow [6 Column Types Covered]

If you’ve ever had to set up a new SharePoint site and create 20 or 30 site columns by hand, clicking through Site Settings, filling in display names, internal names, types, and groups, you know exactly how tedious that gets. One typo in an internal column name and you’re stuck with it forever (or dealing with a messy cleanup).

I ran into this exact situation during a project where we needed to provision the same set of site columns across multiple SharePoint sites. Doing it manually wasn’t an option. So I built a Power Automate flow that reads column definitions from an Excel file and creates all the site columns automatically — including six different column types.

In this tutorial, I’ll walk you through the exact flow I built, step by step. You’ll end up with a reusable solution you can adapt for any site provisioning project.

Before You Start — Quick Checklist

Make sure you have these things in place before building the flow:

•  Your Excel file is saved in SharePoint or OneDrive (not on your local drive — Power Automate can’t reach it there)
•  The Excel data is formatted as a Table (not just a plain range) — go to Insert > Table in Excel to convert it
•  You have Site Collection Administrator rights on the target SharePoint site
•  You have a Power Automate Premium license (the “Send an HTTP request to SharePoint” action requires it)
•  Internal column names in your Excel file contain no spaces or special characters — use underscores instead
•  Choice values in Excel are comma-separated with no trailing spaces, like: Option1, Option2, Option3

If you’re not sure whether you need a Premium license, check with your Microsoft 365 admin. Without it, the HTTP connector won’t work.

Is This the Right Approach for You?

Before diving in, it’s worth making sure this solution fits your situation. Here’s how I’d think about it:

Use this Power Automate + Excel approach if:

• You need to create site columns on one or a few site collections
• You want a no-code or low-code solution that doesn’t require PowerShell
• You’re provisioning columns occasionally (not as part of a large deployment pipeline)

Consider PnP PowerShell or PnP Provisioning Templates instead if:

• You’re deploying across dozens of site collections
• You need version-controlled, repeatable deployments
• You’re working in a DevOps pipeline

Just create list columns directly in the UI if:

• The columns are unique to a single list and won’t be reused anywhere else

For everything else, this flow is a solid, practical approach.

Create SharePoint Site Columns Using Power Automate Flow

The flow reads each row from an Excel table, figures out what type of column it is, and calls the SharePoint REST API to create that column as a site column (meaning it lives at the site level and can be reused across lists and content types).

We’ll create these six column types:

• Single Line of Text
• Date and Time
• Choice
• Person or Group
• Hyperlink
• Number

Here’s a high-level picture of how the flow works:

Read Excel rows
    ↓
Clean up JSON (remove @odata.etag and ItemInternalId)
    ↓
Parse JSON (make fields available as dynamic content)
    ↓
Loop through each row
    ↓
Check isMandatory column → Set variable (true/false)
    ↓
Switch on Data Type
    ↓
Send HTTP request to SharePoint REST API → Create site column

Step 1 — Set Up Your Excel File

Create an Excel file with these five columns, formatted as a Table:

A few things to note here:

• Internal Column Name: This is the StaticName used by SharePoint internally. Keep it PascalCase with no spaces.
• Data Type: Use exactly: Text, DateTime, Choice, Person, Hyperlink, Number — These are what our Switch action will match against.
• Choices: Only needed for Choice columns. Leave it blank for everything else.
• Is Mandatory: Use Y for required columns, N for optional ones.

Save this file to a SharePoint document library or OneDrive before building the flow.

Step 2 — Create the Flow

Log in to Power Automate at make.powerautomate.com. Click + Create in the left nav, then Instant cloud flow. Give it a name like “Create SharePoint Site Columns from Excel” and choose Manually trigger a flow as the trigger. Click Create.

Step 3 — Read Rows from Excel

Click + New step and search for List rows present in a table. Select the Excel Online (Business) connector.

Fill in:

• Location: Where the Excel file lives (OneDrive for Business or a SharePoint site)
• Document Library: The library the file is in
• File: Select your Excel file
• Table: Select the table name

Step 4 — Clean Up the JSON

Power Automate adds metadata properties like @odata.etag and ItemInternalId to the Excel output. The @ symbol in property names will break your flow later, so we need to strip these out.

Add a Compose action. In the Input field, switch to Expression mode and paste:

json(replace(string(outputs('List_rows_present_in_a_table')?['body/value']), '@odata.etag', 'odataetag'))

This converts the output to a string, replaces the problematic @ prefix, then converts it back to JSON.

From my experience: This step trips a lot of people up. If you skip it, your flow will fail with a vague JSON parsing error, and the error message won’t point you to the @odata.etag issue. The compose-and-replace trick here is the cleanest way to handle it without needing to build elaborate error handling.

Step 5 — Remove Unwanted Properties

Now we’ll strip out odataetag and ItemInternalId so they don’t end up in our column creation requests.

Add a Select action. Configure it like this:

• From: The output of the Compose action above
• Map: Click the T (Text) icon to switch to Map mode, then in the Expression tab paste:

removeProperty(removeProperty(item(), 'odataetag'), 'ItemInternalId')

This uses nested removeProperty() calls to eliminate both unwanted fields from each row.

Step 6 — Parse JSON

Add a Parse JSON action.

• Content: Select the output of the Select action from Dynamic Content
• Schema: Click Generate from sample and paste in a sample row from your Excel output (run the flow once up to this step to get the actual output, then copy it here)

After parsing, your Excel column names (Internal Column Name, Column Name, Data Type, etc.) will show up as Dynamic Content tokens you can use in later steps.

Step 7 — Initialize Variables

Add two Initialize variable actions (two separate steps):

We’ll set IsMandatory to true or false based on the Excel data, and populate Choice with the split array of choices for Choice-type columns.

Step 8 — Check If Column Is Required

Add a Condition action. This will automatically be wrapped in an Apply to each loop since we’re working with the Parse JSON output.

Configure the condition:

• Left value: Select isMandatory from the Parse JSON dynamic content
• Operator: is equal to
• Right value: Y

In the Yes branch, add a Set variable action:

• Name: IsMandatory
• Value: true

In the No branch, add a Set variable action:

• Name: IsMandatory
• Value: false

Step 9 — Add a Switch on Data Type

Still inside the Apply to each loop, add a Switch action after the Condition.

• On: Select Data Type from the Parse JSON dynamic content

Now we’ll add six cases — one for each column type.

Step 10 — The Six Column Cases

Each case uses the Send an HTTP request to SharePoint action. The Site Address, Method (POST), and URI (_api/web/fields) are the same for all six. Only the Body changes.

FieldTypeKind Reference

Before you build each case, it helps to understand what FieldTypeKind means. It’s a number that tells SharePoint’s REST API what type of column to create:

Case: Text (Single Line of Text)

Switch Case value: Text

Add a Send an HTTP request to SharePoint action:

• Site Address: Your SharePoint site URL
• Method: POST
• Uri: _api/web/fields
• Headers:

{
  "Content-Type": "application/json;odata=verbose"
}

• Body:

{
  "Title": "@{items('Apply_to_each')['Column Name']}",
  "FieldTypeKind": 2,
  "Required": @{toLower(variables('IsMandatory'))},
  "EnforceUniqueValues": false,
  "StaticName": "@{items('Apply_to_each')['Internal Column Name']}",
  "Group": "Employee Columns",
  "Hidden": false
}

From my experience: The Group property is what puts your columns into a named group in Site Settings. Always set a meaningful group name here — it makes the columns much easier to find and manage later.

Case: DateTime (Date and Time)

Switch Case value: DateTime

• Body:

{
  "Title": "@{items('Apply_to_each')['Column Name']}",
  "FieldTypeKind": 4,
  "Required": @{toLower(variables('IsMandatory'))},
  "StaticName": "@{items('Apply_to_each')['Internal Column Name']}",
  "Group": "Employee Columns"
}

Tip: By default, SharePoint creates this as a Date + Time field. If you want date-only, add "DateTimeCalendarType": 1 to the body.

Case: Person (Person or Group)

Switch Case value: Person

• Body:

{
  "Title": "@{items('Apply_to_each')['Column Name']}",
  "FieldTypeKind": 20,
  "Description": "Person or Group column",
  "Required": @{toLower(variables('IsMandatory'))},
  "EnforceUniqueValues": false,
  "Group": "Employee Columns",
  "StaticName": "@{items('Apply_to_each')['Internal Column Name']}"
}

Tip: If you want users to select multiple people, add "AllowMultipleValues": true to the body.

Case: Number

Switch Case value: Number

• Body:

{
  "Title": "@{items('Apply_to_each')['Column Name']}",
  "FieldTypeKind": 9,
  "Description": "Number column",
  "Required": @{toLower(variables('IsMandatory'))},
  "EnforceUniqueValues": false,
  "Group": "Employee Columns",
  "StaticName": "@{items('Apply_to_each')['Internal Column Name']}"
}

Case: Hyperlink

Switch Case value: Hyperlink

• Body:

{
  "Title": "@{items('Apply_to_each')['Column Name']}",
  "FieldTypeKind": 11,
  "Description": "Hyperlink column",
  "Required": @{toLower(variables('IsMandatory'))},
  "EnforceUniqueValues": false,
  "Group": "Employee Columns",
  "StaticName": "@{items('Apply_to_each')['Internal Column Name']}"
}

Case: Choice

The Choice column needs a little extra work. SharePoint expects the choices as an array, but in Excel, we stored them as a comma-separated string. So we need to split them first.

Still inside the Choice case:

1. Add a Compose action:

• Input: Select Choices from the Parse JSON dynamic content

2. Add a Set variable action:

• Name: Choice
• Value: Expression: split(outputs('Compose-choice'), ',')

From my experience: Choice columns break most often because of trailing spaces in the Excel Choices column. If your Excel value is HR, Finance, IT (note the space after the comma), the split results in " Finance" with a leading space. SharePoint creates the choice but it looks off and can cause issues in views and filters. Trim your choices in Excel before running the flow — no spaces around the commas.

3. Add a Send an HTTP request to SharePoint action:

• Body:

{
  "__metadata": { "type": "SP.FieldChoice" },
  "FieldTypeKind": 6,
  "Title": "@{items('Apply_to_each')['Column Name']}",
  "Choices": {
    "__metadata": { "type": "Collection(Edm.String)" },
    "results": @{variables('Choice')}
  },
  "Required": @{toLower(variables('IsMandatory'))},
  "EnforceUniqueValues": false,
  "Group": "Employee Columns",
  "StaticName": "@{items('Apply_to_each')['Internal Column Name']}"
}

The Choice column body is the most complex of the six. The key difference is the __metadata type (SP.FieldChoice) and the nested results array — both are required by the SharePoint REST API for choice fields.

Step 11 — Save and Test

Click Save, then click Test in the top right. Select Manually, then click Test again and hit Run flow.

Once it completes, go to your SharePoint site → Site Settings → Site Columns (under Web Designer Galleries). Look for the Employee Columns group — your new site columns should all be there.

Common Errors and Fixes

Check this common error you may face while creating this flow.

The flow runs successfully but no columns appear in SharePoint

Check that the Site Address in your HTTP request points to the root site collection, not a subsite. A common mistake is using https://yourtenant.sharepoint.com/sites/mysite/subsite when it should be https://yourtenant.sharepoint.com/sites/mysite.

Choice column is created but the choices are empty

This usually means the split() expression received an empty string because the Choices cell in Excel was blank for a non-Choice row. Add a condition before the Set variable step to only run the split if Choices is not empty.

401 Unauthorized error on the HTTP request

You’re hitting a licensing wall. The “Send an HTTP request to SharePoint” action requires a Power Automate Premium per-user or per-flow license. Standard Microsoft 365 plans don’t include it.

400 Bad Request — column already exists

The REST API returns a 400 if a site column with the same StaticName already exists. To handle this gracefully, add a GET request before creation to check if the field exists, then wrap the POST in a condition that only runs if it doesn’t. The GET URI would be: _api/web/fields?$filter=StaticName eq '@{items('Apply_to_each')['Internal Column Name']}'

“toLower is not a valid function” error

Make sure you’re using toLower() and not toLowercase(). The correct expression is @{toLower(variables('IsMandatory'))} in the body.

Data type case not matching in the Switch

The Switch in Power Automate is case-sensitive. If your Excel has text in lowercase but your Switch case is Text, it won’t match. Either normalize your Excel data to consistent casing, or use toLower() on the Data Type column in the Switch’s “On” field.

Extending This Flow

Once you have this working, here are some natural next steps:

Add more column types:

• Yes/No (Boolean): Use FieldTypeKind: 8. No extra metadata needed.
• Multi-line text (Note): Use FieldTypeKind: 3 and add "NumberOfLines": 6.
• Lookup columns: Use FieldTypeKind: 7 but you’ll also need to pass the LookupList GUID and LookupField name. Add a preliminary GET request to fetch the list GUID.

Make it multi-site:
Instead of a single site address, add a second column to your Excel file called “Site URL.” Then reference @{items('Apply_to_each')['Site URL']} in the Site Address field of the HTTP request. One flow, many sites.

Add error handling:
Wrap the HTTP action inside a Scope, then add a parallel branch with “Configure run after” set to run on failure. Log the error details to a SharePoint list or send yourself an email. This way, if one column fails, the flow logs it and keeps going rather than stopping entirely.

Add the column to a content type automatically:
After creating the site column, you can make a second HTTP POST to _api/web/contenttypes('{contentTypeId}')/fields to add the field to a content type. Useful for document management setups.

Also, you may like:

• Power Automate skip() Function
• Power Automate: Format Dates to ISO 8601
• Create a SharePoint Folder in Power Automate
• Power Automate Create an Array from JSON Objects

Frequently Asked Questions

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.