Replicate SharePoint Lists with Lookup Lists Automatically using Power Automate

Challenge:

Recently, we faced the challenge of duplicating a SharePoint site schema, which included lists and libraries with lookup columns. The task was to replicate the structure of a site seamlessly, but there was no simple, automated way to do this using Power Automate. Handling lookup columns added extra complexity to the process.

Solution:

After exploring different options, we came up with a solution using Microsoft SharePoint internal http calls. By using the internal APIs, we were able to copy the lists, including their lookup lists, while keeping the relationships between the lists properly connected.

 

Step 1: Get an existing template list and get its site script

Action: Send an HTTP request to SharePoint

Site Address: Use the source site URL

Set the request method to POST.

Endpoint URL:

_api/Microsoft.Sharepoint.Utilities.WebTemplateExtensions.SiteScriptUtility.GetSiteScriptFromList()

to retrieve the script for the existing list.

This API retrieves the site script for the specified list.

The request body:

{"listUrl":"Source_ListURL"}

The HTTP request body provides the list URL to the SharePoint API, which retrieves the site script containing the schema, columns, content types, and settings needed to duplicate the list.

Step 2: Parse and Structure the HTTP Response Data

After receiving the response from the HTTP request in Step 1, the next step is to extract and use the data.

Action: Parse JSON

Input: Use the Body of the HTTP response from the previous action.

In Power Automate, this is typically accessed using dynamic content like

outputs('Send_an_HTTP_Request_to_SharePoint')?['body']

Schema: Generate the schema for the Parse JSON action by providing a sample of the response body.

 

Step 3: Process the Response with Compose

 In this step, the Compose action processes the HTTP response by removing the $schema property from the list script, preparing it for further use.

 

 

Step 4: Create the List on the Destination Site

In this final step, the list is created on the destination site using the HTTP Request action.

Site Address: Use the destination site URL

Endpoint URL:

_api/Microsoft.Sharepoint.Utilities.WebTemplateExtensions.SiteScriptUtility.ExecuteTemplateScript()

The request body:

 Use the output of the Compose action from Step 3.

Conclusion:

This API automatically creates the base list and its lookup lists with intact relationships and all formatting, including views. It significantly reduces the number of calls previously required to create a lookup list and bind it to the parent list. 

If you have any questions you can reach out our SharePoint Consulting team here.