Indhold

Lister og kolonner fra et Excel-ark med PnP PowerShell

Én Excel-fil beskriver listerne, ét ark pr. liste. Et script opretter, opdaterer og sletter kolonner, så udvikling, test og produktion er ens.

Opdateret 15. oktober 2026SharePoint OnlineLister og kolonnerPowerShell 11 min. læsning

Mit script fra april 2023. En Excel-fil beskriver listerne på et websted: ét ark pr. liste og én linje pr. kolonne. Scriptet opretter listen, hvis den mangler, opretter eller opdaterer hver kolonne ud fra en XML-definition og sletter de kolonner på listen, som ikke står i arket. Den samme fil køres mod udvikling, test og produktion, så listerne er ens alle tre steder.

Excel-filen læses med modulet ImportExcel (Install-Module ImportExcel), så Excel skal ikke være installeret. XML-formen for kolonnerne er fra Kofi Frimpongs “Create SharePoint field types with XML using PnP PowerShell”.

Excel-filen#

Arkets navn er listens titel. Et ark med navnet _BackendData springes over og kan bruges til opslagsværdier i selve Excel-filen. Kolonnerne i arket:

Kolonne	Bruges til
`Name`	Kolonnens interne navn. Det er nøglen: findes navnet på listen, bliver kolonnen opdateret, ellers oprettet
`DisplayName`	Visningsnavnet
`Type`	`Text`, `Note`, `Number`, `Currency`, `DateTime`, `Choice`, `URL`, `User`, `Calculated`, `Boolean`, `Lookup` eller `Thumbnail`
`Description`	Beskrivelsen
`Required`	`TRUE` eller `FALSE`
`Default`	Standardværdi (Number, Currency, Boolean, Choice)
`NumberMin`, `NumberMax`, `Decimals`	Number og Currency
`LCID`	Currency: valutaens land, fx 1030 for kroner
`DateFormat`	`DateOnly` eller `DateTime`
`DateViewFormat`	`Standard` eller `Friendly`
`UserSelectionMode`	`PeopleOnly` eller `PeopleAndGroups`
`CHOICEFormat`	`Dropdown` eller `RadioButtons`
`FillInChoice`	`TRUE` eller `FALSE`: må brugeren skrive sin egen værdi
`CHOICE1` … `CHOICE10`	Valgmulighederne. Tomme felter springes over
`CALCFormula`, `CALCResultType`	Formlen og resultatets type (`Text`, `Number`, `DateTime`, `Boolean`, `Currency`)
`LookupListSource`, `LookupShowField`	Opslagslistens titel eller id, og den kolonne, der vises

Forbindelsen#

-Interactive kræver en egen appregistrering med -ClientId, siden PnP fjernede sin fælles app i september 2024. Til en kørsel uden bruger står forbindelsen med certifikat i Upload fra et fildrev til SharePoint Online med PowerShell.

