Get previous value(s) of modified SharePoint column(s) in Power Automate

Posted On August 15, 2021

“Can I use Power Automate to send an email with the previous and new value in a SharePoint column? I’m interested only in the changed columns.”

---

This post is an extension to the previous post ‘Identify which SharePoint item columns were updated‘. The outcome of that flow is an array with the changed columns. Only the column internal names, nothing else. While it’s nice to know that a column was updated, it’s better to know also the actual values. And by values I mean not only the new value, but also the previous value.

The result of this post is an email similar to the alert sent on item update. But this one will include only the updated columns and skip the rest.

Note: the version history on the SharePoint list must be enabled.

Get the previous item version

To get the previous field values, you must get the whole previous version of the item. That’s one of the reasons you need the version history, without it there’d be nothing to get. If there’s an existing previous version, use a HTTP request to SharePoint to get it.

Method: GET

Uri:
/_api/web/lists/getByTitle('<listName>')/items(<itemID>)/Versions(<versionID>)

Note: everything inside < and > are placeholders which you must replace.

The only problematic placeholder is the <versionID>. You might be tempted to use the previous version number in the same way you did in the ‘Get changes…’ action. But that’s not the expected <versionID> parameter. That number, e.g. 15, is the VersionLabel. The actual VersionId, which is required for the HTTP request, is a much higher number.

But that’s not a problem as the right number should be already available among your dynamic contents. It’s one of the outputs from the ‘Get changes…’ action.

outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/SinceVersionId']

Knowing this you can build the whole HTTP request, e.g.

/_api/web/lists/getByTitle('PlaygroundList')/items(@{triggerOutputs()?['body/ID']})/Versions(@{outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/SinceVersionId']})

The output of this HTTP request will be a JSON with the previous version of the item, including all previous column values.

Get values for modified columns

Once you have the previous item, you can extract the column values and store them in a variable. But you’re interested only in values of the modified columns, not all of them. That leads us back to the previous post.

The output of the previous post was an array with the modified columns – the columns you’re interested in. If you loop through that array, you can extract from the previous version only their values. As the first step, initialize an array variable, e.g. var_changesArray.

Then add the ‘Apply to each’ to loop through the modified columns, the output from ‘Select’.

In each loop it’ll take one of the modified columns and store it as the ‘Current item’ (=item() expression). Navigating through the version history JSON, you can then take only the value for this specific column. For the value in the previous version history, the output from the HTTP request:

outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['d']?[item()]

And for the value in the current item:

triggerOutputs()?['body']?[item()]

Place these expressions in their own ‘Compose’ actions for later processing.

Format the values

The last step is to fill the array variable in a readable way. I say ‘readable’ because you’re still dealing with complex SharePoint columns, e.g. lookup, choice, or people picker. These column types contain a whole object, much more information than you need.

And as such they’ll need an extra expression to process them. By using the if(…) expression you should check if the column contains specific “type”, and if it does, take only the ‘Value’, not the whole object.

If the column contains string ‘FieldLookupValue’, it’s a lookup column and you want to take the value in ‘LookupValue’ property.

The same logic applies if it contains ‘FieldUserValue’, which defines a people picker column.

For the previous item version it would mean expression as the one below. Check if the column string contains one of the values, and if it does, select the ‘LookupValue’. Otherwise use the column as is.

if(contains(string(outputs('Compose')),'FieldLookupValue'),
  outputs('Compose')?['LookupValue'],
  if(contains(string(outputs('Compose')),'FieldUserValue'),
    outputs('Compose')?['LookupValue'],
    outputs('Compose')
  )
)

For the current item version it’s a bit different as it uses different properties for those columns. Lookup columns are identified by string ‘SPListExpandedReference’ and the value is stored in ‘Value’. People picker columns contain ‘SPListExpandedUser’ and it allows you to take any user property, e.g. ‘DisplayName’ or ‘Email’.

if(contains(string(outputs('Compose_2')),'SPListExpandedReference'),
  outputs('Compose_2')?['Value'],
  if(contains(string(outputs('Compose_2')),'SPListExpandedUser'),
    outputs('Compose_2')?['DisplayName'],
    outputs('Compose_2')
  )
)

Using both the expressions you can append a new entry in the variable.

Then just convert the array to a string with the join(…) expression using some separator, e.g. a new line or HTML new line (<br>), and send the overview in an email.

join(variables('var_changesArray'),'<br>')

In this post I used a simple, text only email, but since you’re working with HTML, you can format it in any way you’d like by adding html tags in the ‘Append to array variable’ action, e.g. by using <b>[Current item]</b>:

Summary

When you check SharePoint item for modified columns, you might need also the column previous value, and Power Automate can give it to you. The whole process, including filtering the modified columns, has multiple steps.

Firstly, you must extract only the modified columns. Secondly, get the previous version of the item. And thirdly, get the actual value and format it. You must take into account the complex column types, as shown on the people picker, choice and lookup column, and display them in a readable way.

---

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

First Name

Email address

Zero spam, unsubscribe anytime.

Leave this field empty if you’re human: