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 actionScreenshotAction NameDetails
Recurrence O 
Recurrence 
Parameters 
Interval 
Settings 
Code View 
About 
Frequency TriggerComment: The Flow runs once a month
Interval: 1 month
header nometa header nometa 
Parameters 
Name 
header nomet3 
*Type 
Object 
Va Iue 
Settings 
Code View 
About 
"Accept": "application/json; odata=nometadata" Initialize variable
Initialize variable 
VE ria bles
Comment: I use this for http requests against SharePoint
Name:header_nometa
Type: Object
Value:
{
  "Accept": "application/json; odata=nometadata"
}
flowName {x) 
flowName 
used to filter array later in the flow 
Parameters 
Name 
flowName 
*Type 
String 
Va Iue 
workflowO x 
Settings 
Code View 
Run After Initialize variable
Initialize variable 
VE ria bles
Comment: Used to filter array later in the flow
Name: flowName
Type: String
Value: workflow()?['name']
notification URL 
Parameters 
Name 
notification URL 
*Type 
String 
Va Iue 
https://prod 
Settings 
Code View 
Run After 
14. n orth c2a55d8ebfc6f5a/trigge rs/manu I/pa Initialize variable
Initialize variable 
VE ria bles
Comment: This is the url of the Handle flow.
Name: notificationURL
Type: String
Value: URL from the Handler flow
aul!meauogel!dxa {x) 
expirationDateTime 
Parameters 
Name 
expiration DateTime 
*Type 
String 
Va Iue 
Settings 
Code View 
Run After Initialize variable
Initialize variable 
VE ria bles
Comment: This is the calculation of expirationdate for the flow
Name: expirationDateTime
Type: String
Value: addDays(utcNow(),60)
aluoAd!Dsqns {x) 
SubscriptionlD 
Parameters 
Name 
SubscriptionID 
*Type 
String 
Settings 
Code View 
Run After Initialize variable
Initialize variable 
VE ria bles
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 
Site Address 
Settings 
Code View 
Run After 
https://domain.sharepoint.com/sites/managementSite 
List Name 
SiteList 
Limit Columns by View 
Allltems 
x 
x 
x 
x 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
v 
u! uBu q.ee For each item in list 
Parameters 
*Select an out t from 
value x 
Settings 
reviousste s 
Run After 
About For each item in list
Comment: Loop through each item in the list
Value: body('Get_Sites')?['value']
Get ( ont ts 
十 Get contract lists 
Parameters 
*Site Address 
URL x 
Method 
Settings 
Code View 
Static Result 
x 
eq O and Hidden eq false and startswith(TitIe, 'contracts-I) Send an HTTP request to SharePoint
Send an HTTP request to SharePoint 
SherePoint
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-')
(v) 
Select IDs from contract lists 
Parameters 
From 
d.results X 
Map 
Enter key 
Settings 
Code View 
Run After 
Enter value 
x Select
Select 
Dete Operetions
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 
From 
Output x 
Map 
" api/web/lists(guid' 
Settings 
Code View 
')/Subscriptions• 
Run After Select
Select 
Dete 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.
dav stW!AeÆd 
uogd!nsqns 
q.ee For each subscription URI in the previous step 
Parameters 
Settings 
Run After 
About 
*Select an out t from reviousste s 
Output x For each item in list
Comment: For each subscriptionURI
Output: body('Select_Making_batch_requests')
sl!Qep 
uoud!Dsqns Get subscription details 
Parameters 
*Site Address 
URL x 
Method 
Settings 
Code View 
Static Result 
Current item X 
Headers 
header_nometa x 
x 
x Send an HTTP request to SharePoint
Send an HTTP request to SharePoint 
SherePoint
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 
From 
value x 
clientState 
Settings 
Code View 
is equal to 
Run After 
flowName x Filter array
Filter array 
Data Operations
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 {x) 
Set SubscriptionlD 
Parameters 
Name 
Value 
Settings 
Code View 
Run After 
SubscriptionID 
first(...) x 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 년 Get resource id 
Parameters 
Inputs 
Settings 
Code View 
replace(. 
About 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 
True 
False Condition 
If the subscription exists, then update it, if not, create a new one 
Parameters 
And •v 
Settings 
Run After 
About 
is greater than 
+ Add •v Condition
Comment: If the subscription exists, then update it, if not, create a new one.
If:
 – length(body(‘Filter_array’))
 – “is greater than”
 – 0
suogd!nsqns 
enu Update subscriptions 
Parameters 
Site Address 
Method 
Headers 
Settings 
Code View 
Static Result 
URL x 
pATCH 
"resource": 
Outputs x 
"notificationUrI": 
notificationURL x 
"expirationDateTime 
expirationDateTime X 
"clientSt3te":" 
flowName X 
header_nometa x 
x 
x 
x Send an HTTP request to SharePoint
Send an HTTP request to SharePoint 
SherePoint
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
Send an HTTP request to SharePoint 
SherePoint
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 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 actionScreenshotAction NameDetails
Isanbal e uaqm When a HTTP request is received 
Parameters 
HTTP POST URL 
Settings 
ttps:// p 
Code View 
About 
Request Body JSON Schema 
"properties" • 
"body": { 
"properties": 
"value": 
"items": 
"properties" 
"clientState": 
"type" • "string" When a HTTP request is received
When HTTP request is 「 · 一 v d
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 Web hook is Response Webhook is active 
This needed to remind SharePoint that the webhook is active 
Parameters 
Status Code 
Headers 
Body 
Add new parsmeter 
Settings 
Enter key 
Code View 
About 
Erter 
Enter response contert Response
Response 
Request
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 
True 
False Condition 
Parameters 
And •v 
Settings 
int(„.) x 
About 
is equal to 
+ Add •v 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
asuodsey 
emi Response 
Parameters 
Status Code 
Headers 
Add new parameter 
Settings 
Enter key 
Code View 
About 
Enter value 
triggerOutputs() x 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 
Settings 
Succeeded 
Code View 
Run After Terminate
Term inate 
Control
Comment: Terminate the flow. No further actions are needed.
Status: Succeeded
ResponseWebh00k is Response Webhook is active 
This needed to remind SharePoint that the webhook is active 
Parameters 
Status Code 
Headers 
Body 
Settings 
Erter key 
Code View 
About 
Erter 
Enter response contert 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 ListGUlD {x) 
ver ListGLllD 
Get resource from HTTP request 
Parameters 
Name 
*Type 
Value 
Settings 
ListGUlD 
String 
Code View 
Run After 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']
lunausJs!l {x) 
ver listSiteURL 
Get SiteURL from HTTP Request 
Parameters 
Name 
• Type 
Settings 
listSiteLIRL 
String 
Code View 
Run After 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 
Site Address 
Method 
Headers 
Add new parameter 
Settings 
Code View 
Run After 
https://domain.sharepoint.com/sites/yoursitehere 
_api/web/lists(guid c ? 
EfiIte=IistGlJID eq 
ListGUID x 
header_nometa X 
x 
x Send an HTTP request to SharePoint
Send an HTTP request to SharePoint 
SherePoint
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')
I astModifiedTime {x) 
lastModifiedTime 
Parameters 
Name 
*Type 
Value 
Settings 
Code View 
Run After 
lastModifiedTime 
String 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 Parameters 
Site Address 
List Name 
Add new parameter 
Settings 
Code View 
listSiteURL x 
first(...) x 
"LastModifiGtionDateTime": 
Run After 
utcNowO x 
x 
x 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 
Site Address 
List Name 
Filter Query 
Settings 
Code View 
listSitelJRL x 
ListGUlD x 
Run After 
Modified ge 
x 
x 
x 
lastModifiedTime Send an HTTP request to SharePoint
Send an HTTP request to SharePoint 
SherePoint
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

Leave a Reply

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