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
power automate create site columns from excel file
Sample Excel file

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
power automate create site columns from excel file

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.

How to create site columns from excel file in Power Automate

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

Create site columns from an Excel file in Power Automate

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.
Create site column from an Excel file in Power Automate

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'))
Create site columns from an Excel file using Power Automate

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.

See also  How to Use Power Automate trim() Function?

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')
Create site columns from an Excel file using Microsoft Flow

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.
power automate create site column from excel file

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

Variable nameType
IsMandatoryString
ChoiceArray
power automate create sharepoint site columns from excel file

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
power automate create sharepoint online site columns from excel file

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.
power automate create sharepoint online site columns from excel file

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
}
power automate create site columns from excel file

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"
}
power automate create site column from excel file

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']}"
}
power automate create sharepoint site columns from excel file

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']}"
}
power automate create sharepoint online site columns from excel file

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']}"
}
Create site columns from an Excel file in Power Automate

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.

See also  Power BI Slicer Buttons

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'), ',')
Create site column using Microsoft flow read from excel

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']}"
}
create site columns from excel file in Power Automate

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:

Create site columns from an Excel file using Power Automate

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.

how to create site column in SharePoint Online using flow from an excel file

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
See also  How to Use Power Automate trim() Function?

You may like the following Power Automate tutorials:

>