Sharepoint Online lists with Powershell CSOM, Not able to pull list data

Question

I am trying to Connect to SharePoint Online with Powershell and the Client.dll library with a full control user but am unable to receive list data. I seem to be able to connect with my credentials but am unable to pull list data. I continually get the following errors:

MethodInvocationException: \Powershell\script.ps1:36:1 Line | 36 | $Context.ExecuteQuery() | ~~~~~~~~~~~~~~~~~~~~~~~ | Exception calling "ExecuteQuery" with "0" argument(s): "The remote server returned an error: (400) Bad Request."

InvalidOperation: \Powershell\script.ps1:44:2 Line | 44 | $ListItems | foreach { | ~~~~~~~~~~~~~~~~~~~~~~~ | An error occurred while enumerating through a collection: The collection has not been initialized. It has not been requested or the request has not been executed. It may need to be | explicitly requested..

here is my code:

Add-Type -Path "C:/Program Files/WindowsPowerShell/Modules/SharePointPnPPowerShellOnline/3.19.2003.0/Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:/Program Files/WindowsPowerShell/Modules/SharePointPnPPowerShellOnline/3.19.2003.0/Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:/Program Files/WindowsPowerShell/Modules/SharePointPnPPowerShellOnline/3.19.2003.0/Microsoft.SharePoint.Client.WorkflowServices.dll"

#Config Parameters
$SiteURL= "https://myorg.sharepoint.com/sites/mysite/"
$ListName="my list"
$ExportFile = "networklocation.csv"
$UserName = "myUser"
$Password = "myPassword"

#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))

#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $Credentials

#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)

#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$Context.Load($ListItems)
$Context.ExecuteQuery()

#Array to Hold List Items
$ListItemCollection = @()

 $ListItems |  foreach {
    $ExportItem = New-Object PSObject
    $ExportItem | Add-Member -MemberType NoteProperty -Name "DepartmentName" -value $_["DepartmentName"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "ProjectTitle" -value $_["ProjectTitle"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "ActivityDescription" -value $_["ActivityDescription"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Account" -value $_["Account"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Department"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Fund Code" -value $_["Fund Code"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Fund Source" -value $_["Fund Source"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "ProjectGroup" -value $_["ProjectGroup"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Fund Code" -value $_["Fund Code"]
    $ListItemCollection += $ExportItem
 }
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $ExportFile -NoTypeInformation

Write-host "List data Exported to CSV file successfully!"```

strange, I checked Your code and in my case it worked without any error. I tried to update SharePointPnPPowerShellOnline to the version You are having (3.19.2003.0) and I had trouble downloading it via Update-Module... maybe there is some problem with it. Could You try to lower down the version of PnPPowerShellOnline (I have 3.15.1911 - didn't update it for some time :) ). You can install specific version using -RequiredVersion param. Like: 'Install-Module -Name SharePointPnPPowerShellOnline -RequiredVersion 3.5.1901.0' — Adam

Jerry Jerry · Accepted Answer · 2020-04-07T06:43:17

Please download and install SharePoint Online CSOM Library from here:

SharePoint Online Client Components SDK

And then reference dll like below:

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"


#Config Parameters
$SiteURL= "https://Tenant.sharepoint.com/sites/dev/"
$ListName="mylist7"
$ExportFile = "D:\UserData1.csv"
$UserName = "[email protected]"
$Password = "password"

#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))

#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $Credentials

#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)

#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$Context.Load($ListItems)
$Context.ExecuteQuery()

#Array to Hold List Items
$ListItemCollection = @()

 $ListItems |  foreach {
    $ExportItem = New-Object PSObject
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Title" -value $_["Title"]
    $ListItemCollection += $ExportItem
 }
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $ExportFile -NoTypeInformation

Write-host "List data Exported to CSV file successfully!"```

Tested in PowerShell ISE, it's working as expected:

Sharepoint Online lists with Powershell CSOM, Not able to pull list data

1 Answers