When working with SharePoint, there are scenarios where you need to update a library/list item’s information but want to keep the system fields, like Modified and Modified By, intact. In this blog post, I’ll walk you through how to update custom columns in a SharePoint list while preserving these system columns.

Why Is This Useful?

Imagine you have a live application and need to add new columns to an existing list or modify data without affecting the historical metadata, such as who modified or last modified the item. We want ensure that only the new information is updated and the record’s creation and modification details remain untouched.

Scenario

Let’s take a SharePoint list named “Desks Reservations” where we store Desk Name, Description, etc. We now need to add an 'Reserved By email'-column and populate it with existing records without changing the original Created By, Created, Modified By and Modified fields.

1. Using PnP PowerShell

We could use a PnP PowerShell script to modify specific fields while preserving the metadata related to who modified or created the item. The script would include a section that might look something like this:

# Update the fields
Set-PnPListItem -List $ListName -Identity $ItemId -Values @{
    "Title" = "Test Title"; 
    "Category"="Test Category"
}

# Preserving system fields explicitly
Set-PnPListItem -List $ListName -Identity $ItemId -Values @{
    "Created" = $page.FieldValues["Created"];
    "Modified"= $page.FieldValues["Modified"];
    "Author" = $page.FieldValues["Author"].Email;
    "Editor" = $page.FieldValues["Editor"].Email;
}

2. Using Power Automate

Another alternative (and the focus of this article) for performing this type of operation is to use Power Automate.
Power Automate provides actions like 'Update Item' or 'Send an HTTP request to SharePoint' that can be used to update fields in a SharePoint list. When updating custom fields, you have to take additional steps to preserve the system columns by capturing the original values and then restoring them, similar to PowerShell.

So, how it works?
By default, whenever you update a SharePoint item, the system will change the 'Modified' and 'Modified By' fields to reflect the current user and the time the update was made. What we will do is send an HTTP request to submit the original values along with our updates, similar to the PowerShell approach.

Set up

1. Create an Instant Cloud Flow: make.powerautomate

2. Retrieve SharePoint items:

3. Add Compose Actions: Use two Compose actions to store the 'Created By'-email and 'Modified By'-email for each item. This will allow us to re-apply these values after we update the custom field.

4. Create a JSON structure: Include the custom fields you wish to update (like 'Reserved By email') and also pass the system fields that we want to preserve.

Date formating expression:

formatDateTime(items('Apply_to_each')?['Created'], 'yyyy-MM-dd hh:mm:ss')

Note: Author is the internal name for the Created by-field and Editor is the internal name for the 'Modified by'-field.

5. Send the update request: Use the Send an HTTP request to SharePoint action to post this data to your list. The method will be POST and the URI will point to the specific list and item:

_api/web/lists/getbytitle('Desk Reservations')/items(ID)/ValidateUpdateListItem

The bNewDocumentUpdate property is used in the HTTP request to indicate whether the update should be treated as a new or as a regular item update. When it's set to false, it prevents the system fields from being automatically updated with new metadata.

PowerShell vs. Power Automate

My thoughts

The choice between PowerShell and Power Automate depends on your specific needs. If you're performing large-scale operations, PowerShell might be the best option. On the other hand, if you’re looking for a more accessible, low-code solution with an intuitive interface, Power Automate is ideal.

Testing

Before:

Behind the scenes

After Voilà

Conclusion

Ultimately, the right choice depends on the complexity of your task and your familiarity with scripting. Regardless of the tool you choose, you can efficiently manage SharePoint data while preserving system metadata.