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.

SharePoint Online - Content Types are not published to SharePoint Site from Content Type Hub

Introduction

We recently implemented an automated process to provision new Sites and apply a Site Design using Power Automate for a construction and engineering company based out of Massachusetts, United States. The Site Design included a process of creating new Lists and adding Content Types to the Lists. Here the Content Types are the ones that are published across all site collections from Content Type Hub. But in the recent past, Microsoft has switched content-type publishing approaches from a "Push Everywhere Model" to "Pull as Needed". So, what does this mean for us? To understand this, let's first look at how the old approach worked.

Problem Statement [Old Content Type Publishing Process]:

In the old process, the published content types in Content Type Hub were synced in all the site collections by Timer Job. There was no fixed sync duration - it took anywhere between a few hours to even 48 hours. This led to the issue of indefinitely waiting for a published content type to get synced to the site. Furthermore, unnecessary content types were also synced to the site collections.

To address these issues and to improve the performance and reliability of the sync, Microsoft has changed the way content type publishing worked. Let's now look at the new approach.

Resolution [New Content Type Publishing Process]:

The content type publishing will now sync to sites “on-demand”. Due to this, any solution dependent on waiting for a published content type to get automatically copied to all sites, stopped working. Thus, the users need to select or pull content types directly from the hub while adding them to lists and libraries. This approach solves the below issues:

• Reduces the copying of published content types to sites where they are not desired
• Adds published content types to a document library or list in a quicker way
• Reduces unnecessary resource consumption to sync all published content types

Currently, there are three different ways available to programmatically add published content types to the desired destinations as below:

• Site Scripts
• PnP
• CSOM

How to add published Content Types to any Site Collection programmatically using Site Scripts?

A new addContentTypesFromHub verb is available for use within SharePoint site script actions. This verb syncs content types from a content type hub to a specific site.

Example: 

 {  
  "verb": "addContentTypesFromHub",  
  "ids": ["0x01007CE30DD1206046628BAFD1C39A850120"]  
 }  

Note: Here ids can be an array of Content Type ID(s) that need to be synced.

Below is an example of Site Script to sync required Content Type(s) from a Content Type Hub to a specific site:

 {  
      "$schema": "https://developer.microsoft.com/json-schemas/sp/site-design-script-actions.schema.json",  
      "actions": [  
           {  
                "verb": "addContentTypesFromHub",  
                "ids": [  
                     "0x010100867E94987C6169418A7FB1B63A62AED5",  
                     "0x010100867E94987C6169418A7FB1B63A62AED501",  
                     "0x0120D520007912086EAFB3B24BA71AF502B342CDF8",  
                     "0x010100867E94987C6169418A7FB1B63A62AED502"  
                ]  
           }  
      ],  
      "version": 1  
 }  

We will explore PnP and CSOM approaches in the next part of this article.

Conclusion:

This is how we can change the approach to publish Content Type(s) from Content Type Hub to any SharePoint Site with a new approach. Hope this helps!

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