Change column values based on other column values using PowerShell

Today, I wanted to share something I’ve learned thanks to the PowerShell Community out there, who responded to my Tweet for Help within minutes! 👏

Scenario

We have a bunch of columns in SharePoint Online, all different types, but more importantly, some are Multi-line of Text columns. This means that users can enter whatever they like: Initials, words in UPPER/lower case, insert special characters, etc… You name it, users will type it!

So in this case, I was looking for a specific abbreviation in just 2 letters (i.e.: BB) and nothing else. If I find that word, then change another column value to be something specific.

So this is what the script should do (change to your requirements):

• Go through each document library called Library1, Library2, etc…
• Check each document (item) and:
  • IF the column called “Other Comments” (multiline type) has BB or bb in it, then amend the column called “Type of Hotel” (choice type) to “Bed and Breakfast”
  • IF the column called “Hotel Location” (choice type) has “Unknown” in it, then remove the data (set to blank) and amend the column called “Country” to “UK”
  • DO NOT change the Modified By and Modified (date) metadata

Tests

When building the script, I thought that using the -match parameter would solve my problem easily. But it didn’t! So I went on trying -cmatch (case sensitive) or -imatch but no luck. Every time, a word like “Rabbit” or “RABBIT” would be picked up… Also tried Anchors to grab a specific word but again, still out of luck…

So that’s when I took on Twitter and managed to get pointed in the right direction by Guy Leech.

Setting the scene

Let’s have a look in SharePoint Online at what should change, and what should not.

What do we have:

• 3x document libraries (Documents, Library1, Library2)
• 5x documents for different scenarios

What should/shouldn’t happen to those documents:

First of all, all those documents should keep the same data for Modified By, same Modified Date, and therefore same Version!

• In the Documents library -–> Nothing should happen despite having the letters ‘BB’ or ‘bb’ in the comments because the library is not targeted in the script

• In Library1:

  • Doc1 (only) should change to Bed and Breakfast
  • NoChangeHere.xlsx should not change at all

• In Library2:

  • Brochure should be amended to Country = UK and Hotel Location = blank
  • Doc2 should be amended to Type of Hotel = Bed and Breakfast + Hotel Location = blank + Country = UK

Let’s go!  

The script

In the below script, we….

• Loop through each targeted library
• Set our 2 conditions
• Set the items to the new values
• Use the -SystemUpdate parameter to prevent any changes in the Modified By, Modified (date), and Version values

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

#Connect to SPO site
Connect-PnPOnline -Url "https://<TENANT-NAME>.sharepoint.com/sites/<YOUR-SITE>"

#Store all the targeted libraries in a variable
$allLists = Get-PnPList | Where-Object {($_.Title -like "Library*") } ## <--- CHANGE TO YOUR LIBRARY NAMES

#Loop thru each targeted document library
foreach ($docLib in $allLists) {
    $allItems = Get-PnPListItem -List $docLib.Title 
    
    foreach ($item in $allItems) {
        
        #IF 'Other Comments' has 'BB' or 'bb' in it
        if (($item.FieldValues.OtherComments -match '\bBB\b') -or ($item.FieldValues.OtherComments -match '\bbb\b')) {
            Write-Host "Amending Type of Hotel to Bed and Breakfast for" $($item["FileLeafRef"]) -ForegroundColor Magenta
            Set-PnPListItem -List $docLib.Title -Identity $item -Values @{"TypeOfHotel" = "Bed and Breakfast"} -SystemUpdate
        }
        
        #IF 'Hotel Location' has 'Unknown' in it
        if ($item.FieldValues.HotelLocation -match 'Unknown') {
            Write-Host "Amending the Country to UK for" $($item["FileLeafRef"]) -ForegroundColor Cyan
            Set-PnPListItem -List $docLib.Title -Identity $item -Values @{"Country" = "UK"; "HotelLocation" = ""} -SystemUpdate
        }
    }
}

Results

After running the script, seems all went well!

You can add a lot of conditions if necessary, but be mindful of what you are trying to do.

This script is designed for a specific Site Collection, and that’s for performance reasons. Remember that we have 2 conditions here. If you need to loop through multiple Site Collections, which loops through multiple libraries, and then through 1000’s of items, you may prefer to do it site by site.

Up to you!

Thanks for reading!