Use One flow or logic app to trigger multiple SharePoint lists from multiple SharePoint sites

If you ever have to create a flow or a logic app where you need to listen for updates from several SharePoint lists, you actually need 2 flows. One flow to handle the subscriptions and retraction of the subscriptions and one to act, whenever anything happens on one of the lists, that you subscribe to. Say you have the lists spread across different sites as well. In this scenario you will get a guide on how to loop across several sites and locate all the lists, that have a specific name or starts with a specific name. And then setting all those lists to trigger a single flow whenever something happens on one of those lists.

Research for this solution:
• https://poszytek.eu/en/microsoft-en/office-365-en/powerautomate-en/trigger-one-flow-from-multiple-sharepoint-lists/
• http://johnliu.net/blog/2019/3/one-flow-to-handle-them-all-how-to-subscribe-to-multiple-sharepoint-lists-with-one-flow

Power Automate vs Logic apps

I would recommend that you use Logic apps instead of Power Automate Flows. If you wish to use Power Automate Flows, you are going to need premium flows subscription before this will work. Might as well use logic apps, which are “pay-as-you-go” and they have better controls for your flow creation. Logic Apps are in many ways still Workflows or “Flows”. Technically, it’s an Logic app, but I use the term “Flow” in this post.

This solution is built to handle adding x- amount of lists and x-amount of sites. So you can no nuts on sites and lists.

Inventory needed

You will need 2 flows and 2 controlling lists:

Flow: Subscription
Flow: Handler
List: Sites (List of sites to go through)
List: (List of timestamps of the last modified items on each list)

HTTP Webhooks

Webhooks are bacisly a way to tell SharePoint (in this case) “Whenever something happens on this list, inform this endpoint”. That translate into:
• New item or updated item in List A -> inform endpoint at flowaddress [1234]
• New item or updated item in List B -> inform endpoint at flowaddress [1234]
• Etc

This means that you are going to setup a flow that creates webhook subscriptions, that all informs the same flow of changes. The annoying/bad news is, that the webhook subscription only informs you, that a change has occurred, not, what actually has happend or the ID of the item that has been created or updated. So whenever you get a signal from a webhook subscription, you only get the site and the GUID of the list. Then you need to go into that list and figure out what has changed.

NOTE: To get the actual flowaddress, you need for the notificationURL, you need to create the “Handler flow” and get the URL, befor you can complete the “Subscription flow“.

Subscription Flow

I am using this solution to handle a subscription on lists called “contracts-xxx”. A lot of lists with contract information on them. If you have other requirements you just need to change the oData query in the “Get contracts lists” – action. I’ve made the flow to if, somehow a subscription gets deleted, it will be re-added.

NOTE1: The action “Select Making batch requests”, will ofthen be the reason for you flow not wanting to save. It removes the ” from the beginning and end of the mapping. Just set them again and save the flow.

NOTE2: If you try and run the flow without setting up the basic resonse from the “Handler flow”, you will get an error. If you don’t set a response then the list will stop informing the subscription after a few tries, because SharePoint doesn’t know that there is an answer and the it just stops working for a while.

Flow action	Screenshot	Action Name	Details
		Trigger	Comment: The Flow runs once a month Interval: 1 month
		Initialize variable	Comment: I use this for http requests against SharePoint Name:header_nometa Type: Object Value: `{ "Accept": "application/json; odata=nometadata" }`
		Initialize variable	Comment: Used to filter array later in the flow Name: flowName Type: String Value: `workflow()?['name']`
		Initialize variable	Comment: This is the url of the Handle flow. Name: notificationURL Type: String Value: URL from the Handler flow
		Initialize variable	Comment: This is the calculation of expirationdate for the flow Name: expirationDateTime Type: String Value: `addDays(utcNow(),60)`
		Initialize variable	Comment: This is the variable, the will hold the SubscriptionID’s, that are set later in the flow Name: SubscriptionID Type: String Value:
		Get Items (SharePoint)	Comment: Get all items on the list that holds the Title and URL of the sites. Site Address: https://[domain].sharepoint.com/sites/managementSite List Name: SiteList
		For each item in list	Comment: Loop through each item in the list Value: `body('Get_Sites')?['value']`
		Send an HTTP request to SharePoint	Comment: Get all the lists on the site that starts with “contracts-“. The Uri is Odata query and you can change that to get the lists you need Site Address: items(‘For_each_item_in_list’)?[‘URL’] Method: GET Uri: `_api/web/lists?$filter=BaseType eq 0 and Hidden eq false and startswith(Title, 'contracts-')`
		Select	Comment: Get all the listID’s from the lists on the current site From: body(‘Get_contract_lists’)[‘d’][‘results’] Map: `Id --> Item()?['Id']`
		Select	Comment: Take the ID’s from the previous step and merge them into an subscriptionURI of that specific list. From: body(‘Select_IDs_from_contract_lists’) Map: `"_api/web/lists(guid'@{item()?['Id']}')/Subscriptions"`NOTE: Logic app will often remove the ” from the start and end of the Map input and the signal an error. Just put the ” back and save the flow.
		For each item in list	Comment: For each subscriptionURI Output: `body('Select_Making_batch_requests')`
		Send an HTTP request to SharePoint	Comment: Get the subscription details for the current SubscriptionURI Site Address: items(‘For_each_item_in_list’)?[‘URL’] Method: GET Uri: `items('For_each_subscription_URI_in_the_previous_step')` Headers: `variables('header_nometa')`
		Filter array	Comment: Filter the array of subscriptions on the list, to only get the subscriptions that this flow has created. From: `body('Get_subscription_details')['value']` Filter: `equals(item()?['clientState'], variables('flowName'))`
		Set variable	Comment: get the ID of the array from the previous step. Since this flow only creates one subscription, we can be sure that the first item in the array is the only one and the one we need. Name: SubscriptionID Value: `first(body('Filter_array'))?['Id']`
		Compose	Comment: Get the ressource ID for later use, when you create or update a subscription. Inputs: `replace(replace(item(),'_api/web/lists(guid''',''),''')/Subscriptions','')`
		Condition	Comment: If the subscription exists, then update it, if not, create a new one. If: – length(body(‘Filter_array’)) – “is greater than” – 0
		Send an HTTP request to SharePoint	Comment: Update the subscription Site Address: `items('For_each_item_in_list')?['URL']` Method: PATCH Uri: `concat(items('For_each_contractlist_on_site'),'(''',variables('SubscriptionID'),''')')` Body: `{ "resource":"@{outputs('Get_resource_id')}", "notificationUrl":"@{variables('notificationURL')}", "expirationDateTime":"@{variables('expirationDateTime')}", "clientState":"@{variables('flowName')}" }` Headers: `variables('header_nometa')`
		Send an HTTP request to SharePoint	Comment: Create a new subscription Site Address: `items('For_each_item_in_list')?['URL']` Method: POST Uri: `items('For_each_contractlist_on_site')` Body: `{ "resource":"@{outputs('Get_resource_id')}", "notificationUrl":"@{variables('notificationURL')}", "expirationDateTime":"@{variables('expirationDateTime')}", "clientState":"@{variables('flowName')}" }` Headers: `variables('header_nometa')`

