Use Microsoft Flow to automatically update calculated column on a SharePoint List

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]‘}

12 thoughts on “Use Microsoft Flow to automatically update calculated column on a SharePoint List”

  1. 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?

    Reply
    • 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.

      Reply
    • 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.

      Reply
  2. 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

    Reply
    • 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.

      Reply
  3. 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?

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.