How to create SharePoint site columns from an Excel file in Power Automate?

In this Power Automate tutorial, I will explain to you, how to create SharePoint site columns from an Excel file in Power Automate. I will show you, how to create multiple types of site columns in SharePoint reading from an Excel file using Power Automate.

Recently I got a requirement to create site columns in SharePoint from Excel data automatically using Power Automate. In this flow, we will create multiple types of site columns:

• Single line of text
• Date Time
• Choice
• Person
• Hyperlink
• Number

So our Excel sheet contains below columns:

• Internal column name
• Column name
• Data type
• Is Mandatory
• Choices

And we will create the below Power Automate flow to create a Site column in SharePoint.

In this flow we are doing the below steps:

• We are reading rows from an Excel -> Then we are removing unwanted properties.
• Next, we are checking each row which column is required or not. Based on that we are setting variables like the column is Mandatory contains Y -> set true else N -> set false.
• Next using switch case we are creating 6 different types of site columns in SharePoint:
  • Single line of text
  • Date Time
  • Choice
  • Person
  • Hyperlink
  • Number

Create SharePoint site columns from an Excel file in Power Automate

Here we will see how to create site columns from an Excel file using Power Automate or Microsoft Flow.

Before we start creating the flow in Power Automate, just create the Excel sheet like the above screenshot.

Step 1: Log in to Power Automate, then click on +Create in the left navigation -> click on Instant Cloud Flow.

Then provide the flow name and select Manually trigger a flow. You can see Manually triggering a flow is added to the flow

Step 2: Click on the +New step, to get the data from an Excel file, select the ‘List rows present in a table action. Then provide the below information:

• Location: Select the location of the Excel file
• Document library: Select in which library the Excel file exists.
• File: Then select a file from the options
• Table: Then select the table.

Step 3: To remove @ from the ‘@odata.etag’ property name, then we will convert JSON to string then, we will replace ‘@odata.etag’ with ‘odataetag’. Once the replacement is done, then it will convert back to the JSON format using JSON (). So click on +New step and select Compose action. Then provide the below information:

• Input: Provide the below expression:

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

Step 4: Next, to remove the unwanted properties i.e. odataetag and ItemInternalId we will use removeProperties(). Click on the +New step button. Then select the “Select’ operation.

Provide the below information:

• From: Select the output of the above compose action from Dynamic content.
• Map: In map change the key value mode to Map by clicking on the Text icon. Then go to Expression, and write the below expression:

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

Step 5: Click on + New step button -> select Parse Json Action from the dynamic content. To add all the Excel values in dynamic content.

Then provide the below information:

• Content: Select the output of compose- remove unwanted properties
• Schema: To add schema, click on Generate from sample, then provide the output of compose -remove unwanted properties and click on done.

Step 6: Next, click on the +New step, and select Initialize variable action. Then initialize two variables like below

Variable name	Type
IsMandatory	String
Choice	Array

Step 7: Loop through the Excel rows and check isMandatory column, so, click on the +New step button -> select condition action.

Then provide the below information:

• Choose a value: Select isMandatory- parse json from dynamic content. This will automatically add Apply to each action.
• Operator: Select the operator as is equal to
• Choose a value: Write the value as Y

If the condition is true, then in the If Yes part -> click on Add an action -> select the Set variable action. Then provide the below information:

• Name: Select the name as IsMandatory
• Value: write the value as true

For If No part -> click on Add an action -> select the Set variable action. Then provide the below information:

• Name: Select the name as IsMandatory
• value: write the value as false

Step 8: Click on the +New step and select the ‘Switch‘ action. Here we will switch based on Data type. Provide the below information:

• On: Select the Data type column from the dynamic content. It will automatically add Apply to each action.

Now, we will create Six cases to create six types of Site columns in SharePoint Online.

Case – Single line of text

In this case, click on Add an action -> select ‘Send an HTTP request to SharePoint’. Then provide the below information:

• Site address: Provide or select the site address.
• Method: Select the POST method as we create the Site column.
• Uri: provide the URI below to create a site column in SharePoint

_api/web/fields

• Headers: Then provide the headers require to do the rest API call

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

• Body: Then provide the body containing the information about the Site column, required to create the site column.

{
    "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
}

Case – Date Time

In this case, click on Add an action -> select ‘Send an HTTP request to SharePoint’. Then provide the below information:

• Site address: Provide or select the site address.
• Method: Select the POST method as we create the Site column.
• Uri: provide the URI below to create site column in SharePoint

