2

So I've been trying for the past 4 days now to write a full automated Powershell script that will connect to SharePoint Online, automatically login with credentials (with password located in a file), locate a List Title, and export all fields (Name, Description, Owner, etc.) and items in each field.

I believe it's just about there, but I'm having a problem with the foreach loop returning everything. This is making me pull my hair out and any help would be more than greatly appreciated.

Here's the full code (some parts found online and some parts hand coded):

#Load SharePoint CSOM Assemblie
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"

#Defining Variables
$SiteUrl = "https://site.sharepoint.com"
$ListName = "ListTitle"
$Path = "C:\Users\Username\Desktop\filename.csv"
$UserName = "username@email.com"
$Password = Get-Content "C:\Users\Username\Desktop\password.txt" | ConvertTo-SecureString -Force

#Automated Login Credentials
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL) 
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $password) 
$Context.Credentials = $credentials 

#Retrive 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()

# Turn item into a catch array
$ListItemCollection = @()

#Fetch each list item value to export to excel
$ListItems | foreach {
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Title"]

#Add the object with above properties to the Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV $Path -NoTypeInformation

Write-host "Deed has been done!"

So I can name all fields manually in the foreach loop, but is there a way to just pull everything instead?

Thanks so much!

  • I haven't done much with SPO, but if you're using CSOM, have you tried using ListItem.FieldValues? – Jeff Freeman Dec 1 '16 at 21:36
  • Hey, thanks for the response. I am using CSOM and have tried playing with .FieldValues but I can't get it to work. I also can't find any examples of it being used with SPO to put it together. It seems like SPO is too new to have a lot of scripts written specifically for it. – Chris Dec 1 '16 at 22:52
  • Interesting. Well, the documentation for the on-prem CSOM says that you can also load List.Fields into your context and iterate over that. Article and sample code is here. – Jeff Freeman Dec 2 '16 at 15:00
1

Try :

$ListItems | % {
    $propertiesValues = New-Object PSObject;
    $currentItem = $_;
    $_.FieldValues.Keys | ? {$_ -ne "MetaInfo"} | % {  
           Add-Member -InputObject $propertiesValues -MemberType NoteProperty -Name $_ -Value $currentItem[$_]
        };
    $propertiesValues}

and pipe it to Export CSV. Tested on Pshell 4.0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.