Create a SharePoint List and Column From Excel Using Power Automate [Download Complete Solution]

While working on a client project, I needed to create a SharePoint list with various columns, including Single Line of Text, Choice, Date and Time, Person or Group, Number, Yes/No, Hyperlink, Multiple Lines of Text, Currency, and Lookup.

One challenge I encountered was that the columns’ internal and display names differed, which often led to confusion when using them in Power Automate. After researching various approaches, I decided to automate the process using Power Automate with an HTTP request.

Download Power Automate Create SharePoint List & Column From Excel

If you want to explore more, you can download 50+ Power Platform solutions and try them in your own environment.

To make the automation more efficient, I added additional features:

• If the list already exists, delete it and create a new one.
• Handle errors gracefully and send an email notification if any issue occurs.
• If the process is successful, generate a log file in the SharePoint document library to confirm that all columns were created correctly.

In this tutorial, I will show you how to use Power Automate to create a SharePoint list and its columns from an Excel file.

Create a SharePoint List and Column From Excel Using Power Automate

We must create an Excel sheet before I tell you how to make it.

Display Name	Internal Name	Data Type	Is Mandatory	Choices	Default Value	Lookup Column Name	Lookup List Name
Name	Nmae	Single Line of Text	Yes
Status	Status	Choice	No	Pending, Approve, Rejected	Pending
Created On	CreatedOn	Date and Time	Yes
Assigned To	AssignedTo	Person or Group	No
Amount	Amount	Currency	No
Description	Description	Multiple Lines of Text	No
Is Active?	IsActive	Yes/No	No		No
Priority	Priority	Choice	No	High, Medium, Low	Medium
Department	Department	Lookup	No			DepartmentName	Departments
Website	Website	Hyperlink	No
Modified On	ModifiedOn	Date and Time	No
Employee ID	EmployeeID	Number	No

Where:

• Display Name: What users will see in SharePoint.
• Internal Name: Used internally in Power Automate and API requests.
• Data Type: Column type (Single Line of Text, Choice, Date and Time, etc.)
• Is Mandatory: Yes/No (whether the column is required).
• Choices: Applicable only if the column is a Choice column.
• Default Value: Default value if needed.
• Lookup Column Name: Name of the column from the lookup list.
• Lookup List Name: The name of the lookup list.

This structure will make it very easy for your Power Automate flow to:

1. Read columns
2. Create columns based on type
3. Handle required, choice, default value, and lookup settings dynamically.

To start, we must create an Instant Cloud Flow in Power Automate. This flow will take user input and dynamically create a SharePoint list with columns based on an Excel file.

1. Go to Power Automate and click Create -> Instant Cloud Flow. Select Manually trigger a flow as the trigger. Click + Add an input and add the following inputs:

• Site Address (Text): Enter the SharePoint site URL to create the list.
• List Name (Text): Enter the Display Name of the SharePoint list.
• Column Name (Text): Enter the Internal Name of the SharePoint list.
• Include Time (Yes/No): Choose whether to include the time in the date columns.
• Email (Email): Provide an email address to receive notifications if errors occur.

These inputs allow flexibility, so you can reuse the same flow to create multiple lists with different configurations. The Include Time input determines whether date columns store only the date or both the date and time.

Once the flow is triggered with the required inputs, the next step is to read the Excel file and clean up the data before creating the SharePoint list and columns.

2. I add a Try block to ensure error handling. Use List rows present in a table Action:

• Select Location (OneDrive or SharePoint where the Excel file is stored).
• Select Document Library and File Name (your Excel file).
• Select the Table Name (the table inside the Excel file that contains the column details).

3. Add a Compose Action to Clean Data and a Parse JSON action. Use the body from the Compose action as the input. Click Generate from Sample and paste the sample data from the Excel table to auto-generate the schema.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Display Name": {
                "type": "string"
            },
            "Internal Name": {
                "type": "string"
            },
            "Data Type": {
                "type": "string"
            },
            "Is Mandatory": {
                "type": "string"
            },
            "Choices": {
                "type": "string"
            },
            "Default Value": {
                "type": "string"
            },
            "Lookup Column Name": {
                "type": "string"
            },
            "Lookup List Name": {
                "type": "string"
            }
        },
        "required": [
            "Display Name",
            "Internal Name",
            "Data Type",
            "Is Mandatory",
            "Choices",
            "Default Value",
            "Lookup Column Name",
            "Lookup List Name"
        ]
    }
}

Parsing JSON converts the raw Excel data into a structured format, making it easier to extract each column’s details.

We must check if the SharePoint list exists before creating it to avoid errors. We can do this using the Send an HTTP request to SharePoint action in Power Automate.

4. Then add a Send an HTTP request to SharePoint action and use the following URI:

/_api/web/lists?$select=Title&$filter=Title eq '@{triggerBody()?['text_2']}'

5. We need to check if the HTTP request returned an empty response (meaning the list doesn’t exist). Use the following expression in the condition:

