Skip to main content
Notice removed Canonical answer required by Michael A
Bounty Ended with Damjan Tomic's answer chosen by Michael A
Notice added Canonical answer required by Michael A
Bounty Started worth 50 reputation by Michael A
added 367 characters in body
Source Link
Michael A
  • 1.4k
  • 6
  • 37
  • 60

I've attempted to add the line:

Write-Host $ListItems | ConvertTo-Json

However this doesn't output the contents of the objects themselves, and results in the following:

enter image description here

Is there potentially a way I could amend this to output the fields associated with the files instead (the list field contents)?

I've attempted to add the line:

Write-Host $ListItems | ConvertTo-Json

However this doesn't output the contents of the objects themselves, and results in the following:

enter image description here

Is there potentially a way I could amend this to output the fields associated with the files instead (the list field contents)?

added 855 characters in body
Source Link
Michael A
  • 1.4k
  • 6
  • 37
  • 60

Converting a SharePoint list How to a json fileGetListItems as JSON using Powershell with SharePoint Online?

#Clear Error Log
$Error.Clear()

Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'
Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
Import-Module "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll" 

Write-Host ""
write-host "## SharePoint Online List to JSON converter ##" -ForegroundColor DarkYellow 


# Constant settings, leave blank to specify at runtime
$siteroot = ""
$sitesubsite = ""
$ListName = ""
$AdminUsername = ""

#Connect to MSOL and Exchange Online. Comment out the MSOL session bits to ignore the exchange bits
$MSOLSession = $Null
$PSCredential = $Null
$0365Credential = $Null
#Determine Enviroment
if ($Env:COMPUTERNAME -eq "CLIENT" -and $Env:USERNAME -eq "CLIENT") {
    write-verbose "Azure Automation Services Environment Detected"

    #use Azure Auotmation Services Stored Credentials
    $PSCredential = Get-AutomationPSCredential -Name Office365AzureAdmin
    $0365Credential = Get-AutomationPSCredential -Name ExchangeOnlineAdmin        
} else {
    write-host "Local Environment Detected"
}

$AdminName = [Environment]::UserName
$PCName = $Env:COMPUTERNAME
 
$AdminUsername = ""
$AdminPassword = $null

if ($AdminUsername  -eq "")
{
    Write-Host "Username couldn't be detected" -ForegroundColor Red
    $AdminUsername = Read-Host "Enter username:"
}

if ($siteroot -eq "")
{
    $siteroot = Read-Host "Enter site root (ex https://test.sharepoint.com):"
}

if ($ListName -eq "")
{
    $ListName = Read-Host "Enter list title (ex 'Contacts list'):" 
}

if ($sitesubsite -eq "")
{
    $sitesubsite = Read-Host "Enter subsite (ex /sites/teamsite):" 
}

$FileName = $AdminName + $PCName  
$CredsFile = ".\EncryptedPasswords\$FileName-O365Password.txt"
$FileExists = Test-Path $CredsFile

if  ($FileExists -eq $false) {
    Write-Host "Credential file not found. Enter password for $AdminName :" -ForegroundColor Red
    Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File $CredsFile
    $AdminPassword = get-content $CredsFile | convertto-securestring
            
} else {
    Write-Host 'Using your stored credential file' -ForegroundColor Green
    $AdminPassword = get-content $CredsFile | convertto-securestring
}

#Connect-MsolService -Credential $PSCredential
$siteroot = Read-Host "Enter site root (ex https://test.sharepoint.com):" 
$sitesubsite = Read-Host "Enter subsite (ex /sites/teamsite):" 
$site = $siteroot + $sitesubsite
$ListName = Read-Host "Enter list title (ex 'Contacts list'):" 

#Get the Client Context and Bind the Site Collection
$context = New-Object Microsoft.SharePoint.Client.ClientContext($site$siteroot + $sitesubsite)

#Authenticate
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($AdminUsername, $AdminPassword)
$context.Credentials = $credentials

#Lookup Source Address 
$rootWeb = $Context.Web
$List = $rootWeb.lists.getByTitle($ListName)
$fields = $List.Fields;
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())

#Load the List
$Context.Load($rootWeb)
$Context.load($List)
$Context.Load($ListItems)
$context.Load($fields)
$Context.ExecuteQuery()

Write-Host "List items are"
foreach ($item in $ListItems)
{
    Write-Host "----------------------------------------------"
    Write-Host "Display Title: " $item["Title"]
    Write-Host "Setting:" $item["Setting"]
    Write-Host "File Description:" $item["File Description"]
    Write-Host "Health Area:" $item["Health_x0020_Area"]
    Write-Host "Resource Type: " $item["Resource_x0020_Type"]
    Write-Host "Currently active: " $item["Currently_x0020_active_x003f_"]
    Write-Host "External file path: " $item["External_x0020_file_x0020_path"]
    Write-Host "----------------------------------------------"
}

