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.

Implement Search for a Specific Column of Document Library in SharePoint Online

Overview

We at Binary Republik recently implemented the intranet solution for an Engineering & Construction company having headquarters in Boston, Massachusetts, United States. Implementing the tailored search for SharePoint Online - Modern Sites was also the part of requirements. The main purpose of the custom search implementation was to avail the results from a specific Document Library that will target the query on a specific column only.

Example

Let’s consider an example of one of the Document Libraries on our SharePoint site. Let’s say if we have a custom property for our document and when we search the document with that property it should find the document with its value. We want to implement a custom search for a specific document library column. Then how can we implement this?

So, now let’s check the overall procedure.

1.     Create custom properties for the column we wish to configure search.

2.     Go to site setting of the site.

From Site Collection Administration, go to Search Schema. We will be able to see all the manage properties of the site.

URL: https://yourtenant.sharepoint.com/sites/SiteName/_layouts/15/listmanagedproperties.aspx?level=sitecol

3.     To enable column search, we need to create a new custom property.

4.     Select New Managed Property.

5.     Give Name, Description (optional), and Type for the property.

6.    Check the checkbox for Searchable and also for Queryable. This is used the enable the column searches and enable the query for that column.

 7. Check the checkbox for Retrievable for retrieving the column in search call. If we need multiple values from this column, we can check the checkbox "Allow multiple values".

8.  Set the Alias to this property. We can also search based on this alias name instead of the property name.

9. We have to map this managed property with the Crawled Property (the column created in the Document Library should appear in the Crawled Properties). Click on "Add a Mapping".

 

10.  After that search the column name and click on the "Find" button. We are able to see all properties related to the column name, select the appropriate one and click "OK".

11.  Once we add the property, we will be able to see on the below screen.

So, this is how we can configure, managed property for a SharePoint - Document Library column.

Important Notes

·       If we want to implement search on multiple columns, then we need to repeat steps 1 to 11 for each property.

·       After creating custom properties that’s column will be searchable. This will take around 24 hours to crawl this column.

Usability of the Property

E.g.: We need to search "02265" cell phone number. So, we add this number with 'querytext' and we also use 'selectproperties' to select our "Cell Phone". It will search the cell phone number and as result, it will show in cell phone column value.

https://yourtenant.sharepoint.com/Sites/SiteName/_api/search/query?querytext='CellPhone:02265'

We can use the following syntax to search in a different manner. We can integrate the same Search API in the SPFx webparts or custom solutions to be implemented.

Conclusion

This is how we can configure the search to target the specific column of SharePoint Document Library!

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