Overview

I currently have a SharePoint list with an identity (person or group) column called “Owners,” where I can search for employees and add them to a list item. I then want to e-mail newly added people that they have been added to a list item so they can go and review the details of it.

While SharePoint does have an ‘automate’ feature where you can detect a list item’s column is modified, the only option is to send an email to all the people that are currently in that column. So, if you add a new person, it will re-email everyone who is already there. The other issue is that the e-mail says (below) is non-descript, so your users will be confused about why they are getting it.

Logic Apps does have a trigger that is “When an item is modified,” but its output is the current values of the entire list item that was modified, not what changed. SharePoint also has a “Get changes for an item or a file (properties only)” step, but like the name says, it returns properties only. In this case, the output is boolean (true/false) on if a column did change, not what changed. Luckily, we can use both of these logic app steps to find out what changed, what the previous value was, and what the value is now.

Resolution

SharePoint Site and List

Create SharePoint Site

First, you need to create a SharePoint site and/or a List. You can add a new list to an existing SharePoint site, or if you already have these ready, you can proceed to the next step.

In SharePoint, I am going to click the “+ Create Site” button

I am going to choose “Communication Site” but you can choose whichever.

For the template, I will choose “Standard Communication“

Next, give your site a site name.

Create SharePoint List

Now that we have created our SharePoint site, we need to create a list for it. Navigate to your newly created site and click + New > List

Next, select “List” and then give your list a Name.

Next, make the columns that you need for your list. Below is a screenshot of mine. The “Owners” column is the one that I want to detect change in, get the previous value and the current value, and in my case, email the people who have been newly added.

Logic App

Now that we have our SharePoint Site, our List, and the columns created, we can proceed with creating the Logic App. In Azure, create a new Logic App and then go to the Logic App designer view.

Click “Add a trigger” and search for SharePoint. We want to select “When an item or a file is modified“. You could also choose “When an item is created or modified,” but in my case, they will never be created with an Owner assigned; it’s only later in our process that we add an owner.

Next, in the drop-down, pick your SharePoint site and then select your List. You can also select the recurrence values.

Add a new Action and search for SharePoint again. Select “Get changes for an item or a file (properties only)

Pick your SharePoint Site and List again. For the Id select the lightning bolt icon to insert data from a previous step and select ID. For the Since value, we are going to find the property Trigger Window Start Token from the previous step.

Next, add a new action and search for “Condition“

I am going to name this “Has Owners column changed,” and for the value, I am going to choose “Has Column Changed: Owner“

And we want it to be “is equal to” and set “true” as the value. This is case-sensitive!

In the True column, add an action and choose “Send an HTTP request to SharePoint“

Call this action “Get SharePoint List Item Versions” and select your site address, the method should be GET, and the URI should be the following: _api/web/lists('ced8bd12-80e9-4776-8752-18b63dd0e02a')/items({ITEMID})/versions?$top=2

Make sure you put the SharePoint list ID that is for YOUR list. To get the ID, go to your SharePoint list, add a item. Then edit the list item and add a owner and run your Logic App. The first step will show you your ID.

The ID is the ID output from the trigger. The Id is the Id of the list item that changed.

Add a Header called Accept and the value is application/json; odata=nometadata

You may have noticed the $top=2, this will get the current version and the previous 1 version. Otherwise, it will return all (within the API limit) versions, which isn’t necessary.

Next, we need to add a new action. Select “Condition” for the new action.

Name this step “Is Response 404 (no previous versions).” Here, we want to check if the previous response (get all versions) returns a 404, which means that the item was just created and there are no previous versions. For the condition, click the fx button to add a function and put in the following:

outputs('Get_SharePoint_List_Item_Versions')?['statusCode']

Here, you can see we are taking the output of our step “Get SharePoint List Item Versions” (we replace spaces with underscores for functions) and selecting the statusCode property.

and then for the rest of the conditional, it should say is equal to and put 404

Next, go to Settings for “Is Response 404 (no previous versions)” and in the Run after setting, select “Has Failed“

In the True column, add an action and look up Control and select For each

We are going to call this action “For each owner in current owners” and in the “Select an output from previous step” enter a function and paste the following:

triggerBody()?['Owner']

This is taking the output body from the trigger and selecting the value(s) for Owner property.

