Get-PnPListItem not retrieving column data

%3CLINGO-SUB%20id%3D%22lingo-sub-810965%22%20slang%3D%22en-US%22%3EGet-PnPListItem%20not%20retrieving%20column%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810965%22%20slang%3D%22en-US%22%3EI'm%20having%20an%20issue%20with%20Get-PnPListItem%20where%20I'm%20getting%20the%20content%20of%20a%20list.%20I'm%20wanting%20to%20make%20a%20change%20on%20each%20item%20in%20the%20list.%20For%20some%20reason%20even%20when%20telling%20the%20command%20which%20columns%20to%20lookup%20some%20are%20empty.%20However%20if%20I%20use%20Get-PnPListItem%20with%20the%20Id%20switch%20it%20does%20retrieve%20all%20the%20needed%20column%20data.%20I've%20done%20the%20script%20to%20use%20Get-PnPListItem%20to%20get%20me%20the%20number%20of%20items%20then%20go%20over%20them%20again%20with%20the%20ID%20to%20get%20the%20data%20but%20this%20is%20so%20slow.%20Is%20there%20something%20I'm%20missing%20from%20Get-PnPListItem%20I%20can%20do%20to%20ensure%20the%20column%20data%20I%20want%20is%20retrieved%20without%20having%20to%20iterate%20over%20each%20item%20individually%20with%20Get-PnPListItem%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-810965%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPnP%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-813428%22%20slang%3D%22en-US%22%3ERe%3A%20Get-PnPListItem%20not%20retrieving%20column%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-813428%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F238903%22%20target%3D%22_blank%22%3E%40David%20Jenkins%3C%2FA%3E%26nbsp%3BHi%20David%2C%20I%20will%20answer%20this%20in%20two%20parts%20First%20get%20all%20the%20list%20columns%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20the%20below%20script%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConnect-PnPOnline%20%3CA%20href%3D%22https%3A%2F%2Fyourorg.sharepoint.com%2Fsites%2Fdemo%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyourorg.sharepoint.com%2Fsites%2Fdemo%2F%3C%2FA%3E-cred%20%24cred%3CBR%20%2F%3E%24ListName%20%3D%20%22List1%22%3CBR%20%2F%3E%24Fields%20%3D%20Get-PnPField%20-List%20%24ListName%3CBR%20%2F%3E%24global%3AFieldsList%20%3D%20%22%22%3CBR%20%2F%3E%24i%20%3D1%3CBR%20%2F%3Eforeach(%24Field%20in%20%24Fields)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%24global%3AFieldsList%20%3D%20%24global%3AFieldsList%20%2B%20%24Field.InternalName%20%2B%20'%2C'%3CBR%20%2F%3EWrite-Host%20%22Field%20Number%20%22%20%24i%20%24Field.InternalName%3CBR%20%2F%3E%24i%2B%2B%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20have%20all%20the%20columns%20select%20the%20columns%20you%20need%20%3A%3C%2FP%3E%3CP%3EThen%20use%20the%20below%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EConnect-PnPOnline%20%3CA%20href%3D%22https%3A%2F%2Fyourorg.sharepoint.com%2Fsites%2Fdemo%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyourorg.sharepoint.com%2Fsites%2Fdemo%2F%3C%2FA%3E-cred%20%24cred%3CBR%20%2F%3E%24ListName%20%3D%20%22List1%22%3CBR%20%2F%3E%24Fields%20%3D%20Get-PnPField%20-List%20%24ListName%3CBR%20%2F%3E%24Items%3D%20(Get-PnPListItem%20-List%20%24ListName%20-Fields%20%22Title%22%2C%22GUID%22%2C%20%22Modified%22%2C%22Created%22%2C%22ID%22)%3CBR%20%2F%3E%24i%3D1%3CBR%20%2F%3Eforeach(%24Item%20in%20%24Items)%3CBR%20%2F%3E%7B%3CBR%20%2F%3EWrite-Host%20%22Item%20%23%22%20%24i%20-ForegroundColor%20Yellow%3CBR%20%2F%3EWrite-Host%20%22Title%20%3A%22%20%24Item%5B%22Title%22%5D%3CBR%20%2F%3EWrite-Host%20%22GUID%20%3A%22%24Item%5B%22GUID%22%5D%3CBR%20%2F%3EWrite-Host%20%22Modified%20%3A%22%24Item%5B%22Modified%22%5D%3CBR%20%2F%3EWrite-Host%20%22Created%20%3A%22%24Item%5B%22Created%22%5D%3CBR%20%2F%3E%24i%2B%2B%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3EFinal%20output%20will%20be%20something%20like%20this%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127663i8E27FD3ACDF62A70%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-813439%22%20slang%3D%22en-US%22%3ERe%3A%20Get-PnPListItem%20not%20retrieving%20column%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-813439%22%20slang%3D%22en-US%22%3EI%20have%20actually%20done%20this%20already.%20Pretty%20much%20the%20same%20procedure%20to%20determine%20the%20internal%20name%20just%20in%20case%20it%20was%20different.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20tested%20by%20adding%20the%20responses%20to%20an%20object%20and%20reviewing.%20Unless%20I%20did%20as%20mentioned%20it%20would%20always%20miss%20a%20few%20columns.%3CBR%20%2F%3E%3CBR%20%2F%3EPart%20of%20the%20reason%20I'm%20messing%20with%20the%20columns%20is%20that%20it%20is%20a%20migration%20from%202013%20and%20has%20many%20lookup%20columns.%20Since%20PowerApps%20doesn't%20support%20that%20many%20lookups%20I%20have%20to%20swap%20the%20lookups%20for%20textboxes%20and%20go%20back%20with%20PowerApps%20and%20add%20DDLs.%20I'm%20wondering%20if%20the%20restrictions%20on%20lookup%20columns%20is%20causing%20the%20issue.%20I%20noticed%20in%20views%20I%20was%20not%20able%20to%20add%20all%20the%20lookup%20columns%20either.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-813675%22%20slang%3D%22en-US%22%3ERe%3A%20Get-PnPListItem%20not%20retrieving%20column%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-813675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F238903%22%20target%3D%22_blank%22%3E%40David%20Jenkins%3C%2FA%3E%26nbsp%3BSorry%20I%20could%20not%20help!%20If%20it%20would%20be%20me%20then%2C%20even%20I%20will%20first%20query%20the%20required%20items%20and%20make%20an%20array%20of%20ids%20for%20which%20I%20need%20to%20run%20some%20updates.%20Then%20I%20would%20just%20pass%20that%20array%20to%20do%20the%20intended%20business%20operation.%20I%20tried%20at%20my%20end%20but%20unfortunately%20I%20could%20not%20replicate%20but%20I%20am%20trying%20in%20a%20new%20site%20not%20in%20an%20migrated%20site.%20That%20might%20be%20the%20difference.%20Best%20of%20luck%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor
I'm having an issue with Get-PnPListItem where I'm getting the content of a list. I'm wanting to make a change on each item in the list. For some reason even when telling the command which columns to lookup some are empty. However if I use Get-PnPListItem with the Id switch it does retrieve all the needed column data. I've done the script to use Get-PnPListItem to get me the number of items then go over them again with the ID to get the data but this is so slow. Is there something I'm missing from Get-PnPListItem I can do to ensure the column data I want is retrieved without having to iterate over each item individually with Get-PnPListItem?
3 Replies