Converting a SharePoint list to a json file?

#Clear Error Log
$Error.Clear()

Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'
Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
Import-Module "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll" 

Write-Host ""
write-host "## SharePoint Online List to JSON converter ##" -ForegroundColor DarkYellow

#Connect to MSOL and Exchange Online. Comment out the MSOL session bits to ignore the exchange bits
$MSOLSession = $Null
$PSCredential = $Null
$0365Credential = $Null
#Determine Enviroment
if ($Env:COMPUTERNAME -eq "CLIENT" -and $Env:USERNAME -eq "CLIENT") {
    write-verbose "Azure Automation Services Environment Detected"

    #use Azure Auotmation Services Stored Credentials
    $PSCredential = Get-AutomationPSCredential -Name Office365AzureAdmin
    $0365Credential = Get-AutomationPSCredential -Name ExchangeOnlineAdmin        
} else {
    write-host "Local Environment Detected"
}

$AdminName = [Environment]::UserName
$PCName = $Env:COMPUTERNAME
 
$AdminUsername = ""
$AdminPassword = $null

if ($AdminUsername  -eq "")
{
    Write-Host "Username couldn't be detected" -ForegroundColor Red
    $AdminUsername = Read-Host "Enter username:"
}

$FileName = $AdminName + $PCName  
$CredsFile = ".\EncryptedPasswords\$FileName-O365Password.txt"
$FileExists = Test-Path $CredsFile

if  ($FileExists -eq $false) {
    Write-Host "Credential file not found. Enter password for $AdminName :" -ForegroundColor Red
    Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File $CredsFile
    $AdminPassword = get-content $CredsFile | convertto-securestring
            
} else {
    Write-Host 'Using your stored credential file' -ForegroundColor Green
    $AdminPassword = get-content $CredsFile | convertto-securestring
}

#Connect-MsolService -Credential $PSCredential
$siteroot = Read-Host "Enter site root (ex https://test.sharepoint.com):" 
$sitesubsite = Read-Host "Enter subsite (ex /sites/teamsite):" 
$site = $siteroot + $sitesubsite
$ListName = Read-Host "Enter list title (ex 'Contacts list'):" 

#Get the Client Context and Bind the Site Collection
$context = New-Object Microsoft.SharePoint.Client.ClientContext($site)

#Authenticate
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($AdminUsername, $AdminPassword)
$context.Credentials = $credentials

#Lookup Source Address 
$rootWeb = $Context.Web
$List = $rootWeb.lists.getByTitle($ListName)
$fields = $List.Fields;
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())

#Load the List
$Context.Load($rootWeb)
$Context.load($List)
$Context.Load($ListItems)
$context.Load($fields)
$Context.ExecuteQuery()

How to GetListItems as JSON using Powershell with SharePoint Online?

#Clear Error Log
$Error.Clear()

Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'
Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
Import-Module "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll" 

Write-Host ""
write-host "## SharePoint Online List to JSON converter ##" -ForegroundColor DarkYellow 


# Constant settings, leave blank to specify at runtime
$siteroot = ""
$sitesubsite = ""
$ListName = ""
$AdminUsername = ""

#Connect to MSOL and Exchange Online. Comment out the MSOL session bits to ignore the exchange bits
$MSOLSession = $Null
$PSCredential = $Null
$0365Credential = $Null
#Determine Enviroment
if ($Env:COMPUTERNAME -eq "CLIENT" -and $Env:USERNAME -eq "CLIENT") {
    write-verbose "Azure Automation Services Environment Detected"

    #use Azure Auotmation Services Stored Credentials
    $PSCredential = Get-AutomationPSCredential -Name Office365AzureAdmin
    $0365Credential = Get-AutomationPSCredential -Name ExchangeOnlineAdmin        
} else {
    write-host "Local Environment Detected"
}

$AdminName = [Environment]::UserName
$PCName = $Env:COMPUTERNAME
$AdminPassword = $null

if ($AdminUsername  -eq "")
{
    Write-Host "Username couldn't be detected" -ForegroundColor Red
    $AdminUsername = Read-Host "Enter username:"
}

if ($siteroot -eq "")
{
    $siteroot = Read-Host "Enter site root (ex https://test.sharepoint.com):"
}

if ($ListName -eq "")
{
    $ListName = Read-Host "Enter list title (ex 'Contacts list'):" 
}

if ($sitesubsite -eq "")
{
    $sitesubsite = Read-Host "Enter subsite (ex /sites/teamsite):" 
}

$FileName = $AdminName + $PCName  
$CredsFile = ".\EncryptedPasswords\$FileName-O365Password.txt"
$FileExists = Test-Path $CredsFile