Scriptet#

  1Import-Module PnP.PowerShell
  2###########################################################################
  3#  Name: Deploy lists and columns to DEV
  4#  Description: Script that deploys all lists and columns to DEV
  5#  Author: Ulrich Gerting Bojko
  6#  Updated: 23. April 2023
  7#  GUID Generator used: https://www.guidgenerator.com/
  8#  XML-format: https://www.kofrimpong.com/create-sharepoint-field-types-with-xml-using-pnp-powershell/
  9#  Import fra Excel: https://github.com/dfinke/ImportExcel
 10#
 11###########################################################################
 12
 13# Set parameter values
 14$SiteURL="https://<tenant>.sharepoint.com/sites/<websted>"
 15$ClientId = "<klient-id>"
 16$ExcelFilePath = ".\Lister.xlsx"
 17$TemplateListName = "DeployScriptTemplateList123456789"
 18
 19Connect-PnPOnline -Url $SiteURL -Interactive -ClientId $ClientId
 20
 21function fieldtypeText($data) {
 22    $output = "<Field Type='Text' "
 23    $output += "DisplayName='" + $data.DisplayName + "' "
 24    $output += "Name='" + $data.Name + "' "
 25    $output += "Description='" + $data.Description + "' "
 26    $output += "Required='" + $data.Required + "'>"
 27    $output += "</Field>"
 28    return $output
 29}
 30
 31function fieldtypeNote($data) {
 32    $output = "<Field Type='Note' "
 33    $output += "DisplayName='" + $data.DisplayName + "' "
 34    $output += "Name='" + $data.Name + "' "
 35    $output += "Description='" + $data.Description + "' "
 36    $output += "Required='" + $data.Required + "'>"
 37    $output += "</Field>"
 38    return $output
 39}
 40
 41function fieldtypeBoolean($data) {
 42    $output = "<Field Type='Boolean' "
 43    $output += "DisplayName='" + $data.DisplayName + "' "
 44    $output += "Name='" + $data.Name + "' "
 45    $output += "Description='" + $data.Description + "' "
 46    $output += "Required='" + $data.Required + "'>"
 47    $output += "<Default>" + $data.Default + "</Default>"
 48    $output += "</Field>"
 49    return $output
 50}
 51
 52function fieldtypeUser($data) {
 53    $output = "<Field Type='User' "
 54    $output += "DisplayName='" + $data.DisplayName + "' "
 55    $output += "Name='" + $data.Name + "' "
 56    $output += "Description='" + $data.Description + "' "
 57    $output += "UserSelectionMode='" + $data.UserSelectionMode + "' "
 58    $output += "UserSelectionScope='0' "
 59    $output += "Required='" + $data.Required + "'>"
 60    $output += "</Field>"
 61    return $output
 62}
 63
 64function fieldtypeNumber($data) {
 65    $output = "<Field Type='Number' "
 66    $output += "DisplayName='" + $data.DisplayName + "' "
 67    $output += "Name='" + $data.Name + "' "
 68    $output += "Description='" + $data.Description + "' "
 69    if ($null -ne $data.NumberMin) {
 70        $output += "Min='" + $data.NumberMin + "' "    
 71    }
 72    if ($null -ne $data.NumberMax) {
 73        $output += "Max='" + $data.NumberMax + "' "    
 74    }
 75    if ($null -ne $data.Decimals) {
 76        $output += "Decimals='" + $data.Decimals + "' "    
 77    }
 78    $output += "Required='" + $data.Required + "'>"
 79    $output += "<Default>" + $data.Default + "</Default>"
 80    $output += "</Field>"
 81    return $output
 82}
 83
 84function fieldtypeCurrency($data) {
 85    $output = "<Field Type='Currency' "
 86    $output += "DisplayName='" + $data.DisplayName + "' "
 87    $output += "Name='" + $data.Name + "' "
 88    $output += "Description='" + $data.Description + "' "
 89    $output += "LCID='" + $data.LCID + "' "
 90    if ($null -ne $data.Decimals) {
 91        $output += "Decimals='" + $data.Decimals + "' "    
 92    }
 93    $output += "Required='" + $data.Required + "'>"
 94    $output += "<Default>" + $data.Default + "</Default>"
 95    $output += "</Field>"
 96    return $output
 97}
 98
 99function fieldtypeURL($data) {
100    $output = "<Field Type='URL' "
101    $output += "DisplayName='" + $data.DisplayName + "' "
102    $output += "Name='" + $data.Name + "' "
103    $output += "Description='" + $data.Description + "' "
104    $output += "Format='Hyperlink' "
105    $output += "Required='" + $data.Required + "'>"
106    $output += "</Field>"
107    return $output
108}
109
110function fieldtypeDateTime($data) {
111    $FriendlyDisplayFormat = "Enabled"
112    if ($data.DateViewFormat -eq "Standard") {
113        $FriendlyDisplayFormat = "Disabled"
114    }
115    $output = "<Field Type='DateTime' "
116    $output += "DisplayName='" + $data.DisplayName + "' "
117    $output += "Name='" + $data.Name + "' "
118    $output += "Description='" + $data.Description + "' "
119    $output += "Format='" + $data.DateFormat + "' "
120    $output += "FriendlyDisplayFormat='" + $FriendlyDisplayFormat + "' "
121    $output += "Required='" + $data.Required + "'>"
122    $output += "</Field>"
123    return $output
124}
125
126function fieldtypeChoice($data) {
127    $output = "<Field Type='Choice' "
128    $output += "DisplayName='" + $data.DisplayName + "' "
129    $output += "Name='" + $data.Name + "' "
130    $output += "Description='" + $data.Description + "' "
131    $output += "Format ='" + $data.CHOICEFormat + "' "
132    $output += "FillInChoice ='" + $data.FillInChoice + "' "
133    $output += "Required='" + $data.Required + "'>"
134    $output += "<Default>" + $data.Default + "</Default>"
135    $output += "<CHOICES>"
136    for ($i = 1; $i -le 10; $i++) {
137        $choice = $data."CHOICE$i"
138        if ($choice) {
139            $output += "<CHOICE>" + $choice + "</CHOICE>"
140        }
141    }
142    $output += "</CHOICES>"
143    $output += "</Field>"
144    return $output
145}
146function fieldtypeCalculated($data) {
147    $arr = $data.CALCFormula.split("]")
148    $FieldRefOutput = ""
149    foreach($item in $arr)
150    {
151        $FieldRefOutput += $FieldRef
152        $field = $item.split("[")[1]
153        $FieldRef = "<FieldRef Name='" + $field +"' />"
154    }
155
156    $output = "<Field Type='Calculated' "
157    $output += "DisplayName='" + $data.DisplayName + "' "
158    $output += "Name='" + $data.Name + "' "
159    $output += "Description='" + $data.Description + "' "
160    $output += "ResultType='" + $data.CALCResultType + "'>"
161    $output += "<FieldRefs>"
162    $output += $FieldRefOutput
163    $output += "</FieldRefs>"
164    $output += "<Formula>" + [System.Security.SecurityElement]::Escape($data.CALCFormula) + "</Formula>"
165    $output += "</Field>"
166    return $output
167}
168function fieldtypeLookup($data) {
169    # Get list by GUID
170    $List = Get-PnPList -Identity $data.LookupListSource
171    $output = "<Field Type='Lookup' "
172    $output += "DisplayName='" + $data.DisplayName + "' "
173    $output += "Name='" + $data.Name + "' "
174    $output += "Description='" + $data.Description + "' "
175    $output += "List='" + $List.Id + "' "
176    $output += "ShowField='" + $data.LookupShowField + "'/>"
177    return $output
178}
179
180function fieldtypeThumbnail($data) {
181    $output = "<Field Type='Thumbnail' "
182    $output += "DisplayName='" + $data.DisplayName + "' "
183    $output += "Name='" + $data.Name + "' "
184    $output += "Description='" + $data.Description + "' >"
185    $output += "</Field>"
186    return $output
187}
188
189# Get list of fieldnames to be excluded based on an empty standardlist
190$ExcludeFieldNames = [System.Collections.ArrayList]::new()
191
192## Create list of fields that is to be excluded from the removal of fields
193New-PnPList -Title $TemplateListName -Template GenericList
194
195# Get fields from SP List
196$Fields = Get-PnPField -List $TemplateListName
197    ForEach($Field in $Fields){
198        [void]$ExcludeFieldNames.add($Field.InternalName)
199    }
200Remove-PnPList -Identity $TemplateListName -Force
201
202# Get Worksheetnames
203$WorksheetNames = Get-ExcelSheetInfo $ExcelFilePath
204
205# Go through each worksheet
206ForEach($Item in $WorksheetNames)
207{
208$ListName = $Item.Name
209
210# Reset arrays
211$SPListFieldNames = [System.Collections.ArrayList]::new()
212$ExcelListFieldNames = @()
213
214## Create new or update existing fields
215if ($ListName -ne '_BackendData') {
216    
217    # Create list if it does not exist
218    $List = Get-PnPList -Identity $ListName -ErrorAction SilentlyContinue
219
220    if ($Null -eq $List) {
221        New-PnPList -Title $ListName -Template GenericList
222        
223    }
224
225        Import-Excel $ExcelFilePath -WorksheetName $ListName | foreach-object {
226            if ($_.Type -eq 'Text') {
227                $XmlText = fieldtypeText($_)
228            }
229            if ($_.Type -eq 'Note') {
230                $XmlText = fieldtypeNote($_)
231            }
232            if ($_.Type -eq 'Number') {
233                $XmlText = fieldtypeNumber($_)
234            }
235            if ($_.Type -eq 'Currency') {
236                $XmlText = fieldtypeCurrency($_)
237            }
238            if ($_.Type -eq 'DateTime') {
239                $XmlText = fieldtypeDateTime($_)
240            }
241            if ($_.Type -eq 'Choice') {
242                $XmlText = fieldtypeChoice($_)
243            }
244            if ($_.Type -eq 'URL') {
245                $XmlText = fieldtypeURL($_)
246            }
247            if ($_.Type -eq 'User') {
248                $XmlText = fieldtypeUser($_)
249            }
250            if ($_.Type -eq 'Calculated') {
251                $XmlText = fieldtypeCalculated($_)
252            }
253            if ($_.Type -eq 'Boolean') {
254                $XmlText = fieldtypeBoolean($_)
255            }
256            if ($_.Type -eq 'Lookup') {
257                $XmlText = fieldtypeLookup($_)
258            }
259            if ($_.Type -eq 'Thumbnail') {
260                $XmlText = fieldtypeThumbnail($_)
261            }
262
263            $FieldTitle = $_.Name
264            if($FieldTitle -ne $Null)
265            {
266                $Field = Get-PnPField -Identity $FieldTitle -List $ListName -ErrorAction SilentlyContinue | Select InternalName
267            
268                If($Field.InternalName -eq $Null)
269                {
270                    Write-Host "-------------------------------------"-f Yellow
271                    Write-host "The field '$FieldTitle' does not exists in the list '$ListName'. Creating it now...." -f Yellow
272                    Write-Host "-------------------------------------"-f Yellow
273                    # Write-host $XmlText 
274                    Add-PnPFieldFromXml -List $ListName -FieldXml $XmlText    
275                }
276                Else
277                {
278                    # Set-PnPField -List $ListName -Identity $_.Name -Values $fieldObject -ErrorAction Stop
279                    Set-PnPField -List $ListName -Identity $_.Name -Values @{SchemaXml=$XmlText} -ErrorAction Stop
280                    Write-host "Updating field '$FieldTitle' in the list '$ListName'" -f Green
281                }
282
283            }
284
285            # Add Internal name to array for comparison later
286            $ExcelListFieldNames += $_.Name
287        }
288
289        Write-Host "-------------------------------------"
290
291        #######################################################################################################################
292        ## Delete unused fields
293        # Add the excluded fields to the excel-array
294        $ExcelListFieldNames += $ExcludeFieldNames
295
296        # Write-Host "----------`$ExcelListFieldNames-------------------------"
297        # Write-Host $ExcelListFieldNames
298
299        # Build the SPListfield array
300        # Get fields from SP List
301        $Fields = Get-PnPField -List $ListName
302            ForEach($Field in $Fields){
303                [void]$SPListFieldNames.add($Field.InternalName)
304            }
305        # Write-Host "----------`$SPListFieldNames-------------------------"
306        # Write-Host $SPListFieldNames
307
308
309        # Compare the two arrays and delete the difference
310
311        $SPListFieldNames | ForEach-Object {
312            if ($ExcelListFieldNames -notcontains $_) {
313                Write-Host "The field [$_] on the list $ListName is deleted" -f Red
314                Remove-PnPField -List $ListName -Identity $_ -Force
315                Write-Host "-------------------------------------" -f Red
316            }
317        }
318        ##
319        #######################################################################################################################
320    
321    }
322}

