3

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 = "[email protected]"
$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!

3
  • I haven't done much with SPO, but if you're using CSOM, have you tried using ListItem.FieldValues? Commented Dec 1, 2016 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
    Commented Dec 1, 2016 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. Commented Dec 2, 2016 at 15:00

2 Answers 2

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.

0

I had a crack at this because I'm currently migrating an old SharePoint 2010 Foundation server to another a platform.

So, no. It isn't actually an answer to the question about SharePoint Online. But it answers the question in the head line :)

What really got me stomped with SharePoint 2010/PowerShell 2.0 was that the list item properties wasn't listed with the object (and that .Net object notation isn't fully supported). You need to know that they actually are available as hash table lookups :/

$item["Title"]
$item["CustomProp_1"]
etc.

Anyhow...

function Get-mySPListContent {

    [CmdletBinding()]
    param (
        $List
    )

    if ($List.BaseType.ToString() -ne 'DocumentLibrary') {
        
        $ParentWeb = $List.ParentWeb.Url
        $Title = $List.Title 
        $Fields = $List.Fields | Select StaticName, Title, Type, FieldValueType

        [array]$ExtractedList = $null

        foreach ($Item in $List.Items) {

            $ListObject = New-Object PSObject
            foreach ($FieldName in $Fields | Select -ExpandProperty StaticName) {
                Add-Member -InputObject $ListObject -MemberType NoteProperty `
                    -Name $FieldName `
                    -Value $Item[$FieldName]
            }# end fields

            $ExtractedList += $ListObject

        }# end items

        return New-Object PSObject | Select `
            @{l='ParentWeb'; e={$ParentWeb}},
            @{l='Title'; e={$Title}},
            @{l='Fields'; e={$Fields}},
            @{l='List'; e={$ExtractedList}}
    
    }# endif

}

And to use the function...

Add-PSSnapin Microsoft.SharePoint.PowerShell

(Get-SPSite).AllWebs | Select -ExpandProperty Lists | foreach {
    Get-mySPListContent $_ | foreach {
        $_.List | Export-Csv -Path "$($_.Title).csv" -NoTypeInformation
    }
}
1
  • As the function will return empty objects for empty lists, Export-Csv will throw errors for each such list.
    – Dennis
    Commented Dec 7, 2022 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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