if  ($FileExists -eq $false) {
    Write-Host "Credential file not found. Enter password for $AdminName :" -ForegroundColor Red
    Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File $CredsFile
    $AdminPassword = get-content $CredsFile | convertto-securestring
            
} else {
    Write-Host 'Using your stored credential file' -ForegroundColor Green
    $AdminPassword = get-content $CredsFile | convertto-securestring
}

#Get the Client Context and Bind the Site Collection
$context = New-Object Microsoft.SharePoint.Client.ClientContext($siteroot + $sitesubsite)

#Authenticate
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($AdminUsername, $AdminPassword)
$context.Credentials = $credentials

#Lookup Source Address 
$rootWeb = $Context.Web
$List = $rootWeb.lists.getByTitle($ListName)
$fields = $List.Fields;
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())

#Load the List
$Context.Load($rootWeb)
$Context.load($List)
$Context.Load($ListItems)
$context.Load($fields)
$Context.ExecuteQuery()

Write-Host "List items are"
foreach ($item in $ListItems)
{
    Write-Host "----------------------------------------------"
    Write-Host "Display Title: " $item["Title"]
    Write-Host "Setting:" $item["Setting"]
    Write-Host "File Description:" $item["File Description"]
    Write-Host "Health Area:" $item["Health_x0020_Area"]
    Write-Host "Resource Type: " $item["Resource_x0020_Type"]
    Write-Host "Currently active: " $item["Currently_x0020_active_x003f_"]
    Write-Host "External file path: " $item["External_x0020_file_x0020_path"]
    Write-Host "----------------------------------------------"
}
Source Link
Michael A
  • 1.4k
  • 6
  • 37
  • 60

Converting a SharePoint list to a json file?

I need to output the items in a SharePoint list into a *.json file for our web development team.

I've successfully connected to and loaded the list using Powershell, but I'm unsure what the best process to retrieve the items as json would be? Should it I just iterate over them, put them into an object and then serialize it using json.net? Is there an entirely better method than my current approach?

#Clear Error Log
$Error.Clear()

Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'
Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
Import-Module "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll" 

Write-Host ""
write-host "## SharePoint Online List to JSON converter ##" -ForegroundColor DarkYellow

#Connect to MSOL and Exchange Online. Comment out the MSOL session bits to ignore the exchange bits
$MSOLSession = $Null
$PSCredential = $Null
$0365Credential = $Null
#Determine Enviroment
if ($Env:COMPUTERNAME -eq "CLIENT" -and $Env:USERNAME -eq "CLIENT") {
    write-verbose "Azure Automation Services Environment Detected"

    #use Azure Auotmation Services Stored Credentials
    $PSCredential = Get-AutomationPSCredential -Name Office365AzureAdmin
    $0365Credential = Get-AutomationPSCredential -Name ExchangeOnlineAdmin        
} else {
    write-host "Local Environment Detected"
}

$AdminName = [Environment]::UserName
$PCName = $Env:COMPUTERNAME

$AdminUsername = ""
$AdminPassword = $null

if ($AdminUsername  -eq "")
{
    Write-Host "Username couldn't be detected" -ForegroundColor Red
    $AdminUsername = Read-Host "Enter username:"
}

$FileName = $AdminName + $PCName  
$CredsFile = ".\EncryptedPasswords\$FileName-O365Password.txt"
$FileExists = Test-Path $CredsFile

if  ($FileExists -eq $false) {
    Write-Host "Credential file not found. Enter password for $AdminName :" -ForegroundColor Red
    Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File $CredsFile
    $AdminPassword = get-content $CredsFile | convertto-securestring
            
} else {
    Write-Host 'Using your stored credential file' -ForegroundColor Green
    $AdminPassword = get-content $CredsFile | convertto-securestring
}

#Connect-MsolService -Credential $PSCredential
$siteroot = Read-Host "Enter site root (ex https://test.sharepoint.com):" 
$sitesubsite = Read-Host "Enter subsite (ex /sites/teamsite):" 
$site = $siteroot + $sitesubsite
$ListName = Read-Host "Enter list title (ex 'Contacts list'):" 

#Get the Client Context and Bind the Site Collection
$context = New-Object Microsoft.SharePoint.Client.ClientContext($site)

#Authenticate
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($AdminUsername, $AdminPassword)
$context.Credentials = $credentials

#Lookup Source Address 
$rootWeb = $Context.Web
$List = $rootWeb.lists.getByTitle($ListName)
$fields = $List.Fields;
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())

#Load the List
$Context.Load($rootWeb)
$Context.load($List)
$Context.Load($ListItems)
$context.Load($fields)
$Context.ExecuteQuery()