Create SharePoint List Lookup Column Using Power Automate

Last week, while working on creating a SharePoint list and column from Excel flow, I needed to create a lookup column using Power Automate dynamically. At first, it seemed tricky since there’s no direct action for creating lookup columns. However, after some research and testing, I found a way to make it work using the Send an HTTP request to SharePoint action.

In this tutorial, I will explain how to create SharePoint list lookup Column using Power Automate. Also, I will tell you how to create a SharePoint library lookup column using Power Automate.

Create SharePoint List Lookup Column Using Power Automate

Imagine you have two SharePoint lists:

1. Departments List
   • Department ID (Title column, Single line of text)
   • Department Name (Single line of text)
2. Employees List
   • Employee Name (Person or Group)
   • Department (Lookup column referencing the ‘Department Name’ in the ‘Departments List’)

Suppose you want to use Power Automate to create a lookup column in the Employees List that references the Department Name from the Departments List. This lookup ensures users can select a department from a predefined list when adding an employee rather than manually entering it.

Now, follow the steps below:

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 Name of the SharePoint list.
• Lookup List Name (Text): Enter the Lookup List Name of the SharePoint list.
• Lookup Column Name (Text): Enter the Lookup Column Name of the SharePoint list.

2. Add the Send an HTTP request to SharePoint action to get the sharepoint list ID. Provide the action with the following parameters:

• Site Address: Select the SharePoint Site Address from the dynamic content.
• Method: Select GET as the method
• URI: Provide the below URI:

_api/web/lists/getbytitle('@{triggerBody()?['text_5']}')

This step is required because SharePoint internally identifies lists by their unique GUID (Globally Unique Identifier) rather than their display name. Using the List ID ensures that our automation works even if the list name changes in the future.

3. Add the Send an HTTP request to SharePoint action to create a lookup Column. Provide the action with the following parameters:

• Site Address: Select the SharePoint Site Address from the dynamic content.
• Method: Select POST as the method
• URI: Provide the below URI:

_api/web/Lists/getByTitle('@{triggerBody()?['text_1']}')/fields/addfield

• Header: Use the headers below to make REST API calls to SharePoint:

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

• Body:

{
  'parameters': {
    '__metadata': {
      'type': 'SP.FieldCreationInformation'
    },
    'FieldTypeKind': 7,
    'Title': '@{triggerBody()?['text_2']}',
    'LookupListId': '@{body('Send_an_HTTP_request_to_SharePoint')?['d']?['Id']}',
    'LookupFieldName': '@{triggerBody()?['text_3']}'
  }
}

This step creates the lookup column in SharePoint by requesting a REST API. The Send an HTTP request to SharePoint action allows us to define the column properties, including:

• FieldTypeKind: 7 -> Specifies that it’s a lookup column.
• Title -> The name of the lookup column in the target list.
• LookupListId -> Connects this column to another list using the List ID obtained in the previous step.
• LookupFieldName -> Specifies which field from the source list should be used for the lookup.

Run the Flow to Create a SharePoint List Lookup Column

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, Lookup Column Name, and Lookup List Name in the input fields.

Click Run flow and wait for execution. After the flow runs successfully, go to that SharePoint list (Employees). Then, you can see the lookup column created.

Create a SharePoint Library Lookup Column Using Power Automate

The steps for creating a lookup column in a SharePoint document library using Power Automate are the same as creating a lookup column in a SharePoint list. You can follow the above steps.

Conclusion

In this tutorial, I showed you how to create a lookup column in a SharePoint list using Power Automate, even though there’s no direct action. We used the Send an HTTP request to SharePoint action first to get the List ID and then create the lookup column dynamically.

I also explained why each step is necessary to ensure the lookup column correctly links one list to another. Additionally, we learned that the same approach applies to creating a lookup column in a SharePoint document library.

Related Power Automate tutorials:

• Check IF Date is Today Using Power Automate
• Check If the Column is Changed in Power Automate
• Get SharePoint List Column Details Using Power Automate
• Check If SharePoint List Column Equals in Power Automate
• Disable Include Time Option in SharePoint List Date Time Column Using Power Automate