Synchronize two SharePoint lists using Power Automate - clone SharePoint list

1 Best way to clone SharePoint list: Synchronize two SharePoint lists using Power Automate

No comments

 187 total views,  9 views today

In this “Clone SharePoint list” article we will learn how to synchronize two SharePoint lists or duplicate SharePoint Online list using Power Automate which means if we add or edit an item in List A, this will be added to or edited in another list List B.

Business use case: Clone SharePoint list or Duplicate SharePoint Online list (Synchronize two SharePoint lists)

One of the use cases would be handling the security of sensitive items. For example, you are storing sensitive information of customers in a customer info list, here you do not want to expose specific fields like deal amount, and country to normal users and these will only be shown to board executive members. We can not make this security proof by hiding and showing the fields using JSON, PowerApps, or creating various views.

If you have a contribute permission level, you can yourself can expose these secured data. We can say this is one of the drawbacks of SharePoint when it comes to managing the permission for the secured data. You can read our previous article Security breach in SharePoint online conditionally show or hide columns.

Clone SharePoint list or Synchronize two SharePoint lists Step by step implementation

Prerequisites:

  • Create a Single line of text type site column “CustomUniqueID”.
  • Create two lists – ListA and ListB
  • Add the “CustomUniqueID” site column to both ListA and ListB

Note:

  • The purpose of having the unique column “CustomUniqueID” is to have a common unique column both in ListA and ListB, as the ID column value differs in ListA and ListB. While we copy a list item to another list, it will not carry the same ID. The destination list will maintain its own new IDs. So, we need to create another column to maintain a unique column in both lists.
  • If your existing lists maintain unique values for the title or any other column, then we don’t need this custom unique column. We can make use of any existing unique columns which you already have in your lists.

Clone SharePoint list or Duplicate SharePoint Online list (Synchronize two SharePoint lists) – Demo

Add an item in ListA.

Clone SharePoint list demo
Clone SharePoint list demo

After sometimes we can see the “CustomUniqueID” column has been updated with an alphanumeric value (CUSTUNIQUEID(ID column value)).

Once this “CustomUniqueID” column has been updated, let’s navigate to ListB, we can see that the same item has been created here. Similarly, if we edit the same item in ListA, this will be updated in ListB as well.

Item added/edited in ListB automatically – synchronize two sharepoint lists

Clone SharePoint list demo - item added to ListB automatically from ListA
Clone SharePoint list demo – item added to ListB automatically from ListA

Notes:

  • We shouldn’t display the “CustomUniqueID” column in the view both in ListA and ListB – for this demo to show how to handle it, we have displayed this.
  • And also, hide this “CustomUniqueID” column in the Add and Edit list form, using JSON we can easily hide the column, you can refer to our previous article for this – Conditionally show or hide columns in a SharePoint list .

Clone SharePoint list or Duplicate SharePoint Online list using Power Automate

The above clone SharePoint list or duplicate SharePoint Online list, we have implemented using Power Automate.

Power Automate:

Here is the complete Power Automate flow for this demo.

Clone SharePoint list or Duplicate SharePoint Online list using Power Automate
Clone SharePoint list or Duplicate SharePoint Online list using Power Automate

Explanation: Duplicate SharePoint Online list using Power Automate

When an item is created or modified in the SharePoint list

The first action is the trigger point which is “when an item is created or modified” in ListA.

Clone SharePoint list - when an item is created or modified
Clone SharePoint list – when an item is created or modified

Parameters:

We need to pass the below parameters in the “when an item is created or modified” trigger point:

  • Site Address – your source site URL.
  • List Name – Your source list name ( here it is ListA).

Initialize variable – Generate Unique ID

Create this generate Unique ID variable using the initialize variable action

Clone SharePoint list - initialize variable
Clone SharePoint list – initialize variable

Get item from list A – Get item from the source list

Add a “Get Item” action.

Clone SharePoint list - Get item from list A (source list)
Clone SharePoint list – Get item from list A (source list)

Parameters:

We need to pass the below parameters in the “when an item is created or modified” trigger point:

  • Site Address – your source site URL.
  • List Name – Your source list name ( here it is ListA).
  • ID – select the ID column from the “when an item is created or modified” trigger output from the Dynamic content tab.

