“I’d like to see which specific columns were updated in a SharePoint item that triggered a flow, how can I identify them using Power Automate?”
Inspiration for this post is a similar solution from Dennis.
When you work with SharePoint items, sometimes it’s not enough to know that an item was modified. You might need to trigger a flow only on update in a specific column, or perform some action depending on the modified columns. For example, send an information email with the updated columns. But how can you identify only the updated columns?
Note: you must enable version history on the SharePoint list/library for this solution to work.
Get all the SharePoint columns
Power Automate has an action ‘Get changes for an item or a file (properties only)’. As already explained in avoiding duplicate approval tasks, this action can tell you if there was a change in each column. Configure the action, use the item ID, and get the information for previous item version (VersionNumber -1).
add(int(triggerOutputs()?['body/{VersionNumber}']),-1)
Note: there’s also an option to get changes since a specific date and time.
But here comes a problem with the output ‘Has Column Changed:’. It’s a single object with all the columns as properties and the change information as a value. True = column was changed, false = column wasn’t changed. You can easily access information about a specific column, but you can’t filter only the changed ones.
For that you’ll need to further process the result.
Filter the changed columns
As already mentioned, it’s an object and as such you can’t filter the properties based on their values. For the filtering you must turn it in a format that’s easier to work with: an array. But since there’s no possibility for a direct conversion, there’s a middle step. Convert the object to string with string(…) expression, and then the string to an array with split(…) by a comma.
split(string(outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/ColumnHasChanged']),',')The output will be an array where each item will be a pair property:value converted into a string.
Now, when it’s an array, you can use the ‘Filter array’ to filter only the items that contain the string ‘true’. I’d recommend using string ‘:true’ to avoid situations where the column name contains *true*.
item()
contains
string(':true')
The output from the ‘Filter array’ will be an array with changed columns only, but still in the property:value format. Which leads to the last step, remove the :value and keep only the property (=column name).
Keep only the column names
At this point you might think about using multiple replace(…) expression to remove all the extra characters. But there’s an easier way to convert it with the json(…) expression. When you apply json(…) on each of the items it’ll do a cleanup.
from:
"\"Title\":true"
you get:
"Title"Use this functionality in a ‘Select’ action by applying json(…) on each item in the array.
json(item())
The output will be an array that’ll contain only the updated column names. You can then process it as any other array in your flows.
Full flow diagram
Summary
When you check in Power Automate flow which SharePoint item columns were updated, you’ve got multiple way on how to approach it. One option is to convert the whole object into an array as Dennis did, and filter it later. The other option is to reverse the process – filter it first and then convert to an array.
This solution contains multiple conversions – object to string to array to json. Most of the time the action outputs look strange, but at the end you get a single array with the updated columns only. And once you get the column names, you can easily get also their values.
It’s also a solution to get dynamic list of all columns in a SharePoint list or library, you just skip the ‘Filter array’ action.
Really well written and useful post! I was able to refactor a flow I am working on using far few steps!
Ellis
Thanks for this well written and most helpful article, this was exactly what I was looking for. I hope you don’t mind that I linked to this in the Power Community thread that was the source for Dennis’ article which, in turn, was the inspiration for your own article 🙂
Hello Oliver,
sure, feel free to link to it, it always leads to interesting solutions once more people start working on the same problem. 🙂
This is a very useful method of converting an object to an array! But there is one caveat – this only works because the SharePoint ID never changes.
When using this method to convert an object to an array, the first item in your new array will always include an open curly brace. For example:
[
“{\”Email\”:\”email@email.com\””,
“\”Phone\”:\”555-555-5555\””,
“\”Name\”:\”John Smith\”}”
]
If you put this through your Select json() method, this is what you get:
[
{
“Email”: “email@email.com”
},
“Phone”,
“Name”
]
The open curly brace seems to confuse the json() expression, and it makes the first array item into it’s own object and includes the value. If you strip off the first item in the array, it works fine (the closing curly brace doesn’t seem to cause any problems for some reason).
So in this case, you’ll never run into this issue because the ID never changes and will always be removed before it’s put through json(). But if you’re using this method in a different scenario, you’ll need to find some other way of ensuring the first item in your array is something that can be removed. Using another Select would be a good way to do this – add a dummy column as the first one, and then filter it out with a Filter Array.
Great content as always!
Hello Ben,
thank you for the detailed explanation. You’re right, the solution works with the assumption that the SharePoint item ID won’t change. If you work with a different array then you must take care of the {, exactly as you wrote.
Hello there,
nice tutorial, easy to apply and well written.
However, the result of the “select”-part where we use “json(item())” results in:
[
{
“Editor”: “”
},
{
“Modified”: “”
}
]
and not
[
“Editor”,
“Modified”
]
what should I do about it?
Thanks for your help in advanece!
Hello Fred,
that’s caused by the extra { as described by Ben above, I think the easiest way to get rid of it would be add replace() to the expression to remove the { before you start processing it:
split(replace(string(outputs(‘Get_changes_for_an_item_or_a_file_(properties_only)’)?[‘body/ColumnHasChanged’]),'{‘,”),’,’)
Hello Tom,
Thank you for explaining this approach in such a detailed manner. Highly appreciated.
Nevertheless, the issue described by Ben can’t be remediated with your proposed solution.
I suspect that it appears because the Select action fills in a value (the empty string, in this case) for the key (the expression, in this case). When I have looked at the Map in text mode I could see:
{
“json(item())”:””
}
The Select action shows to me the Map as a pair key and value, and not like in your screen shot only as a single field.
Any suggestions?
Hello John,
the ‘Select’ action allows you to remove the Key field. On the right side of the Map field an icon that does the switch.
Sorry, I am referring at Fred’s comment, and not the issue described by Ben.
Hi Tomriha,
Nice blog,About the Item on the Filter Array (Item contains string(‘:true’))..The Item i can’t find it in the Dynamic Content panel.. Is it a field in your Sharepoint list?
Hello Earl,
the item() is an expression, type it in the expression field and use it in the ‘Condition’
Hello,
Trying to follow some of your code. Curious if you could edit post to reflect tool names and what snippet of code is placed in each field in each respective tool? Array: Box 1: split, Box 2: item, and Box 3: string. Thanks. Only worked with automate for one week and trying to wrap head around arrays.
Hi Tom,
I was achieve the similar result for my query,my next advancement is from the output I got I would like to exclude some columns such as modified,edited by and so.How do I achieve that?
Appreciate your response
Hello Valencia,
since the result is an array you can use the ‘Filter array’ action to remove the columns you don’t want.
‘Filter array’ from the ‘Select’ output where ‘item() is not equal to Modified/Edited/etc’.
Hello All,
I have followed this article .Its working fine but I get the choice field value as with odata type microsoft azure connectors sharepoint sp list id and value .
How can I get only the value .Any help will be truly helpful
Hello Goldie,
take a look on the following article, it explains how to handle the more complex fields with a condition: https://tomriha.com/get-previous-values-of-modified-sharepoint-columns-in-power-automate/ i
Hi Tom,
In a selected item, how to get all the column’s names which value is boolean(true) then pass it to next process (approval/HTML table etc.)?
Hi Tom,
Followed the Filter Array and Select steps managed to out put the field names from Get Item. Just need to format the output properly for the next process. Thanks so much!!
Hi! Great article. Question – I’m running this is in an Apply to Each loop that then feeds into an HTML Table. For values where there are no “true” responses, I am still getting “[]”. Is there a way to remove the brackets? Thanks!