I had to do something with SharePoint lists today and had some fun with PnP PowerShell in the process. Here’s what I had to do. (What follows is a made up example).
I have a bunch of lists that contain the prices of an item per country. For instance, there’s 6 lists for 6 items.
Here’s what one of the lists looks like:
All 6 lists have the same contries, and the price of the item in that country.
What I wanted to do was have country specific lists that showed the price of the 6 items for that country. Initially I thought I might be able to do a lookup or something between lists, but that doesn’t work. Instead I decided to create new lists that are populated via PnP PowerShell.
To begin with, let’s get the names of the items and the countries. I do the first by finding all lists with “Item xx Prices” as the name. And for the latter I simply read all the Title field values from one of these lists.
|
1 2 3 |
$itemNames = (Get-PnpList | Where-Object { $_.Title -match "^Item.*Prices$" }).Title $countryNames = (Get-PnPListItem -List "Item 1 Prices").FieldValues.Title |
Then I’d want to process each country. Check if there’s a list with that country name already, and if not create it. Then check if the list (new or existing) has a field called Price, and if not create it. And lastly go through each of the item lists, find the price of the item for that country, and add these as rows to the newly created list.
I did it this way (the snippet includes the two lookups I showed above too).
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
$itemNames = (Get-PnpList | Where-Object { $_.Title -match "^Item.*Prices$" }).Title $countryNames = (Get-PnPListItem -List "Item 1 Prices").FieldValues.Title foreach ($country in $countryNames) { $newListName = "Country: $country" Write-Progress "Processing $newListName" try { Get-PnPList -Identity "$newListName" -ErrorAction Stop | Out-Null Write-Progress "List exists $newListName" } catch { if ($_.Exception.Message -match "does not exist at site") { Write-Progress "Creating new list $newListName" try { New-PnPList -Title "$newListName" -Template GenericList -ErrorAction Stop | Out-Null Write-Progress "Created new list $newListName" Start-Sleep -Seconds 5 } catch { Write-Warning "Something went wrong. Skipping." continue } } } $newfield = "Price" if ((Get-PnPField -List "$newListName").Title -notcontains "$newfield") { Write-Progress -Id 1 "Adding the $newfield field" try { Add-PnPField -List "$newListName" -DisplayName "$newfield" -InternalName "$newfield" -Type "Note" -AddToDefaultView -ErrorAction Stop | Out-Null } catch { Write-Warning "Error adding the $newfield field. Skipping." continue } } foreach ($item in $itemNames) { Write-Progress -Id 1 "Processing item $item" # find the field used for that country try { $result = Get-PnPListItem -List "$item" -ErrorAction Stop | Where-Object { $_.FieldValues.Title -eq "$country" } } catch { Write-Warning "Error getting field. Skipping." continue } $values = @{ "Title" = "$item" "Price" = if ($result.FieldValues.Keys -contains "Price") { $result.FieldValues.Price } } # The query is a CAML query - https://pnp.github.io/powershell/cmdlets/Get-PnPListItem.html#example-6 try { Write-Progress -Id 2 "Looking up item $item in list $newListName" $listItem = Get-PnpListItem -List "$newListName" -ErrorAction Stop ` -Query "<View><Query><Where><Eq><FieldRef Name='Title'/><Value Type='Text'>$item</Value></Eq></Where></Query></View>" } catch { Write-Warning "Error getting existing item. Skipping." continue } if ($listItem) { Write-Progress -Id 2 "Updating existing item" try { Set-PnPListItem -List "$newListName" -Values $values -Identity $listItem -ErrorAction Stop | Out-Null } catch { Write-Warning "Error updating $field in $newListName" } } else { Write-Progress -Id 2 "Creating new item" try { Add-PnPListItem -List "$newListName" -Values $values -ErrorAction Stop | Out-Null } catch { Write-Warning "Error adding $field to $newListName" } } } } |
Now I can have this running periodically (I suppose I could look into triggering this whenever there’s a change to the item lists) and have the country lists up to date. Nice, huh! 😊
Here’s an example country list:
And here’s all the automatically created lists, with 6 items each:
