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 reactiation 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 thoses 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:

  1. Flow: Subscription
  2. Flow: Handler
  3. List: Sites (List of sites to go through)
  4. 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
Recurrence Recurrence Parameters Interval Settings Trigger Comment: The Flow runs once a month
Interval: 1 month
header nometa header nometa Parameters Name Initialize variable
Initialize variable
Comment: I use this for http requests against SharePoint
Name:header_nometa
Type: Object
Value:
{
  "Accept": "application/json; odata=nometadata"
}
flowName flowName used to filter array later in the flow Parameters Initialize variable
Initialize variable
Comment: Used to filter array later in the flow
Name: flowName
Type: String
Value: workflow()?['name']
notification URL Parameters Initialize variable
Initialize variable
Comment: This is the url of the Handle flow.
Name: notificationURL
Type: String
Value: URL from the Handler flow
expirationDateTime expirationDateTime Parameters Initialize variable
Initialize variable
Comment: This is the calculation of expirationdate for the flow
Name: expirationDateTime
Type: String
Value: addDays(utcNow(),60)
SubscriptionlD SubscriptionlD Parameters Initialize variable
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 sites Get Sites Parameters Get Items (SharePoint)
Get Items
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 For each item in list Parameters For each item in list
Comment: Loop through each item in the list
Value: body('Get_Sites')?['value']
Get contract lists Get contract lists Parameters Send an HTTP request to SharePoint
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 IDs from contract lists Select IDs from contract lists Parameters Select
Select Operations
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 Making batch requests Select Making batch requests Parameters Select
Select Operetions
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 subscription URI For each subscription URI in the previous step Parameters For each item in list
For each
Comment: For each subscriptionURI
Output: body('Select_Making_batch_requests')
Get subscription details Get subscription details Parameters Send an HTTP request to SharePoint
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')
Filterarray Filter array Find the subs that this flow has previously created Parameters Filter array
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 SubscriptionlD Set SubscriptionlD Parameters Set variable
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']
Get resource id Get resource id Parameters Compose
Compose Operation
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 True False Condition If the subscription exists, then update it, if not, create a new one Parameters Condition
Condition Operation
Comment: If the subscription exists, then update it, if not, create a new one.
If:
 – length(body(‘Filter_array’))
 – “is greater than”
 – 0
Update subscriptions Update subscriptions Parameters Send an HTTP request to SharePoint
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')
Set Subscription Set Subscription Parameters Send an HTTP request to SharePoint
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

4 
1
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
clap board roadside Jakob and Ryan

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.

Flow action Screenshot Action Name Details
When a HTTP request is received When a HTTP request is received Parameters When a HTTP request is received
When a HTTP request is received Operation
Comment: The Flow runs whenever one of the subscriptions sends a message
 
HTTP 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 Request Response Webhook is active Parameters Response
Response Request Operation
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 Condition Parameters Condition
Condition Control
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 Response Parameters Response
Response Request
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 Terminate Parameters Terminate
Terminate Control
Comment: Terminate the flow. No further actions are needed.
Status: Succeeded
ResponseWebhook is Active Response Webhook is active Parameters Set variable
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:
var ListGUID var ListGUID Parameters Set variable
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']
var listSiteURL var listSiteURL Parameters Set variable
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'])
Get lastModified time for list Get lastModified time for list Parameters Send an HTTP request to SharePoint
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')
lastModifiedTime lastModifiedTime Parameters Set variable
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 Update item Parameters Update item
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()
Get items Get items Parameters Send an HTTP request to SharePoint
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')}'

Overview of the Handler flow

o 
Response 
Terminate 
o 
When a HTTP request 
is received 
header nometa 
Is the HTTPca11 
is a request for 
var ListGUlD 
var listSiteURL 
o 
Response Webhook is 
o

2 thoughts on “Use One flow or logic app to trigger multiple SharePoint lists from multiple SharePoint sites”

  1. This is great. A couple of comments. I may have missed it. I had to turn off concurrency on the for each to keep the variables from getting messed up.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.