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.
Would this change the “Modified By” field to the name of the flow owner ?
No. It would not change it.
Hi Ulrich,
I keep getting this error:
An entry without a type name was found, but no expected type was specified. To allow entries without type information, the expected type must also be specified when the model is specified.
Can you point me in the right direction?
– Cheers
Hi Oshi
I would look to see if there are any of your rows that are miscalculated. When to flow is triggered it essentially does the recalculation for all the rows again. But to me it looks like the error is trying to tell you that one row is missing some data to do the calculation.
To test your function, try and create a new list with the exact same columns and calculated column and then see if you can get it to work on that one. That will tell you that it’s not the flow but the list that has an error.
Thank you this worked perfectly. Question. If I need to update two columns, is there a way to do it with one HTTP request or do I need to call it twice?
Hi M.A
For that, you will need to make another http request. One requst for each field.