Rettet i forhold til 2023-udgaven#

Kommentarerne med # var blevet til [[…]]-links, da noten kom ind i Obsidian. De er sat tilbage. Webstedets adresse er erstattet af en pladsholder, og -ClientId er tilføjet. Ud over det fire fejl, som viste sig, da XML-funktionerne blev kørt i PowerShell 7, og resultatet blev læst som XML:

URL. Format='Hyperlink' blev skrevet efter > og endte som tekst inde i elementet i stedet for som attribut.
DateTime. if ($data.DateViewFormat = "Standard") er en tildeling, ikke en sammenligning, så betingelsen var altid sand, og FriendlyDisplayFormat blev altid Disabled. Nu -eq.
Choice. FillInChoice fik værdien fra CHOICEFormat (fx Dropdown). Den har fået sin egen kolonne i arket. Tomme CHOICE-felter blev til tomme valgmuligheder. Nu springes de over.
Calculated. En formel med <, > eller & gav ugyldig XML. Formlen escapes nu.

Faldgruber#

Sletningen. Alle kolonner på listen, der hverken står i arket eller findes på en tom standardliste, bliver slettet med Remove-PnPField -Force, uden at spørge og med de data, der står i dem. Det gælder også kolonner, som nogen har lagt på i hånden, eller som er kommet med en indholdstype. Første kørsel mod et nyt miljø bør være mod en kopi, eller med sletningen kommenteret ud.
Hjælpelisten. Scriptet opretter DeployScriptTemplateList123456789 for at se, hvilke kolonner en tom liste har, og sletter den igen. Stopper scriptet midt imellem, bliver listen stående og skal fjernes i hånden.
Ingen escaping ud over formlen. Et ', & eller < i et visningsnavn eller en beskrivelse giver ugyldig XML.
Opdatering sætter hele SchemaXml. Set-PnPField -Values @{SchemaXml=…} skriver hele definitionen om. Om typen på en eksisterende kolonne kan skiftes, er uvist.
Opslagskolonner. Listen, der slås op i, skal findes, før arket med opslaget køres. Arkene køres i den rækkefølge, de står i filen.
Beregnede kolonner. FieldRef bygges af navnene i […] i formlen. Scriptet går ud fra, at de er de interne navne.

PnP’s egne skabeloner#

Den anden vej er at eksportere lister fra et websted, der allerede er rigtigt, og lægge dem på et andet:

1Get-PnPSiteTemplate -Out lister.xml -Handlers Lists,Fields,ContentTypes -ListsToExtract "<liste>"
2Invoke-PnPSiteTemplate -Path lister.xml

Get-PnPSiteTemplate eksporterer fra det websted, forbindelsen peger på, og Invoke-PnPSiteTemplate lægger skabelonen på det websted, der er forbundet til bagefter. Skabelonen sletter ingenting. Hvor godt en ny kørsel opdaterer kolonner, der allerede findes, står ikke i PnP’s dokumentation. Excel-vejen har den fordel, at beskrivelsen af listerne kan læses og rettes uden at kende skabelonformatet, og at den fjerner det, der ikke står i den.

Del

Alle noter