Additional menu

Tachytelic.net

Easily update fields in a SharePoint list with Power Automate

by 53 Comments

When using the SharePoint Update Item action in Microsoft Power Automate it forces you to enter values for mandatory columns. For lists with many columns this can be a long process if you only want to update a few fields. Example:

Image of the SharePoint Update Item Action in Power Automate

This list item contains a number of fields:

Field NameTypeMandatory
NameSingle Line of TextYes
DepartmentSingle Line of TextYes
GenderChoiceNo
Marital StatusChoiceNo
StatusMultiple Selection ChoiceNo
HomepageHyperlink or PictureNo

If you want to be able to update one or more of those fields without supplying the information for every field, there is an easy way to do it.

The Update Item Trick

The update item action helpfully retrieves the column information from SharePoint for us to give us the fields to fill in. Most of the time, this is what we want. But here is how to force it so that it cannot do that.

Simply Create a Compose action and type the name of your list into it. Then create an Update Item action, choose your SharePoint site as normal, but for the list name choose Enter Custom Value and from the dynamic content panel choose the output of your compose action:

Image showing the SharePoint Update Item action in Power Automate being supplied with a dynamic List Name

Now your Update Item action will not suggest any columns at all and you must supply this information manually:

Image showing how the Update Item action behaves when it is supplied with a dynamic list name.

Updating the SharePoint List Item

Our example list has a bunch of different column types and they are all updated in a slightly different way. So I will give examples of how to update each type, and then update multiple fields in a single request.

Single Line of Text

The single line of text field is the easiest to update. Simply supply the field name and the new value, like this:

{
  "Title": "Paulie"
}

With the hyperlink we need to first supply the field name, and then the URL and description.

{
  "homepage": {
    "Url": "http://tachytelic.net",
    "Description": "somesite"
  }
}

Single Choice Column

The single choice column can only take a single value. So, only the field name and value are required.

{
  "Marital_x0020_Status": {
    "Value": "Common-Law"
  }
}

Multi Choice Column

The multi choice column must be supplied with an array of values, so an example would look like this:

{
  "Status": [
    {
      "Value": "Subscribed"
    },
    {
      "Value": "Liked"
    }
  ]
}

Updating Multiple Columns

If you want to update multiple values, you can easily combine the examples above, like this:

{
	"Title": "Paulie",
	"homepage": {
		"Url": "http://tachytelic.net",
		"Description": "somesite"
	},
	"Marital_x0020_Status": {
		"Value": "Common-Law"
	},
	"Status": [{
			"Value": "Subscribed"
		},
		{
			"Value": "Liked"
		}
	]
}
Image showing the Update Item Action in SharePoint being supplied with JSON input to update multiple fields.

Conclusion

So, as you can see from the above, by supplying the JSON directly to the Update Item action, you can easily update columns without having to supply the information for every other column again.

Hope this helps.

Support This Website

Thank you for visiting. It’s because of readers like you that I continue to write on this blog.

If you found value in what I share, I’ve set up a ‘Buy Me a Coffee’ page as a way to show your support.

Buy Me a Coffee

Reader Interactions