Next, add a action in the for each loop. Search for the action “Send an email (V2)” from Office 365 Outlook.

In the email settings, for the “To” put in the following function which will take the Email value from our step called “For each owner in current owners”

For subject I put “You’ve been added as an Owner to a SharePoint item”

For the body, I brought in the title and description property from the output of the trigger.

No, we have finished all the logic where if we add an item and then modify the Owners, it will successfully send them an email letting them know they have been added to a SharePoint List item. Now, we need to create the logic where if the list item is not new, and we have previous versions.

Return to the False block for “Is Response 404 (no previous versions)“. Before we can add a action here, we must run our Logic App to where the step “Get SharePoint List Item Versions” will return data so we can copy the output body JSON.

I am going to go into my SharePoint list, make a new test item and modify the owners a few times and then run my Logic App.

Now, in my recent ru,n I am going to go to the step, “Get SharePoint List Item Versions” and copy the body

Back in our Logic App Designer, in the False block for “Is Response 404 (no previous versions” add a action called Parse Json.

In the Schema, click “Use sample payload to generate schema” and paste in the body you had copied from before. For content, it is the body from “Get SharePoint List Item Versions“. Call this “ParseJson” with no spaces.

Next, add a new Compose action below and call it CurrentOwners. This compose will take the body of the output from ParseJson and extract the first values Owner. The Inputs should be:

body('ParseJSON')?['value'][0]?['Owner']

Add another Compose step and call it PreviousOwners. This compose will take the body of the output from ParseJson and extract the 2nd item’s Owner value. The inputs should be:

body(‘ParseJSON’)?[‘value’][1]?[‘Owner’]

Next, create another Compose step and call it PreviousOwnerIDs. This time, the inputs should be

if(equals(outputs('PreviousOwners'), null), \n   createArray(), \n   createArray(\n      if(greater(length(outputs('PreviousOwners')), 0), outputs('PreviousOwners')[0]?['LookupId'], 0),\n      if(greater(length(outputs('PreviousOwners')), 1), outputs('PreviousOwners')[1]?['LookupId'], 0),\n      if(greater(length(outputs('PreviousOwners')), 2), outputs('PreviousOwners')[2]?['LookupId'], 0),\n      if(greater(length(outputs('PreviousOwners')), 3), outputs('PreviousOwners')[3]?['LookupId'], 0)\n   )\n)

This is a Logic Apps expression that dynamically builds an array based on the PreviousOwners output.

Now, we need another for each control to iterate through each owner. Name this action “For each current owner“. The function for this action should be:

outputs('CurrentOwners')

Which is just the output from CurrentOwners.

Within this for each loop, we need to add a conditional to run “Is current owner not in previous owner ID”. If this is true, then we know that this user was added as a owner as they are in our current owner value but not present in the previous.

Create a condition action and call it “Is current owner not in previous owner ID“. The value should be the following:
contains(outputs('PreviousOwnerIDs'), items('For_each_current_owner')?['LookupId'])

and the condition should be is equal to and then false

The condition is taking the LookupId in our for each block and seeing if it’s present in the PreviousOwnerIDs.

Lastly, for True we need to send an email to all newly added people that they have been added to the list item. I will leave the false block blank as I don’t need to perform any action for users that were already there as they have already received an email.

Create another Send an Email (V2) action step. In the To field put the following function:

items('For_each_current_owner')?['Email']

Which will grab the user’s Email value.

For the body it’s the same as before,e but this time, I am also going to grab the person that made the change using the function:

body('ParseJSON')?['value'][0]?['Editor']?['LookupValue']

Otherwise, the Title and Description are from the output of the trigger.

Review

Below is a screenshot where I removed an owner. We can see that since I have nothing in the False block, it didn’t do anything.

If I add a person and then run it, I get an email letting me know that I have been added as an owner of a list item, with some details of it and who performed the action.

In the end my logic app looks like the following:

Brad Wyatt

My name is Bradley Wyatt; I am a 5x Microsoft Most Valuable Professional (MVP) in Microsoft Azure and Microsoft 365. I have given talks at many different conferences, user groups, and companies throughout the United States, ranging from PowerShell to DevOps Security best practices, and I am the 2022 North American Outstanding Contribution to the Microsoft Community winner.