Updating read-only SharePoint fields with Power Automate

Published in

Waterstons Development

10 min readMar 3, 2021

How to create a reusable child Flow to do this for you

It’s fairly common when working with Power Automate to want to be able to update the values of read-only fields in a SharePoint list.

A prime example would be an approval workflow process, where you have approval fields on the list that a user should be able to see but not change. Imagine a basic user field that captures the person who approved or rejected the list item.

Out-of-the-box SharePoint approvals do capture a single approver, however with multiple stages of approval you may well want to capture each individual approver at each stage in different fields— something that basic approvals don’t do.

Unfortunately, Power Automate can’t provide any sort of elevated privileges to update the value of a read-only field, nor can a Site Collection Admin do it (although even if they could, using a Site Collection Admin account for the SharePoint connection in Power Automate would generally be a bad idea…). Calling the SharePoint REST API directly isn’t even enough to get around it.

The only way to do this at time of writing is to take the field and:

Disable read-only
Update the field value
Re-enable read-only

Not ideal, but also not the end of the world given the speed at which Power Automate will be able to do this. It is, however, a little fiddly, and something you’re not going to want to do afresh every time you need to get Power Automate to update a read-only field. Not only is it a right faff, but it’ll clutter up your Flow in no time.

I recently wrote a reusable Flow for a piece of client work that I’d like to share in order to help anyone facing a similar situation. It accepts a list item ID and a JSON object representing the fields that need to be changed, and what their new values should be. It then loops through all of these field changes and repeats the three-step process above to update the values in the list.

As a bonus, it also doesn’t make use of any premium connectors, so at the time of writing it won’t require any additional premium licenses to run.

When I built the Flow I was only targeting a single list, so the child flow isn’t set up to be told what list to target. This would not be a difficult addition to make though and is left as an exercise for the reader 👍

Building the Flow

The first thing is to create a new solution in Power Automate that can house your main Flow and child Flow. This is important as a child Flow can only be called from within the same solution. I made this mistake initially — so save yourself some time and do this first!

Within your solution, create a new manually-triggered Flow. Then in the trigger add two inputs, one Number for the ID and one Text for the JSON string:

Error Handling

Since this is a production Flow, I’m going to set up some error-handling around the body of the internal logic. This means we can return a ‘Success’ status to indicate whether the Flow succeeded or failed in updating all the read-only fields.

For this, the first thing is to initialize a Boolean variable that keeps track of whether the Flow has been successful:

The initial value is bool(true) , which we’ll falsify if we hit a problem.

I’ll then set up three scopes that mimic the sort of ‘Try-Catch-Finally’ construct you’d traditionally see in code:

The Try scope will contain all the logic of the Flow. Catch will then be set up to run if and only if any part of the Try scope fails to run successfully. To do this, click the ellipsis and then ‘Configure run after’:

The Finally scope will then be configured as follows:

This means everything within Finally will run when the Catch scope is skipped, has failed or times out. Note that this is different from a traditional Finally block in that it will not run if the Catch scope was hit and ran successfully. This is because we’ll be returning a value from the Flow within the Catch scope, and we don’t want any further logic to run after this point.

In the body of the Catch scope there is a single ‘Respond to a PowerApp or Flow’ action. I set this up to return a single Yes/No value based on whether the Flow ran successfully or not.

Since we hit the Catch scope, we know that something failed and we should quit the Flow, returning a bool(false) value:

The Finally scope is the same in that it contains a single ‘Respond to a PowerApp or Flow’ action, however this action returns the value bool(variables('Success')) , so it will return true or false depending on the final outcome of the Flow.

Flow Logic

The Flow logic itself resides in the Try scope:

The first step is to convert the JSON string passed into the Flow into an actual JSON object. For this we’ll use the json() function, and all we need to do is create a ‘Compose’ action, and for the input set the value to json(triggerBody()?['text']) :

Next, we’ll need to parse this JSON object in order to extract the information in a form the Flow can make use of. Create a ‘Parse JSON’ action, and for the Content field take the output from the previous step outputs('Field_Values_JSON') .

You’ll then need to provide a schema which I’ve defined as follows. This is the structure that the JSON string passed to the Flow must adhere to. We’ll look at an example of a valid input string later on, but the basic structure is that it contains an array of items , each of which details a field name, field type and a field value that will allow the Flow to call SharePoint and update the field with the value specified:

{
   "type":"object",
   "properties":{
      "items":{
         "type":"array",
         "items":{
            "type":"object",
            "properties":{
               "fieldName":{
                  "type":"string"
               },
               "fieldValue":{
                  "type":[
                     "string",
                     "object"
                  ]
               },
               "fieldType":{
                  "type":"string"
               },
               "fieldTitle":{
                  "type":"string"
               }
            },
            "required":[
               "fieldTitle",
               "fieldName",
               "fieldValue"
            ]
         }
      }
   }
}