Overview of the Subscription flow

FM each item in Get contract lists Select IDs from contract lists Select Making batch requests FM each contradist on Get subscription details Filter array

Handler flow

This is the flow that all the subscriptions notifies whenever something changes. But it is also the flow that validates the notificationURL is correct. In order to get the subscriptions to be valid, you need to get this part of the flow ready first. Then you can get all the subscriptions lined up.

When a HTTP request is received Parameters

Flow action	Screenshot	Action Name	Details
		When a HTTP request is received	Comment: The Flow runs whenever one of the subscriptions sends a messageHTTP Post URL: Generated automaticly, when the flow is saved the first time. This URL is THE NOTIFICATION URL, you need for the subscription flow. Request Body JSON Schema: Use the output of the “Send an HTTP request to SharePoint” from the “Subscription flow”, to generate a schema
		Response	Comment: If you do not send back a response, then SharePoint will assume that the webhook is not beeing used and will then stop firing the subscription Status code: 200
		Condition	Comment: If the value of “Content-header is 0, then the request is for validation of the subscription and the flow needs to respond wtih an “OK” (200) If: – int(triggerOutputs()[‘headers’]?[‘Content-Length’]) – “Is equal to” – 0
		Response	Comment: Respond to the sender that the flow exist and it’s okay to send messages to here. Status code: 200 Body: `triggerOutputs()['queries']?['validationtoken']`
		Terminate	Comment: Terminate the flow. No further actions are needed. Status: Succeeded
		Set variable	Comment: Respond to the sender that the webhok is active. If not, them SharePoint will think there is no-one in the reciving end and then stop sending information to the flow. Status code: 200 Body:
		Set variable	Comment: Get the GUID of the list. The information is located in the http request, that is triggering the flow. Name: ListGUID Type: String Value: `first(triggerBody()['value'])?['resource']`
		Set variable	Comment: Get the URL of the Site, where the list is located. Name: listSiteURL Type: String Value: `concat('https://[domain].sharepoint.com',first(triggerBody()['value'])?['siteUrl'])`
		Send an HTTP request to SharePoint	Comment: Get the date and time for the last list modification. Site Address: The site where your SharePoint list of timestamps are located Method: GET Uri: `_api/web/lists(guid'2e188093-767f-4cca-b93b-5853be31571c')/items()?$filter=listGUID eq '@{variables('ListGUID')}'` Headers: `@variables('header_nometa')`
		Set variable	Comment: Save the timestamp from the list in a variable Name: lastModifiedTime Type: String Value: `first(body('Get_lastModified_time_for_list')['value'])?['LastModificationDateTime']`
		Update item	Comment: Update the timestamp on the list, so it’s ready for the next flow. Site Address: The site where your SharePoint list of timestamps are located Site Address: URL where your list is located List Name: The name of your list Id: `first(body('Get_lastModified_time_for_list')?['value'])?['Id']` Title: `first(body('Get_lastModified_time_for_list')?['value'])?['Title']` LastModificationDateTime: `utcNow()`
		Send an HTTP request to SharePoint	Comment: Get all the items on the list, that are updated after the Timestamp of the variable “lastModifiedTime”. Site Address: `@variables('listSiteURL')` List Name: `@variables('ListGUID')` Filter query: `Modified ge '@{variables('lastModifiedTime')}'`