“Is there a way I can update a single column in Power Automate without showing it on the view I use on the update item?“
“I don’t know enough about these HTTP requests to know which pieces are part of the code and which pieces are information that needs to be specific to my site/library.”
There are multiple situations when it’s better to use an HTTP request to update a single SharePoint column than the Power Automate action ‘Update item’.
You don’t want to see all columns in the ‘update’ action as there’re too many. But at the same time, you don’t want to create a special view for each update. Maybe you even have a huge list with view threshold. Or you’re using variables for the site url and list, and the ‘update’ action doesn’t offer you any column.
In all these situations it’s easier to use HTTP request to do the update (unlike some permission setting situations). There are multiple articles or discussion on what the request should look like. But I think most of them overcomplicate it with headers, metadata, type… as on the image below borrowed from here. How can you do it more easily?
Simple HTTP update
As before, start from the ‘Send an HTTP request to SharePoint’ action. The first part will be the same, select a Site Address and Method. But that’s it, now we’ll continue differently.
- We’ll add a method to the web service in the Uri. If we end the Uri with ‘/items(<ID>)’, we need to add complicated headers and body to tell the service what to do. But we can tell the service to do an update directly in the Uri by adding ‘/validateUpdateListItem’ method.
- As already mentioned above, with the ‘validateUpdateListItem’ method we can keep the headers empty. The service already knows that we want to do an update.
- Also the body will be simplified as the method needs just a list of columns to update inside ‘formValues’.
You can take the code from below and use it in your flow, just replace the <placeholders> with your column / value. Use the column internal name, not the display name.
Method: POST
Uri: _api/web/lists/GetByTitle('<ListName>')/items(<ItemID>)/validateUpdateListItem
Body:
{
"formValues":[
{
"FieldName": "<FieldToUpdate>",
"FieldValue": "<ValueToUpdate>"
}
]
}And if you need to update multiple columns, just add all of them inside the ‘formValues’.
Body:
{
"formValues":[
{
"FieldName": "<FieldToUpdate>",
"FieldValue": "<ValueToUpdate>"
},
{
"FieldName": "<FieldToUpdate2>",
"FieldValue": "<ValueToUpdate2>"
}
]
}Update: if you want to update person or group column, you’ll need a specific format for the value. The same applies also to the multiple choice column, date and time column, or multiple lookup column.
Summary
I’d say that the main point of this post is to understand that if the ‘Update item’ doesn’t work for some reason or if it looks complicated, you can always use the HTTP request. And it’s not as complicated as it may seem. I personally am using them in all of my approval / review flows, especially when building approval history table. Every time I need to update one or two columns, HTTP request is my preferred option.
And if you need, you might even update the item without creating another item version.
This is a lifesaver. We’ve been looking for something like this for quite a while. Thanks so much.
Hello Sandra,
thank you, I’m glad I could help.
Tom
This is fantastic, I have struggled to find a solution to replace to old “Update a field” functionality in SP Designer. I have some quite complex forms and the update item function is cumbersome if you have many mandatory fields. This worked perfectly for me.
Thanks so much
Mine’s not working. 🙁
URI: _api/web/lists/GetByTitle(‘Onboard%20Resource’)/items(@{triggerOutputs()?[‘body/ID’]})/validateUpdateListItem
Body: {
“formValues”:[
{
“FieldName”: “Count Modified”,
“FieldValue”: “1”
}
]
}
says column cannot be found
Hello lynne,
you must use that column’s internal name (https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/) as the “FieldName”, not the display name.
FieldName is InternalName not the Title of the column, you may obtain required information here
_api/web/lists/GetByTitle(‘Onboard%20Resource’)/fields/GetByTitle(‘Count%20Modified’)?$select=Title,InternalName
Hi, do you have any idea on how to update a multi-select column with HTTP?
Hello Kelly,
if it’s a multiple choice column, you must separate the values with the ;# character, as described in this blog post.
Hi, do you know if we can increment a field through an HTTP request? This field is a number that can be simulteously updated by different users and I have to find a way to do it in one step.
Hello Eric,
you can use the add(…) expression as the FieldValue:
add(currentValue,1), that’ll increment the currentValue by 1.OMG Thank you so much! I have been confused about this for more than a week! Thank you!
Hi,
I believe there’s an error in the code example. FieldName and FieldValue should be used for all columns, like this:
{
“formValues”:[
{
“FieldName”: “”,
“FieldValue”: “”
},
{
“FieldName”: “”,
“FieldValue”: “”
}
]
}
Hello Frank-Ove,
you’re right, I had too many 2’s in the second example. Thank you for the correction.
Seriously, you are my hero for this…I have been fighting with trying to get the more complicated version of the HTTP call to work for two days. It would not. But your method worked like a dream. Thank you for this! It saved me so much more frustration.
Thank you so much. This is a game change for me!
Great post! What if yo want to update a managed metadata site column defined for a Content Type?
I have a Content Type called Candidate and it has a managed metada site column called DocumentType?
Hello Kevin,
take a look on the new post: https://tomriha.com/how-to-update-sharepoint-managed-metadata-column-power-automate/
Hi Tom
Really like what you’re doing, very practical stuff.
I’m trying to use this but still struggle. My list is called DemoBookings, that’s how it shows in the browser. I renamed it to Demo Bookings later.
I can’t find any guidance on that. Do I put the name as (DemoBookings), (‘DemoBookings’), or (“Demo Bookings”)?
I think it must be the first option because the error I now get is:
The expression “web/lists/GetByTitle(DemoBookings)/items(7)/validateUpdateListItem” is not valid.
Using (‘DemoBookings’), the error was: List ‘DemoBookings’ does not exist at site with URL ‘https://xxxx.sharepoint.com/teams/NZDemo’.
Thanks,
Christine
Hello Christine,
it uses the service ‘GetByTitle’, therefore, it needs the actual list title, in your case including the space and single quotes: ‘Demo Bookings’. You can test the service also by entering it in the browser (so you don’t need to run the flow for test purpose), just add the whole _/web/lists… part after your site name, e.g. tom.sharepoint.com/sites/SiteName/_api/…
Two things I had to work out:
1. If the column name contains spaces, you have to use this format
” to get the column name correct. In other words, replace the space(es) with “_x0020_”.
2. If the column is a Yes/No column use 0 for No and 1 for Yes, not yes or no or true or false.
Error message through me off “Column not found” even when I used x0020 format with yes or no. worked as soon as i changed it to 0 or 1.
Hello Larry,
regarding point 1, you should always use the column internal name as mentioned in the article, replacing spaces with _x0020_ can solve the problem sometimes, but in general it’s better to take it from the column settings: https://tomriha.com/what-is-sharepoint-column-internal-name-and-where-to-find-it/
Absolute game changer! Thank you.
HI, i tried the HTTP request but it is creating a continuous trigger, not sure what went wrong , followed the exact same steps as above.
Hello Afshan,
it’s probably causing the infinite trigger loop, please take a look on this blog post: https://tomriha.com/how-to-avoid-infinite-trigger-loop-in-power-automate/
Hi there, Tom!
I love the way you just made simple a pain of a chore to do in Power Automate. Unfortunately I’m not able to do this, whenever I try to run the flow, I get this exception:
The List ‘Comunicaes Externas’ does not exit in the site with the URL ‘https://[redacted].sharepoint.com/sites/Intranet’.
clientRequestId: 23eb6edd-XXXX-XXXX-XXXX-0458a72b69a1
serviceRequestId: 596e20a0-XXXX-XXXX-XXXX-15e6c0094bf0
Although the list is in that Site URL, as I used it in many other actions throughout the automation. I do understand that the list name is not the same as we see in the frontend, but I did run the request with multiple data points so I could get the exact name as Sharepoint stores it, so I know both are correct:
“Item ID”: 15,
“Item Path”:Lists/Comunicaes Externas/15_.000,
“Link”:https://[REDACTED].sharepoint.com/sites/Intranet/_layouts/15/listform.aspx?PageType=4&ListId=16931713%2Dc515%2D41b3%2Dbecf%2D7a89b2ecb1d9&ID=15&ContentTypeID=0x01006CB18DF4F88F884AA7601AFEC1D574B600F3F0038F75F4F145A542D7182190F830,
“Item Identification”:Lists%252fComunicaes%2bExternas%252f15_.000,
Here are the details I’m using:
Site address: https://[REDACTED].sharepoint.com/sites/Intranet
Method: POST
Uri: _api/web/lists/GetByTitle(‘Comunicaes20%Externas’)/items(15)/validateUpdateListItem
(Here I’ve tried both: ‘Comunicaes Externas’, ‘Comunicaes20%Externas’ and ‘Comunicaes%2bExternas’, with no success.
The body contains only one field, but the error message is always set to not finding the list, doesn’t say anything about the requested content.
Hello Lucas,
take a look on this blog post: https://tomriha.com/use-the-correct-sharepoint-list-name-in-the-power-automate-http-request/, maybe you’re also using a translated version of SharePoint and the list name you see is not the real name of the list.
Hello,
I am very thankful to have found this page. However, I created a document library. This library has 100’s of documents with a ‘choice’ column that needs to be updated. There are 10 current values in this column and only two that need to be updated. Could I use this example to update the data? Also, how would I start the trigger to update them all at once?
Thank you!
Hello Annie,
you can use the solution to update the documents. You should start with the manual trigger as it sounds as a one time action, filter all the documents that should be updated by adding a Filter Query in the ‘Get files (properties only)’ action, and then loop through all the results in ‘Apply to each’ where you use this HTTP request on each document ID returned.
Hello,
Thank you for the response, however, I am struggling a bit with the manual trigger. I have no idea what to choose to even get started. You cannot choose “send an http request to sharepoint” as a trigger. Any help is greatly appreciated.
Thanks!
Hello Annie,
‘Send an HTTP request to SharePoint’ is one of the actions in the flow, not a trigger. The trigger name is ‘Manually trigger a flow’, New flow -> Instant cloud flow -> Manually trigger a flow. Then the ‘Get files (properties only)’, ‘Apply to each’ to process all the results, and ‘Send an HTTP request to SharePoint’ inside the ‘Apply to each’.
Hello Tom,
Thank you for this article, the solution seems like the best way to go around updating single column in a SP site. Unfortunately, I am facing an issue in my flow that uses this http request. The flow run is successful but the list item does not get updated. The result body has the following output:
“ValidateUpdateListItem”: {
“__metadata”: {
“type”: “Collection(SP.ListItemFormUpdateValue)”
},
“results”: [
{
“ErrorCode”: 0,
“ErrorMessage”: “Expired” {Field value that I pass},
“FieldName”: “PartnerStat” {SP lookup col name},
“FieldValue”: “Expired” {Field value that I pass – this is a dynamic power automate content passed that is also a lookup value},
“HasException”: true,
“ItemId”: 2
}
Can you please assist? Thanks a lot!
Hello Pranay,
are you using the right value for the update? The validateUpdateListItem function body looks different based on the target column, e.g. if it’s a lookup column then you must use the lookup id. You can see some examples if you search on the blog: https://tomriha.com/?s=validateupdatelistitem
Hi Tom,
Thanks very much for pointing that out! I wasn’t explicitly using the lookup item id which is why the flow was failing. Upon making the change, it works!
Thanks a lot for the help! Looking forward to more helpful articles 🙂
Hi, I have an issue and a comment
My issue is:
I have a sharepoint list. I use the “when new item is added to sharepoint” list trigger. This Flow creates a yammer post with the key details from the new role so that it can be discussed. The last step is to write the link to the new yammer post into the “Discussion” field on my original list record so that users can jump to the discussion.
At first I used Sharepoint Update Item. All the normal problems and also one of the fields is a choice with multi-select. If I leave this field blank it deletes the original data, but as soon as I type anything into it, it embeds the update item step in a for…each loop. And I don’t want to do it more than once (plus can’t find what to put in the for-each box.
So I wanted to avoid touching that field so used your method. But as soon as I add the JSON body, the Send HTTP request method is again put into a for…each loop. HELP!
My question is… I use sharepoint lists as pseudo DB tables in an app. But Sharepoint is not an RDBMS, so if I try to write the same record twice, I get duplicate records. How do I create the “create row unless already exists” behaviour?
Found the “check if item exists” answer elsewhere in your blog but still flummoxed by the forced “for each”
Hello Toby,
if it’s adding the ‘Apply to each’ step then the input of the field is probably an array = it’s trying to process each value in the array. Take a look on this article to get rid of the array: https://tomriha.com/stop-power-automate-flow-from-adding-apply-to-each-automatically/, and how to check what format it should be to update it without a loop: https://tomriha.com/what-value-to-use-to-input-entire-array-in-power-automate/.
There’s also a post on how to check if an item already exists: https://tomriha.com/how-to-check-if-sharepoint-item-already-exists-in-power-automate/
Hi Tom,
Thank you for sharing this! I have a question though. I want to trigger a flow with a button click on SharePoint page. I am building a LMS. At the bottom of every page, I want my audience to click on a button that reads “Yes, I’m done”. When that button is clicked, I want the “yes” information to be updated on a SharePoint list column. Each page will have its own column titled as “sub-module 1”, sub-module 2, etc to keep me updated on the progress completion of each page. In this case, should I use the “Send the HTTP request to SharePoint” action or “When a HTTP request is received” trigger. Either way, could you please let me list out the actions & trigger required to accomplish this. Been going in circles and still can’t seem to figure this out. Would appreciate your help in this. Thanks in advance.
Hello Komz,
did you consider using a manually started flow (https://tomriha.com/how-to-build-a-link-to-start-manual-power-automate-flow/)? It won’t be the smoothest user experience (maybe you could use some starting parameter to ask some question related to the topic), but you’ll know who started the flow and which flow they started so you can store the information in the SharePoint list.
Thank you so much. The link you shared was helpful in automating my flow. 🙂
This works great for me with one exception so maybe there’s something else I need to do.
I have a ticket system – fairly standard with a status column that I want to update once the Admin is assigned. Using your method I’m able to update the status, however it wipes out the data in one column that is set to required “multi-line text” column.
Flow – “when an item or file is modified” > Get changes for an item or a file (properties only) > Send http request to SharePoint (Get) item/version > Condition (has item changed – admin assigned)> Send http request with Post (your code).
Is there something else I need to do? Or do I need a different approach.
Thanks in advance as this has been a great help.
Hello Kevin,
it shouldn’t update any values expect the ones you define, but if it does then the simplest solution would be to update also the MLoT column with the original value in the HTTP request.
Thanks so much. This worked like a charm.
Hi Tom, I was struggling with the earlier version of this HTTP request (with headers). I continued to get the error that Uri expession was not valid. I have looked at it and tweaked so many times, I cannot see what the issue is. So I came upon this and throught I would update to this revised version of the HTTP request, hoping it would somehow help, which it did not. I like this version much better but I have the same error and thought I would throw it out here for anyone smarter than I on the topic (which is likely everyone on here). The Uri is _api/web/lists/GetByTitle(‘ChangeRequestLog‘)/items(ID)/validateUpdateListItem , where “(ID)” is the dynamic content and my error is: The expression “web/lists/GetByTitle(‘ChangeRequestLog‘)/items(45)/validateUpdateListItem” is not valid.
clientRequestId: 6ab3c023-9f35-4d94-8726-34ec8e7cd39b
serviceRequestId: 54f45ba0-8003-d000-4500-7d69f2e4b955
I appreciate any/all feedback, as I am learning my way through.
Hello Amy,
I don’t know why the error message mentions an expression, but it’s missing the _api/ part at the beginning that’s necessary.
Hi Tom,
Thanks for all of this writeup – how would you update the hyperlink column type using validateUpdateListItem?
(Nobody seems to have written about it, and all other blogs on this seem to be using the headers!)
Thanks!
Hello Hubert,
here you go: https://tomriha.com/update-single-sharepoint-hyperlink-column-in-power-automate-flow/
Hi Tom, very nice written your article. I can not get it completely to work.
I have a JSON action before the HTTP request which eg. gives me a output like this
“dateTimeDigitized”: “2022:10:31 11:06:45”,
When the I look at the final flow output of HTTP request then in Field “Aufnahmedatum” which is a normal text field should appear that Digitized value but instead it shows a empty field value. I do not understand it , it is like it cannot understand that dynamic value, it also shows no error.
{
“ErrorCode”: 0,
“ErrorMessage”: null,
“FieldName”: “Breite”,
“FieldValue”: “2592”,
“HasException”: false,
“ItemId”: 196
},
{
“ErrorCode”: 0,
“ErrorMessage”: null,
“FieldName”: “Aufnahmedatum”,
“FieldValue”: “”,
“HasException”: false,
“ItemId”: 196
This is whatr it gets as input the HTTP request…..
{
“FieldName”: “Aufnahmedatum”,
“FieldValue”: “@{body(‘Parse_JSON’)?[‘properties’]?[‘dateTimeDigitized’]}”
},
Hello Matthias,
it seems that it doesn’t have any value to update, I’d try to add a ‘Compose’ action before the HTTP request and store the @{body(‘Parse_JSON’)?[‘properties’]?[‘dateTimeDigitized’]} in there, just to make sure that it really has some value. If it’s just a Single line of text field then it should store anything in there.
Hi,
I am having the following error
‘Cannot find resource for validUpdateListItem request.’
My code is Method: POST
Body:
{
“formValues”:[
{
“FieldName”: “Fim_Vigencia”,
“FieldValue”: “Fim_Vigencia”
}
]
}
Is it possible to update a field with its own value? So to update a calculated column based on this field?
Hello Antony,
it’s validATEUpdateListItem, you’re missing the ARE part in the request.
Tom –
Well written, thank you! MY problem is that no matter what field I try to update it is always returning “Value cannot be null. Parameter name: formValues”. I have tried everything I can think of to no avail. The only thing unique about this library is that it is using an external column.
_api/lists/getbytitle(‘Contracts’)/items(1291)/validateUpdateListItem
{
“formValues”:[
{
“FieldName”: “Active”,
“FieldValue”: 0
}
]
}
Hello Beth,
if it’s a lookup column to another list then it won’t accept 0 value as there’s no item with ID 0 in the other list. If you’re trying to empty it I’d try using the null expression instead of 0.
This is fantastic, just like your prior post. I did notice that this does not work with lists that have content type enabled. As such, I had to use the prior steps with the Headers. I wanted to confirm if this was correct or should this work with lists that have content-type enabled?
Hello Joel,
if it works for you it’s a correct approach. 🙂
Hi Tom
Can this be done the other way around? That is, can you retrieve a specific column with an HTTP request?
I have the HTTP cheat sheet but nothing on there ..
Thanks,
Christine
Hello Christine,
you can use an HTTP request to retrieve a value only from a specific column in a specific item if that’s what you’re looking for:
_api/lists/getByTitle(‘listName’)/items(itemId)?$select=columnInternalName
You’d have to extract the value from the resulting JSON though (and I’m not sure whether it would be easier to use the ‘Get item’ action with view filter if you want just 1 column).
Hi,
I want to take this approach, but instead of using the system ID, I want to update an item by using a custom id,
so when using the api, in the /items part, we add some kind of filter like this:
_api/web/lists/GetByTitle(”)/items($Filter:CustomItemID=)/validateUpdateListItem
Is this possible? If yes, how can it be done?
Hi Tom, thank you for this article.
Would you have a idea why the same Uri and Body works for PUT but not for POST for us? POST returns error 400 message “Value cannot be null”. The ID is not null and the single string value being updated is not null. I simply change to PUT and it works. Thank you
Hi everyone,
I’m getting a 401 UNAUTHORIZED error. Any ideas?
_api/web/lists/GetByTitle(‘Product request’)/items(@{outputs(‘Get_item_-_Item_informations’)?[‘body/ID’]})/validateUpdateListItem
{
“formValues”:[
{
“FieldName”: “Status”,
“FieldValue”: “Approved”
}
]
}
Hi everyone,
I have not found any solution for my issue. So I decided to go another way. This way is much easier then described above.
This works perfectly: https://www.youtube.com/watch?v=TRyqVD1754A
Uri:
_api/web/lists/GetByTitle(‘Product order’)/items(@{outputs(‘Get_item_-_Item_informations’)?[‘body/ID’]})
Header:
{
“Accept”: “application/json;odata=verbose”,
“IF-MATCH”: “*”,
“X-HTTP-Method”: “MERGE”
}
Body:
{
“Status”:”Approved”
}