Once these are filled in, the action will look as follows:

For the next step, we will set up a loop that will loop through the array of items parsed by the previous step, and for each one make the necessary SharePoint API calls to disable read-only, update the value and enable read-only again.

Create an ‘Apply to each’ action after the ‘Parse JSON’ action which accepts body('Parse_JSON')?['items'] . This is the array of items parsed from the JSON object:

I’ve also implemented a Try-Catch-Finally structure within the loop here. This is ultimately to ensure that if anything goes wrong during the updates, a field that should be read-only won’t be left in a limbo state where it’s still editable.

Within the Try block are two API calls: one to disable read-only for the field and one to update the field value. These use the ‘Send an HTTP request to SharePoint’ action:

The configuration for the first call is:

The fieldTitle and fieldType fields are taken from the current item in the loop:

fieldTitle — items('Update_each_field_value')?['fieldTitle']
fieldType — items('Update_each_field_value')?['fieldType']

The configuration for the second call is:

Similar to above, the fieldName and fieldValue fields are taken from the current loop item :

fieldName — items('Update_each_field_value')?['fieldName']
fieldValue — items('Update_each_field_value')?['fieldValue']

The ID value used in the URI is taken directly from the trigger inputs: triggerBody()['number'] .

In the body of the call, the __metadata.type field will need setting to the following value:

SP.Data.<List Internal Name>ListItem

E.g., for the list 'My Amazing List’ it would look like SP.Data.My_x0020_Amazing_x0020_ListListItem

The ContentTypeId is only required if your list has content types enabled.

If either of the above two calls fails, the Catch scope will be entered. This simply falsifies the Success variable:

The value is bool(false) .

The Finally block in this case is configured to run even after a successful execution of the Catch block, unlike the surrounding error handling. In this way it behaves more like a traditional Finally block:

This means that whatever happens, the action within the Finally block will be run, which in this case is the API call to make the field read-only again. This ensures that we are not left with the field having read-only disabled when it shouldn’t be:

This step uses the ‘Send an HTTP request to SharePoint’ and is configured as follows:

This is exactly the same as the call to make read-only false, but with "ReadOnlyField": true instead.

And that’s it! With the reusable Child Flow created, let’s see how we can make use of it.

Calling the Flow

To call the Child Flow from a Parent Flow, add a ‘Run a Child Flow’ action to another Flow within the same solution.

In the ‘Child Flow’ field for the action, you will see the Child Flow created above as an option in the dropdown. Selecting this will bring up fields for setting each input value that we defined (item ID and updates JSON string):

The ID should be set to the ID of the SharePoint list item in question.

For ‘Field Values’, the easiest way to generate the JSON string is to add a ‘Compose’ action before calling the Child Flow, which has as its input a valid JSON object with the information set as required.

Here is an example to set values for ‘Tech Approved’ and ‘Tech Approver’, which are a Yes/No field and Multi-User field respectively:

fieldTitle — Display name of the field
fieldName — Internal name of the field
fieldValue — Value with which to update the field
fieldType — The internal ‘type’ reference used to tell SharePoint what type of field we’re dealing with. Yes/No fields use SP.Field and User fields use SP.FieldUser . Most field types use SP.Field , but as with User there are some exceptions. For example, lookup fields use SP.FieldLookup .

This can then be passed into the Child Flow by calling the string() function on the output of the above Compose action:

The ‘Field Values’ input above has the value string(outputs('Field_Values')) .

When the Parent Flow runs, this will mean it runs the Child Flow and waits for a response before moving on. In this example, the Child Flow will set the values of the read-only fields ‘Tech Approved’ to ‘True’ and ‘Tech Approver’ to the user with ID 1 on the SharePoint site.

Conclusion

And there we have it! Now that this Child Flow is in place we can call it all over the place in our Parent Flow without causing clutter, and other Flows can also make use of it due to its generic nature.

We can now have read-only fields on the SharePoint list that are visible to all end users, and can keep the Quick Edit enabled which will prevent users from changing the values of these specific fields while allowing them to still edit those they should. The read-only fields will be modified by Power Automate only and the users will be able to see these changes.

Extending the Child Flow so that the JSON input also defines what list to call wouldn’t be hard either. You’d just have to update the JSON schema used by the Child Flow to include a list title or GUID, and then parse this out and use it in the SharePoint REST API calls instead of a hardcoded list reference like I’ve got. It wasn’t necessary for my use case, but I may do it in future if the situation ever calls for it.

Power Automate often takes a lot of trial and error to get things configured just right, especially where REST API calls are concerned that make use of metadata in the request. However, hopefully this article saves you some of the time and pain involved in that process, and proves useful for whatever project it is you’re working on 😁