Comments

  1. Cameron says

    whoah!
    this is awesome, i’ve been using the SharePoint HTTP action up to now to do this, but your method is /sooo/ much easier.
    good job!

    ==============

    i just tested a few variations of your solution, to see what happens.
    > instead of putting the list name into a compose action, you can also put an expression into the List Name field and that works too.
    for example, these expressions both work:
    concat(‘TestList’)
    string(‘My Awesome List’)

    > if you want to be a ninja :P… you can put a tracked property onto the compose action (or any previous action), and put that into the List Name field and that works too.
    for example, this expression works:
    actions(‘Compose’)?[‘TrackedProperties/MyListName’]

    unfortunately, you cant put a Tracked Property onto the SharePoint Update Item action itself,
    nor can you put the Tracked Property onto a Scope that the SharePoint Update Item action is within 🙁
    but, it seems cool non-the-less.

    ==============

    anyway, Great Post!
    thank you for sharing 🙂

  2. Paulie says

    You have to be careful of putting an expression into the list name field. I also did that and found that it worked, but it also sometimes evaluated the expression and complained that all of the fields were not complete. It’s strange, which is why I left it as a compose. Because it is 100% reliable. Glad you like it, it’s a neat little trick.

  3. Ajay says

    I have been looking to do something like this to update a field in my list. When you have a dynamic entry for the site and library it does not give you the option to select which field/column to update.
    This is exactly what I needed ! Thank you!

  5. Tudor says

    Hi Paulie,

    You are doing a great job here on your blog and YT. Thank you for sharing your smart ideas with us.
    I enjoy discovering your posts, especially this one as I’m searching a method to update faster and easier large lists. I wander if you can come with an update: what about adding some logic in the JSON object so it can dynamically update the values for each field depending on the values or some conditions based in one or more other lists ?

    Thanks and have more time to share your ideas! 😉

  6. Colin says

    Can’t get it to work for a hyperlink field. It just erases any existing content in the field.

  8. Dev says

    Wow! thanks a lot! This has been helpful in all workflows I have.
    I just have a question. How do you do it in a person or group field? I need to update the approver field using the workflow since it’s not automatically being populated.
    Or do you have any solution how to update that field without changing other field?

  9. David Adediran says

    Hi Paul
    You seem to have written this just after I had the issue that inspired this post. This is a neat trick but I’m worried about the depenency of the list display name.
    Anyway, I was forced to use the Send HTTP… action.

  10. Cam says

    @David
    If you need a work around.

    You can put your site + list into environment variables in your solution.
    note: the list environment variable contains the GUID, so it should never change, even if the list display name changes.

    then in your flow call the list endpoint using the HTTP action + the list environment variable to get the list display name.
    note: i use the parameter
    [‘d/EntityTypeName’]

    then once you have the current list display name, use Paulie’s method above to update the list.
    easy 🙂

  12. Rick Savoy says

    @Paul, the error is actually
    “status”: 400,
    “message”: “List View Not Found\r\nclientRequestId:

  14. Kate Zou says

    Hi Paul,

    Thank you for this blog. it’s been great help to the workflow i’ve recently been working on.

    Wonder if it’s possible to use this method to update single person field(dynamic content). i have governance requirement that my workflow needs to capture who has approved it(try to avoid creating another list to store it). Hence, i’m trying to work out how to capture the approver name and update it on SharePoint list.

    Appreciate your response in advance.

    Kate

  17. Daniel Rodriguez says

    Hi Paulie, your method reliably works if using the same value per field for the workflow each time but is there a way to get the JSON snippet use a Power Automate variable’s value?

  18. PJ says

    Like TM, I would like to know how to set a date column to Now() using this method. I’m using it for other fields, but the Date, I’m struggling with.

  22. Frank says

    Do you have a solution for boolean logic, i.e, true, false, yes, no, etc.

    Example { “SentEmailIndicator”: {“Value”:true}}. Receive error “Object must implement IConvertible”. I have tried all variations quoted and none quote, Yes, yes, True, true, fx json(true), fx bool(true), and compose with functions performing same. Still getting the error.

  25. Frank says

    1 or 0 doesn’t work either even when I put the “type”: “bool” and “value”: 1. Still get the IConvertible error.

  26. Frank says

    I fixed the issue. It turns out that my syntax was in error, I think I was over thinking it. My original code was { “SentEmailIndicator”: {“Value”:true}} or { “SentEmailIndicator”: {“Value”: 1}} but, that was for an object that was a select control. However, it really is a single value checkbox so the result that works { “SentEmailIndicator”: 1}. Thanks for your response.

  27. KR says

    Hello, I’m searching for an option to update the list of values that the user can choose from, in a choice field metadata column in Sharepoint library.
    I have 2 metadata fields:
    MainCategory and SubCategory.
    When a user selects the MainCategory the list of the SubCategory has to be updated to show only the subcategories are under the specific MainCategory.
    Is it possible to do dynamic on run time?
    Thanks
    KR

  29. KR says

    Hello Will Cooper and thank for your reply.
    Is this can be executed also in metadata fields in a library?
    (I save my files in libraries and not in lists)
    Best regard

  30. robert says

    Hi,
    This is a really great post!
    What would the type be for a MULTI-LINE text box?
    Using Text does not work 🙁
    Thank you

  31. Robert says

    Hi,
    I am retracting my last question regarding Multi-Line text columns in SharePoint.
    I took the advice of another user and omitted the TYPE.
    Now using “Name”: TheMulti-ColumnDataPill – works
    WAS using “Name”: { “Text:”TheMulti-ColumnDataPill} – type error

    Thank you

  32. Vince says

    The only column type I don’t see is numeric. I have a SP numeric column called RunCount which by default is set to zero. I want to have Power Automate update that to number 1 but keep getting “Object must implement IConvertible.” error. Any assistance would be greatly appreciated

  33. ComeOnSteph says

    What about updating hyperlinks? Ex: If a folder name changes, you’ll need to update the hyperlink to match the new name.

  36. Will Cooper says

    I found out that the Compose block isn’t necessary. You can just use an expression and put the list name in single quotes like this: ‘My List’
    This will have the same effect and streamlines things a bit.

  37. Parik Chauhan says

    Hi,
    Can you please help me with the power automate workflow, which I am trying to create? The purpose is to get the data from the latest csv created on on-drive and update the SharePoint list.

    It is going to be an automated workflow that takes the data from the latest stored file and updates the SharePoint dataset.

  38. Teresa C says

    Hi,
    After new item creation, I need to copy the created date to a new column called ‘Extension Date’. I have not been able to figure out the syntax to accomplish this when using the JSON method to only update one column in the list.

    Any help you can provide is greatly appreciated. Thank you.

  39. anonhelp says

    For date I created a variable (varExtensionDate) before the update action and then used that variable to insert the date like this:
    {
    “ExtensionDate”: varExtensionDate
    }

  40. Pamela Brownfield says

    Thank you awesome information. Is there a way to use this for a People column?

  42. Wikus Von Zijl says

    Hi Paulie, thanks for this was just what I was looking for. However, for some reason the multiple choice options doesn’t work. Seems like it updates the options of the choice column not the selected value. Please go into this a bit deeper. Thanks in advance

  43. Cat G. says

    Thank you for this tutorial! I am having an issue and would greatly love some assistance. I want to update a column, which is a multiple choice column, from “New” to “Awaiting Signature”. The error I keep receiving is “Fix invalid expression for the input parameters of operation “Update_item”.

    Here is my expression:

    {
    “RequestStatus”: [
    {
    “Value”: “New”
    },
    {
    “Value”: “Awaiting Signatures”
    }
    ]
    }

    THANK YOU FOR ANY ASSISTANCE!

  48. Debby says

    This is really helpful, thanks so much!
    Question: Can I update a list item with a variable?
    I need to create a document link and need the ID,
    which I can’t get until it’s submitted. Thanks

  50. Deborah Hines says

    It is working to update the list item with the variable but my variable isn’t working, sigh. I have the attached document file path in variable1 but can’t concat the html code around it in var2 to make it a hyperlink in the SharePoint RTF list column.

  52. Eduard Spelier says

    This is brilliant and solves a long standing irritation I had with Power Automate. However, I can’t get this to work for a Hyperlink field when I want to both submit a description and the URL. The only way I can get a link in is this way:
    {
    “homepage”: “http://tachytelic.net”
    }

    Any ideas on why that might be?

  53. DT says

    fails when attempting to update a date field of a list item.

    I have tried:
    {
    “Date_x0020_applicant_x0020_was_x”: {
    “Value”: “@{formatDateTime(utcnow(), ‘yyyy-MM-dd’)}”
    }
    }

    and:
    {
    “Date_x0020_applicant_x0020_was_x”: {
    “Value”: “@{utcNow()}”
    }
    }

    Any ideas why & solution to fox. Thanks

    output is “status”: 400,
    “message”: “Object must implement IConvertible.\r\nclientRequestId: ………

Leave a Reply

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