PowerShell Script to find and replace part of url

[Eddoria]

Good morning, I am new to PS so would appreciate any help.

We have just migrated our SharePoint WSS 3.0 sites to SPO. We have found that in lists which contain a field with URLs, the url in the field has not updated to the relevant SPO location, it is still referencing WSS location. When users go to the list and click on the link in the Pending field, they are being navigated to an incorrect site.

How can I use PS to update field Pending where it begins with https://sharepoint to https://sharepoint1/sites

Thank you in advance.

Vera Hawkins

[#2]

@EddoriaHi Vera,

I'm no expert but the way I would do this is to use the PnP Powershell module.

You can install the module using: Install-Module SharePointPnPPowerShellOnline

And I quickly amended and tested some PS I already had and with the lines below I was able to iterate through my items and change a field value based on finding some match text. I would assume you could amend the script to find the relevant field in the list item and then you would need to work out the bit about chopping and pasting the new value, rather than simply pasting a new value as my code does. But it hopefully gets you close to where you need to be.

Connect-PnPOnline -Url "[url of site/web containing the list]" -Credential Get-Credential

Get-PnPSite ## returns the site object

Get-PnPList ## returns list including Object Id for use below
$list = Get-PnPList -Identity Object ID 0123456789 ##retrieved with Get-PNPList

$items = Get-PnPListItem -List $list -Fields Title,Id,[Otherfields you want] ## reads all items into a variable

foreach($i in $items)
    {
        if($i.FieldValues["-fieldname-"] -cmatch "-string-")
            {
            ##Write-Host $i.FieldValues["-fieldname-"]
            Set-PnPListItem -List $list -Identity $i -Values @{"-fieldname-" = "-newvalue-"}
            }
    }

Hope that helps, and if any PowerShell wizards want to tell me there is a better way I am very happy to hear it! :)

[#3 · Accepted solution]

@Eddoria 

Try this Pnponline powershell script and it will replace the word in your existing URL field value

Connect-PnPOnline -Url https://contoso.sharepoint.com/sites/test
$items =Get-PnPListItem -List testlist
foreach ($item in $items)
{
   if($item.FieldValues["URL"].URL-ne $null)
  {
      $Oldvalue = $item.FieldValues["URL"].URL
      $Newvalue = $Oldvalue -replace "sharepoint","sharepoint1/sites"
      Set-PnPListItem -List testlist -Identity $Item.Id -Values @{"URL" = $Newvalue}
   }
}