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! 👏
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
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
- Doc1 (only) should change to Bed and Breakfast
- NoChangeHere.xlsx should not change at all
- 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
In the below script, we….
- Loop through each targeted library
- Set our 2 conditions
- Set the items to the new values
- Use the
-SystemUpdateparameter to prevent any changes in the Modified By, Modified (date), and Version values
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!