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