_api/web/fields

• Headers: Then provide the headers require to do the rest API call

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

• Body: Then provide the body containing the information about the Site column, required to create the site column.

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

Case – Person

In this case, click on Add an action -> select ‘Send an HTTP request to SharePoint’. Then provide the below information:

• Site address: Provide or select the site address.
• Method: Select the POST method as we create the Site column.
• Uri: provide the URI below to create a site column in SharePoint

_api/web/fields

Headers: Then provide the headers require to do the rest API call

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

Body: Then provide the body containing the information about the Site column, required to create the site column.

{
    "Title": "@{items('Apply_to_each')['Column Name']}",
    "FieldTypeKind": 20,
    "Description": "A Sample person or group field.",
    "Required": @{toLower(variables('isMandatory'))},
    "EnforceUniqueValues": false,
    "Group": "Employee Columns",
    "StaticName": "@{items('Apply_to_each')['Internal Column Name']}"
}

Case – Number

In this case, click on Add an action -> select ‘Send an HTTP request to SharePoint’. Then provide the below information:

• Site address: Provide or select the site address.
• Method: Select the POST method as we create the Site column.
• Uri: provide the URI below to create a site column in SharePoint.

_api/web/fields

• Headers: Then provide the headers require to do the rest API call.

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

• Body: Then provide the body containing the information about the Site column, required to create the site column.

{
    "Title": "@{items('Apply_to_each')['Column Name']}",
    "FieldTypeKind": 9,
    "Description": "A Sample number field.",
    "Required": @{toLower(variables('isMandatory'))},
    "EnforceUniqueValues": false,
    "Group": "Employee Columns",
    "StaticName": "@{items('Apply_to_each')['Internal Column Name']}"
}

Case – Hyperlink

In this case, click on Add an action -> select ‘Send an HTTP request to SharePoint‘. Then provide the below information:

• Site address: Provide or select the site address.
• Method: Select the POST method as we create the Site column.
• Uri: provide the URI below to create a site column in SharePoint

_api/web/fields

• Header: Then provide the headers required to do the rest API call.

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

• Body: Then provide the body containing the information about the Site column, required to create the site column.

{
    "Title": "@{items('Apply_to_each')['Column Name']}",
    "FieldTypeKind": 11,
    "Description": "A Sample hyperlink field.",
    "Required": @{toLower(variables('isMandatory'))},
    "EnforceUniqueValues": false,
    "Group": "Employee Columns",
    "StaticName": "@{items('Apply_to_each')['Internal Column Name']}"
}

Case – Choice

First, we need to convert the list of choices (string type) into an array, so we can pass it while creating the site column.

So click on Add an action, select Compose action-> then provide the below information:

• Input: Select Choices from the dynamic content

Next, click on Add an action -> select Set variable action, then provide the below information:

• Name: Select Choice from the dynamic content.
• Value: Then provide the below expression:

split(outputs('Compose-choice'), ',')

In this case, click on Add an action -> select ‘Send an HTTP request to SharePoint‘. Then provide the below information:

• Site address: Provide or select the site address.
• Method: Select the POST method as we create the Site column.
• Uri: provide the URI below to create a site column in SharePoint.

_api/web/fields

• Headers: Provide the below code to do rest API call.

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

• Body: Then provide the body containing the information about the Site column, required to create the site column.

{
    "__metadata": {
        "type": "SP.FieldChoice"
    },
    "FieldTypeKind": 6,
    "Title": "@{items('Apply_to_each')['Internal 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']}"
}

Step 9: To run the flow, click on save -> click on the Test icon -> select Manually -> click on the Test button -> click on Run flow (once your connector is configured) -> click on Done.

Now you can see your flow ran successfully:

Now if you Navigate to the Site columns page which is available in the Site setting -> Web design galleries. You can see the group gets created with the Employee column. Then a set of site columns also gets created.

Conclusion

In this Power Automate tutorial, we saw how to create SharePoint site columns from an Excel file using Power Automate. Also, we saw how we can create different types of site columns in SharePoint using a flow like:

• Single line of text
• Date Time
• Choice
• Person
• Hyperlink
• Number

You may like the following Power Automate tutorials:

• Add Item to SharePoint List from Excel using Power Automate
• Remove Duplicate items from SharePoint List using Power Automate
• Power Automate Multilevel Approvals
• Cannot find the resource for the request addroleassignhments
• How to Check If a row exists in Excel using Power Automate?