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

24 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
  4. This is almost a great solution, only limited by the calculated column values not re-calculating after being updated to the same column formula. I’m able to test this by saving my calculated column formula as Now() which gives me a new column value, but once set it does not recalculate the column values when re-saving the formula until toggled to some other value and back to Now(). Same results are seen with Flows using the provided technique in this article or manual updates on SharePoint. Thanks for sharing your solution.

    Reply
  5. Does this work for a SharePoint Library too? The properties of a library are just like lists, so I imagine it would. Do I need to update /_api/web/lists/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’) to /_api/web/library/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’), or similar?

    Also, if your library title or column titles are two or more words, do you just use a %20 to represent the space?

    Thanks!

    Reply
    • It should! You don’t need to change the /web/lists section to /web/library either — SharePoint’s API treats library properties as list information, so the request URL is the same for both. You can use either a space or a %20 in the request since spaces will be translated to %20 automatically when the request is sent. Hope that helps.

      Reply
  6. I think I know why some people’s work and some don’t. One thing that SharePoint does is it makes slight housekeeping to formulas. It capitalises NOW() and TODAY(). If I go into my sharepoint column and change NOW() to Now(). The formulae is seen as altered and the calculated field updates its calculation and the beauty is Now() also reverts back to NOW(). I believe if you used NOW() or TODAY() in the JSON properties of Formula in the flow it wouldn’t work. By making a subtle change like Now() or Today() (where its not all capitalised) it sees it as a modification and triggers the calculation to update and then it must do some housecleaning and recapitalise it. Also adding something as simple as a space in between the brackets of NOW() also has the same affect e.g. NOW( )
    At least this is what I have found in my quick test on this. Hope that helps someone.

    Reply
  7. Thanks Danny Mosquito, that subtle change to use “Today()” instead of “TODAY()” triggered my calculated field formula to run!

    Reply
  8. Thanks for this post. However I am getting an error that the URL was not found.

    /_api/web/lists/getbytitle(‘Tasks’)/fields/getbytitle(‘CalcToday’)

    Is ‘Tasks’ in the formula above the name of the list?

    If so, my List Information in the list settings shows my Name is ‘Invoice Compliance Intake Form’ and the web address is https://……./Lists/Invoice Compliance Tracker/AllItems.aspx

    What should I input?

    Reply
  9. HI, I cannot get the formulas to work. This is what I have typed in {‘__metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’=if(or(‘Returning Date’)<Today(),('Returning Date')=" "),'In','Out')‘} I get the property name " is not valid. The name of a property cannot be empty

    Reply
    • Hi Kris

      You are copy/pasting it wrong. Try running the code by a notepad. There is a difference between ' and ´ .. {‘__metadata’ should be {'__metadata' etc.

      Then it should work.

      Reply
      • Hi, thank-you for the response. I re-wrote the formula in notepad and am still receiving an error…..Invalid JSON. A comma character ‘,’ was expected in scope ‘Object’. Every two elements in an array and properties of an object must be separated by commas.

        {‘_metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’=’if(or((‘Returning Date’)<today(),(‘Returning Date’)=””),’Out’,’In’)’}

        Reply
        • Remember the 2 x _ on metadata. It should be __metadata and not _metadata. I also think that your use of escaping ” and ‘ plays a part in your error. Tryr and start by keeping it simple and have the Formula to be just “=Today()”… When that works, build out.

          Reply

Leave a Reply to munchkin Cancel reply

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