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

  1. Create a new Flow in Power Automate
  2. Use the Action “Send HTTP Response to SharePoint”
  3. 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]‘}

31 responses to “Use Power Automate to automatically update calculated column on a SharePoint List”

  1. munchkin Avatar
    munchkin

    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?

  2. Ulrich Gerting Bojko Avatar

    Hi munchkin. that is what you do. Just update the code in the “body” input form.

  3. Christopher Roberson Avatar
    Christopher Roberson

    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?

    1. Ulrich Gerting Bojko Avatar

      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.

  4. Kev Houston Avatar
    Kev Houston

    Could you use the same approach in a single flow to update calculated columns in multiple lists?

    1. Ulrich Gerting Bojko Avatar

      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.

  5. Ahmed Hanafy Avatar
    Ahmed Hanafy

    Would this change the “Modified By” field to the name of the flow owner ?

    1. Ulrich Gerting Bojko Avatar

      No. It would not change it.

  6. Oshi Avatar
    Oshi

    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

    1. Ulrich Gerting Bojko Avatar

      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.

  7. M. A Avatar
    M. A

    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?

    1. Ulrich Gerting Bojko Avatar

      Hi M.A

      For that, you will need to make another http request. One requst for each field.

  8. Ben Avatar
    Ben

    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.

  9. K. Avatar
    K.

    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!

    1. Jonathan S Avatar
      Jonathan S

      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.

  10. Danny Mosquito Avatar
    Danny Mosquito

    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.

    1. Ulrich Gerting Bojko Avatar

      Hi Danny

      Nicely spotted. I’ve made it a habbit of always using Now() and Today().

  11. munchkin Avatar
    munchkin

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

  12. Joe Avatar
    Joe

    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?

    1. Ulrich Gerting Bojko Avatar

      Hi Joe

      Yes, Tasks is the name of the list.

  13. Jonas Avatar
    Jonas

    Hi There,

    I tried this flow with my formula (Days counter) the flow is successful but it wont update the field. Any suggestions?

  14. Azar Avatar
    Azar

    Hi,
    My flow is running successfully but calculated column is not get updated

    *Site Address Az – https://hclo365.sharepoint.com/sites/Az
    *Method PATCH
    *Uri _api/web/lists/getbytitle(‘ACR’)/fields/getbytitle(‘Reminder’)

    Headers
    content-type application/json;odata=verbose
    X-HTTP-Method MERGE
    Accept application/json;odata=verbose

    Body
    {‘__metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’=IF(ISNUMBER([Closed Date]),”Closed”,IF([Next Action Date]>=TODAY(),”Indue”,”Overdue”))’}

    This is the formula used.

    1. Jhingst Avatar
      Jhingst

      I’m running into the same issue. Have you/has anyone found a fix to this?

  15. Kien Nguyen Avatar

    it works, i just retype ” on fomular

  16. Flávio Neto Avatar
    Flávio Neto

    I keep getting this error on the flow (The parameter __metadata does not exist in method GetByTitle.). What can I be doing wrong?

  17. Akash Avatar
    Akash

    Keep getting an error >Bad Request – Invalid Hostname

  18. db Avatar
    db

    Hi:)

    I am trying to submit the following – but only get an error message:
    {‘__metadata’:{‘type’:’SP.FieldCalculated’},’Formula’:’=IF(ISBLANK([Task zu erledigen bis]);””;[Task zu erledigen bis]-Today())’}

    What’s wrong?

  19. NewGuyWithQuestions Avatar
    NewGuyWithQuestions

    Hi,

    I’ve tried this and was sure to replace all the text as people have pointed out. I was first of all getting an error on a missing colon (:) after ‘type’. Since putting this in, I’m now getting this error about missing commas;

    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.

    Any ideas folks?

Leave a Reply

Your email address will not be published. Required fields are marked *