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 multiple column types such as Single line of text, Multiple lines of text, Choice, Multi-Choice, Date and Time, Person or Group, Multi-Person, Number, Yes/No, Hyperlink or Picture, Currency, Lookup, Image, and Location.

One challenge was handling different internal and display names for the same SharePoint column, so I automated the entire process using Power Automate and SharePoint HTTP requests.

Download Power Automate Create SharePoint List & Column From Excel

To make the solution more practical, I added a few extra features to the flow:

• If the list already exists, the flow can delete it and create it again based on the user’s choice.
• If something fails, the flow captures the failed actions and sends an email notification with error details.
• If the flow succeeds, it creates a log file in the SharePoint document library so you can verify what happened during execution.

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

Create a SharePoint List and Column From Excel Using Power Automate

Before building the flow, create an Excel template that stores both the SharePoint list settings and the column settings.

Excel template structure

The template contains list-level details such as List Internal Name, List Display Name, and Description. It also contains column-level details with the following headers:

• Display Name
• Internal Name
• Column Type
• Required
• Description
• Default Value
• Indexed
• Enforce Unique Values
• Append Only
• Fill In Choice
• Choices
• Display Format
• Selection Mode
• Currency Locale Id
• Lookup List Name
• Lookup Field Name

A sample template includes values like Employee Name, Comments, Priority, Due Date, Requested By, Assigned To, Quantity, Is Active, Website, Amount, Category, Product Image, and Office Location. This structure allows the flow to dynamically create SharePoint fields with the correct settings from Excel.

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.
• Replace existing list if it already exists? (Yes/No): Choose whether to replace the existing SharePoint list if it already exists.

These are the actual inputs used by the flow, and the SharePoint site address is later used in SharePoint actions through triggerBody()?[‘text’].

Once the flow is triggered with the required inputs, the next step is to read the Excel file, clean the data, and create 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, select the Excel file, and enter 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, Multiple lines of text, Choice, Multi-Choice, Date and Time, Person or Group, Multi-Person, Number, Yes/No, Hyperlink or Picture, Currency, Lookup, Image, 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