Setting SharePoint calculated column 2 times in flow to refresh values

(0) Share

Report

Posted on by JOAS_Niels

180

Hi All,

I hope somebody can explain this behavior to me. I'm using calculated columns in SharePoint and I want them to refresh every day. I don't want to trigger an update of the records so I found a way to update the calculated columns through a http request. Because updating the same calculation, does not work, I try to first set it to a different value ( =Today() for example) and then restoring the previous value with another http request in the same flow.

See here a test flow:

I created a test column with the following formula: =NOW() and want to refresh this value.

First http request:

Second http request:

This does not work and I don't understand why.

What I've tried and doesn't work:

- A delay of 1 minute between the requests ( as you can see in the screenshot).

- Doing the requests in two different flows.

- Using in MERGE in stead of PATCH

And now comes an additional strange thing (to me). When I run the first request and then go to the sharepoint list and refresh my screen (and I see the change in formula) and then run the second request, IT WORKS. But this is not really a solution and I don't know how to simulate this in a flow.

I hope someone can clarify this behavior. Thnx!

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Gopala_Krishna 1,495 on at

Like (2)

Report
SharePoint calculates the values of calculated columns on-demand, and these values are cached. Simply updating the formula through an API call may not trigger an immediate recalculation in the back-end unless the cache is cleared.

When you refresh the SharePoint list manually, it forces a recalculation and a refresh of the cached values. This behaviour does not occur when the updates are done programmatically unless something actively triggers it.

These don’t work because the caching issue persists. The change is not committed or recognized as valid until the formula has been refreshed and recalculated by SharePoint.

Instead of directly modifying the calculated column, update a non-calculated column in the same list item. This triggers SharePoint's recalculation for all calculated columns in that item. Here's how:

Add a Dummy Column:

Create a new column in the SharePoint list (e.g., LastUpdated) of type "Date and Time" or "Text."

Update the Dummy Column:

In Power Automate, update the LastUpdated column for each item with the current timestamp or a dummy text value.

Flow Steps:

Trigger: Use a scheduled trigger to run the flow daily.

Get Items: Fetch the items from the SharePoint list.

Update Items: Use the "Update item" action to update the LastUpdated column for each item.

Was this reply helpful? Yes No
JOAS_Niels 180 on at

Like (0)

Report

Thank you, Gopala! That is very helpful information.

About your solution. That is exactly the solution I wanted to circumvent. I'd rather not have all records updated every day. I don't want the Modified by and Modified columns "corrupted" with data from the updates of Power Automate.

Another workaround I explored was using custom formatting through JSON, but this has the disadvantage that you can't export the data because it only visible in the browser.

So unless someone knows how to trigger the refresh of SharePoint through Power Automate, I will have to choose between these two alternatives.

Was this reply helpful? Yes No