It’s not easy to show a list (or part of a list) from one site collection on another. There are data view web parts you could try in SharePoint Designer, content search queries and page viewers in SharePoint web parts and then some scripting methods you could try, but I, in my enterprise environment, had no luck with those. This method, however, utilizes Microsoft Flow and works flawlessly. Here are a couple great features:
- Permissions are completely flexible. Set the “new” list to view only or whatever permissions you like while keeping tight control over the original. People will not be able to access the original list or site collection but they’ll see your up-to-date info you’re wanting to share.
- You can set this up so it’s a one-way publishing experience so updates on list 1 show on list 2, but updates on list 2 don’t show on list 1 OR you can set it up two-way so each list will update the other, creating a shared list experience without allowing permissions to access each other’s site collections
So let’s get started!
A. Export Original List, Revise & Import to Other Site Collection
- It is imperative that items from the two lists match list item ID numbers. To ensure this, first export your source list to excel by clicking “Export to Excel” from the task bar in the new viewing experience, or from the “List” tab in the ribbon in the classic experience.
- Once that downloads, go in and make sure the file is sorted by ID number. If ID number wasn’t part of your view, add it and export again. Once sorted, find where there may be gaps (previous list items now deleted, causing a skip in sequence) and add a row and the missing number as seen below. I also added a note “Delete Me” so I can filter my list by that note later to delete all the records I added. We do this so that our ID numbers match when we import this list to our second site collection.
- Now go to your second (destination) site collection and then Site Contents. “Add an App” and search for “import”. Select “import spreadsheet.” Note: You’ll need to use a browser that allows ActiveX Controls, such as Internet Explorer.
- Name your list and provide a description – these don’t need to match the original. Then select “browse” and find your recently downloaded and updated spreadsheet. Select “Import”.
- A dialog will pop up. Change the second field, Select Range, to the only available option from the drop-down (unless you’d prefer to not import some rows). Note: If you rearranged your data at all while manipulating it prior to import, make sure you sort by ID number prior to importing. Actual, new, ID numbers will be assigned based on sequential order on the spreadsheet.
Note: If you get an error message about not being able to import to the server, make sure you’re signed into Microsoft Office client applications with the same account you’re using to import to SharePoint via the browser.
- You will be redirected to your new list. Make sure the ID numbers match your original list – you may need to add “ID” to your view. We’ll come back to clean the list up later.
B. Create Flows
- For now, since we have our two lists with identical fields and ID numbers, let’s build our Microsoft Flows. Go to https://flow.microsoft.com and login with your Microsoft account.
- Select “My Flows” from the top toolbar.
- Select “Create from blank”
- Name your flow name where it says “Untitled”. We’ll be creating 2-4 of these, based on your needs, so name them something distinctive such as ListName_Modify or ListName_Create. You could build one flow with multiple stages, but I find it easiest to troubleshoot by creating small flows and seeing their run reports separately.
- Select SharePoint as the trigger app.
- Select either of the triggers related to “items” (not files, unless working with Document Libraries)
- Enter the URL of your source list’s SharePoint Site Collection root
- The dropdown will automatically suggest lists for “List Name”. Select your source list. Note: Select the text of the name only when ready to select that folder for the Flow. Select arrows next to names to navigate until you see the name of your list and click its name.
- Select “+ New step” and “Add an action”
- Select SharePoint
- Now select the matching action, either “create item” or “update item” based on your chosen trigger.
- Now enter the URL of the destination SharePoint Site Collection root, and once again navigate via the dropdown until you’re able to select the new list name.
- Now you’ll see all of the fields available to you.
- If you’re on the modify/update part of your flow, the required ID field will be used to match any updated item to its partner in the new list. This is why we ensured ID numbers matched across the two lists. Click within the field and suggestions, based on your lists, will show on the right. Select “ID”.
- If you’re on the create part of the flow, you won’t need to match “ID” so go to the next step:
- Now for the rest of the fields (for which you want updated or new data to transfer), click within the field, then select the field from the suggestions on the right that match the name of the field you’re completing. When you’ve matched all the fields you want in the new list, you’re done. Scroll to the top and select “Create Flow” and then “Done.” Return to step 7 and repeat for the other action (modify or create).
- You now have two flows: one that will update items from the original list on the new list and one that will create items on the new list that were created on the original. If you want the second list to be editable and have those revisions appear on the original list, create two more flows in reverse (using the new list as the source).
C. Polish New List & Test Process
- Now go to your new list and clean it up:
- Choose classic or new viewing experience from the link in the lower left or from list settings –> advanced settings.
- Delete all the columns in your view(s) that you don’t want people to see. Flow will not recreate these columns, it will only populate the info for fields present on the new list. You can delete the ID1 column now that we have an actual ID column again.
- Recreate any views. If you have a lot, you can use a tool such as ShareGate Migration to transfer just the views over.
- Test your flow(s) by making an edit to your original list and seeing if it populates on the second. It can take a few minutes sometimes. You can track the flow attempts (and see if it’s even begun) by clicking the “i” icon from your “My flows” dashboard. These options also allow you the toggle switch option to turn off the Flow to prevent it from copying or creating for a period of time, and the “people” option to share your flow with someone else who can monitor and edit as needed.
And that’s it! Comment if you have suggestions or questions. Good luck!
2 Replies to “SharePoint list cross-site publishing alternative using Microsoft Flow”