length(outputs('Send_an_HTTP_request_to_SharePoint')?['body'])

• If the length is 0 (List does NOT exist) -> Create a new SharePoint list.
  • Proceed with sending an HTTP request to create the list

• If the length is greater than 0 (List EXISTS) -> Delete and recreate the list.
  • First, send an HTTP request to delete the existing list.
  • After deletion, send another HTTP request to create the new list.

Now that we have created the SharePoint list, the next step is to dynamically add columns based on the Data Type specified in the Excel file.

6. Use the Data Type field (from the parsed JSON output) as the switch case condition. This will check the column type and create the correct column in SharePoint using an HTTP request.

Case 1: Single Line of Text

Body:

{
    "__metadata": { "type": "SP.FieldText" },
    "Title": "@{items('Apply_to_each')?['Display Name']}",
    "FieldTypeKind": 2
}

Case 2: Single Line of Text

Body:

{
    "__metadata": { "type": "SP.FieldChoice" },
    "Title": "@{items('Apply_to_each')?['Display Name']}",
    "FieldTypeKind": 6,
    "Choices": {
        "results": ["Choice1", "Choice2", "Choice3"]
    }
}

Case 3: Date and Time

Body:

{
    "__metadata": { "type": "SP.FieldDateTime" },
    "Title": "@{items('Apply_to_each')?['Display Name']}",
    "FieldTypeKind": 4
}

Case 4: Yes/No (Boolean)

Body:

{
    "__metadata": { "type": "SP.FieldBoolean" },
    "Title": "@{items('Apply_to_each')?['Display Name']}",
    "FieldTypeKind": 8
}

Case 5: Number

Body:

{
    "__metadata": { "type": "SP.FieldNumber" },
    "Title": "@{items('Apply_to_each')?['Display Name']}",
    "FieldTypeKind": 9
}

Case 6: Lookup Column

Body:

{
    "__metadata": { "type": "SP.FieldLookup" },
    "Title": "@{items('Apply_to_each')?['Display Name']}",
    "FieldTypeKind": 7,
    "LookupField": "@{items('Apply_to_each')?['Lookup Column Name']}",
    "LookupListId": "@{items('Apply_to_each')?['Lookup List Name']}"
}

Now that we have added the Switch Case to create columns dynamically, we need to handle any errors that occur during the process. We will use a Catch action inside a Scope to capture failed actions.

7. Then I add a catch action. Set it to run only if the Try action fails. Inside the Catch Scope, add a “Filter Array” Action. This will extract all failed, skipped, or timed-out actions from the Try block.

From:

result('Try')

Filter Query:

or(equals(item()?['Status'], 'Failed'), or(equals(item()?['Status'], 'Skipped'), equals(item()?['Status'], 'TimedOut')))

To make the error report more structured and readable, we will create an HTML table from the filtered error results.

8. Add a Create HTML table action:

• From: This takes the filtered error records from the Filter Array action.

body('Filter_array')

• Columns: Select Custom.
• Define the Custom Columns for the Table.

Header	Value
Action	@{item()?[‘name’]}
Status	@{item()?[‘Status’]}
ErrorMessage	@{item()?[‘error’]?[‘message’]}

9. Now that we have an HTML table, we can add it to the email body. To do this, add a Send an email action and provide the parameters below:

• To: @{triggerBody()?[’email’]}
• Subject: Error occurred in ‘Creating SP List from Excel Workflow’
• Body:

<p>Hello,</p>
<p>The following errors occurred while creating the SharePoint list and its columns:</p>
@{outputs('Create_HTML_table')}
<p>Please check the logs for more details.</p>

Run the Flow to Create a SharePoint List and Column From Excel

Now, save the flow -> Click Test (top-right corner) -> Select Manually and click Test again. Then, enter the SharePoint Site Address, List name, Column name, Email Address, and yes/no in the input fields.

Click “Run flow ” and wait for execution. After the flow runs successfully, navigate to Site Contents, and you will see that the list has been created successfully. Then open the list and check whether it was made.

Conclusion

In this tutorial, we automated the creation of a SharePoint list and its columns from an Excel file using Power Automate. We prepared an Excel file with column details, including display name, internal name, data type, and other settings.

Then, we built an Instant Cloud Flow to read the Excel data, check if the SharePoint list exists, and delete and recreate it if necessary. We dynamically created column types such as Single Line of Text, Choice, Date and Time, Person, Number, Yes/No, Hyperlink, Currency, and Lookup using an HTTP request.

We also implemented error handling by capturing failed actions, generating an HTML error table, and sending email notifications in the event of issues. Finally, we tested the flow to ensure the list and columns were created successfully.

Also, you may like some more Power Automate tutorials:

• Expense Reimbursement and Approval using Power Automate
• Create a PDF From SharePoint List Items Using Power Automate
• Send Email Reminders From a SharePoint List Using Power Automate
• Get SharePoint List Attachments Using Power Automate REST API
• Read Excel File & Add Bulk Data to SharePoint List