This is somewhat, what many will describe as a designflaw in SharePoint. It’s supposed to be “By Design”, but still, it’s just something that many people get very annoyed about.
In SharePoint, if you create a Calculated column, which has a calculation that is based on something dynamic, like “=Today()”, you would expect that column to just display todays date, every day you visit your list. But unfortunately, that is not so. To update the column with the calculation, you need to visit the item and the save it again. But if you update the column in the list settings, SharePoint will the re-calculate the list for you. So the trick is to get Microsoft Flow to update the column every night to keep the calculation correct.
To create the MS Flow that will update the column, you need the “Send an HTTP request to SharePoint” action. In this example I have a calculated column on a tasklist. The Column is called “CalcToday”.
Uri: /_api/web/lists/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’)
Headers
| Label | Value |
| content-type | application/json;odata=verbose |
| X-HTTP-Method | MERGE |
| Accept | application/json;odata=verbose |
Body: {‘__metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’[your formula]‘}
Thanks this is useful. Do you know if it is possible to make a similar HTTP request from Flow to update a Calculated Site Column’s formula?
Hi munchkin. that is what you do. Just update the code in the “body” input form.
It took me a bit to troubleshoot my flow failures. Eventually I found that all single quotes needed to be re-typed, as they don’t copy & paste correctly for some reason, as noted in the below post.
https://powerusers.microsoft.com/t5/Building-Flows/Send-HTTP-Request-to-Sharepoint-fails-with-Status-400-Bad/m-p/243172#M24024
However, the flow is running successfully now, but my calculated fields are still not updating. Any idea as to why not?
What the flow does is basicly, just setting the calculated columns calculation. When that is done, SharePoint internals take care of the rest. My guess is that the flow is running and there are no errors. But still you are not getting your dates updated. What if you try to update the column with something else? Like plan tekst. Just to see if anything actually gets written to the column.
Quotes and browsers don’t match. I always test my copy/pasting through the notepad to see if it looks funky.
Could you use the same approach in a single flow to update calculated columns in multiple lists?
I don’t see why not. If you have lots of lists, I would make a list with the URL’s of all those lists and the in a loop that go through that list, repeat the update for each item.