@David Jenkins Hi David, I will answer this in two parts First get all the list columns:

 

Use the below script:

 

Connect-PnPOnline https://yourorg.sharepoint.com/sites/demo/ -cred $cred
$ListName = "List1"
$Fields = Get-PnPField -List $ListName
$global:FieldsList = ""
$i =1
foreach($Field in $Fields)
{
$global:FieldsList = $global:FieldsList + $Field.InternalName + ','
Write-Host "Field Number " $i $Field.InternalName
$i++
}

 

Once you have all the columns select the columns you need :

Then use the below code:

 


Connect-PnPOnline https://yourorg.sharepoint.com/sites/demo/ -cred $cred
$ListName = "List1"
$Fields = Get-PnPField -List $ListName
$Items= (Get-PnPListItem -List $ListName -Fields "Title","GUID", "Modified","Created","ID")
$i=1
foreach($Item in $Items)
{
Write-Host "Item #" $i -ForegroundColor Yellow
Write-Host "Title :" $Item["Title"]
Write-Host "GUID :"$Item["GUID"]
Write-Host "Modified :"$Item["Modified"]
Write-Host "Created :"$Item["Created"]
$i++
}

Final output will be something like this

clipboard_image_0.png

Highlighted
I have actually done this already. Pretty much the same procedure to determine the internal name just in case it was different.

I tested by adding the responses to an object and reviewing. Unless I did as mentioned it would always miss a few columns.

Part of the reason I'm messing with the columns is that it is a migration from 2013 and has many lookup columns. Since PowerApps doesn't support that many lookups I have to swap the lookups for textboxes and go back with PowerApps and add DDLs. I'm wondering if the restrictions on lookup columns is causing the issue. I noticed in views I was not able to add all the lookup columns either.
Highlighted

@David Jenkins Sorry I could not help! If it would be me then, even I will first query the required items and make an array of ids for which I need to run some updates. Then I would just pass that array to do the intended business operation. I tried at my end but unfortunately I could not replicate but I am trying in a new site not in an migrated site. That might be the difference. Best of luck :)