This issomewhat, what many will describe as a designflaw in SharePoint. It’s supposedto be “By Design”, but still, it’s just something that many peopleget 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”.
How to automatically update calculated column on a SharePoint list
- Create a new Flow in Power Automate
- Use the Action “Send HTTP Response to SharePoint”
- Update the field of the list and not all items
Uri
/_api/web/lists/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’)Headers
{
"Accept": "application/json;oData=verbose",
"Content-Type": "application/json;oData=verbose",
"X-HTTP-Method": "MERGE",
"IF-MATCH": "*"
}Body
{‘__metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’[your formula]‘}
Leave a Reply