Create site columns, lists and views in SharePoint using MS Flow/Power Automate — Read from excel

I have wished if MS Flow (Power Automate) had direct options to create a SharePoint list and create and add some site columns to the list and further add these columns to the default view as well. But unfortunately, they don't!

Still there is option to achieve these by sending http requests to SharePoint. After going through bits and pieces of many different blogs that has helped me achieve success through different steps, below are the steps I followed with some screen shots.

1: Click on create on the power automate page which displays the different ways to make a flow.

2. Click on Instant Flow (It can be Automated or Scheduled or whichever based on your requirement)

I have created one flow for site column creation and one for list creation separately since the execution of site column flow might not be as frequent as list creation.

3. Steps to create site columns

a. Provide a variable name ‘Site URL’ to be given as input when you run the flow.

b. Add a new step ‘ List rows present in a table’ and provide the location of the excel file. The excel data to be retrieved should be formatted as a table for this step to recognize the data.

c. Add next step ‘Apply to each’ on the values retrieved from the excel table and select ‘value’ as input to this step.

d. Arrange the data in excel as below for the flow to read columns.

e. Add step ‘Send an HTTP request to SharePoint’

URI : /_api/web/fields

Body : { ‘__metadata’: { ‘type’: ‘SP.Field’ },
‘Title’: ‘@{items(‘Apply_to_each’)?[‘Field Name’]}’,
‘FieldTypeKind’: @{items(‘Apply_to_each’)?[‘FieldTypeKind’]},
‘Group’: ‘Custom Columns’
}

If the column to be created is a choice column, replace the body with

{ ‘__metadata’: { ‘type’: ‘SP.FieldChoice’ },
‘Title’: ‘@{items(‘Apply_to_each’)?[‘Field Name’]}’,
‘FieldTypeKind’: @{items(‘Apply_to_each’)?[‘FieldTypeKind’]},
‘Group’: ‘Custom Columns’,
‘Choices’: { ‘results’: [@{items(‘Apply_to_each’)?[‘Values’]}] }
}

Add a condition like below if it has choice as well as other kinds of columns and then add the HTTP request steps inside it.

4. Steps to create a list, list columns from site columns and add them to the default view

a. Add step ‘Send an HTTP request to SharePoint’ to create the list

URI : /_api/web/lists

Body : {‘__metadata’: { ‘type’: ‘SP.List’ }, ‘AllowContentTypes’: true,’BaseTemplate’: 100, ‘ContentTypesEnabled’: true, ‘Description’: ‘Your List Description’, ‘Title’: ‘Your List Name’}

b. Initialize a variable ‘SchemaXML’

c. Get the table from excel containing the field details as below

d. Set value of Schema XML . Field Type can be set based on requirement or picked up from a column in the excel table.

e. Add these columns to the list

URI : /_api/web/lists/GetByTitle(‘ListName’)/fields/createfieldasxml

Body : {“parameters” : {
“__metadata” : { “type” : “SP.XmlSchemaFieldCreationInformation”},
“SchemaXml”: ‘@{variables(‘SchemaXML’)}’
}}

f. To add to default view -

i. Compose and parse json of output from previous step

The schema for parse json can be provided using ‘Generate from sample’ where the output from previous step can be provided and the schema will be automatically generated.

To obtain the output, run the flow manually and expand the step of the flow (from run history)

ii. Use the internal name obtained from parse json add send an http request

URI : /_api/web/lists/GetByTitle(‘ListName’)//Views/GetByTitle(‘All%20Items’)/ViewFields/addViewField(‘@{body(‘Parse_JSON’)?[‘body’]?[‘d’]?[‘InternalName’]}’)

The site columns, the list creation and adding the site columns to the list and further adding them to the default view can be achieved by following these steps above using power automate.

Hope this was of help! Feel free to drop in suggestions if this can be achieved in a better way. Thanks!