Set variable – Custom Unique ID

Add a set variable action.

Clone SharePoint list - Set variable - Custom Unique ID
Clone SharePoint list – Set variable – Custom Unique ID

Generate a custom unique value. Select the variable “generateUniqueID” and in the value textbox write the below:

concat('CUSTUNIQUEID',triggerOutputs()?['body/ID'])

Notes:

  • Here we are just combining the custom text “CUSTUNIQUEID” with the ID column of trigger outputs. You can give some other text as per your requirement.

Add a Send an HTTP request to SharePoint action update the custom ID (“CustomUniqueID”) column in the source list.

Clone SharePoint list - Send an HTTP request to SharePoint action
Clone SharePoint list – Send an HTTP request to SharePoint action

Parameters:

We need to pass the below parameters in the “Send an HTTP request to SharePoint” action:

  • Site Address – your source site URL.
  • Method – POST
  • Uri: _api/web/lists/GetByTitle(‘ListA‘)/items(@{triggerOutputs()?[‘body/ID’]})/validateUpdateListItem

Note:

  • In Uri, replace ListA with your list, and inside items select the ID column from the “when an item is created or modified” trigger output from the Dynamic content tab.

Body:


{
"formValues":

[
{
"FieldName": "CustomUniqueID",
"FieldValue": "@{variables('generateUniqueID')}"
}
]
}

Notes:

  • For the field name, replace the “CustomUniqueID” column with your column’s internal name.
  • For the FieldValue – select your unique ID generation variable from the Dynamic tab variables section.
  • The above “Send an HTTP request to SharePoint” POST operation updates the custom ID (“CustomUniqueID”) column with a custom unique value in the source list.

Get items from List B – Target list

Add a Get Items action to get the list items from the target list based on the custom ID (CustomUniqueID) filter query.

Duplicate SharePoint Online list - Get Items from target list
Duplicate SharePoint Online list – Get Items from the target list

Parameters:

We need to pass the below parameters in the “Get Items” action:

  • Site Address – your source site URL.
  • List Name – Your target list name ( here it is ListB).
  • Filter Query – CustomUniqueID column from target list eq ‘CustomUniqueID column value from source list’

Add a condition control to check the length Get Items result

Add a condition control.

Duplicate SharePoint Online list - Condition control check length
Duplicate SharePoint Online list – Condition control check length

Check the length of the Get Items result.

length(outputs('Get_items_from_List__B')?['body/value'])

Notes:

  • In the condition, add the length function from the expression tab, then inside the function, select the Get Items action then after the? add [‘body/value’])
Duplicate SharePoint Online list - Condition control to check length of Get Items action
Duplicate SharePoint Online list – Condition control to check the length of the Get Items  action
  • If the length is 0, which means the particular item doesn’t exist in the target list, then it will perform the create item operation, and if it is more than 0, then the item exists in the target list, hence it will perform the update operation.
  • The parameters in the create item and update item are self-explanatory. In the create item action, we need to pass the custom unique id column along with other columns, and in the update item action, we need to pass the ID column along with other columns.

Refer to the previous article, how to create and update items in SharePoint using Power Automate. – Using Power Automate create SharePoint list item if not exists promptly – 0365

Limitation: Synchronize two SharePoint lists

Below are some limitations of the demo:

  • This is one-way synchronization which means items that are added or Edited in the source list (ListA), will be cloned to the target list (ListB), It will not occur in the opposite direction.
  • If you want to synchronize both ways for the Add or Edit, copy the same flow and configure it with the target list wherever the source list and site were referred to. To simplify it, we need to replicate the same flow for the target list.
  • In this demo, the delete trigger is not handled—if you want to synchronize the delete operation, you need to create two delete item flows that need to be configured in ListA and ListB.

Summary: Copy list items to another list SharePoint (Synchronize two SharePoint lists)

Thus in this article, we have learned the below with respect to cloning or duplicating two lists with the same values:

  • How to clone or duplicate two list items with the same values.
  • How to create a custom unique id in SharePoint Online list using Power Automate.

See Also: Power Automate Tutorial

You may also like the below Power Automate tutorials:

 

About Post Author

Do you have a better solution or question on this topic? Please leave a comment