How to GetListItems as JSON using Powershell with SharePoint Online?

Question

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


# 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 "----------------------------------------------"
}

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:

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

Answer 1

Your approach is totally ok. You should change a couple of things to make it work:
For each list item create an object that has all the properties that need to be in json file, put it in the list and at the end convert that list to json string.
After that, save the json string to the file (or do anything else with it… maybe save it as a document in the document library?).
You can use the following code for creating the objects and the json string:

$listArray = New-Object System.Collections.Generic.List[System.Object]
foreach ($item in $ListItems)
{   
    $listArray.Add([hashtable]@{
        DisplayTitle=$item["Title"]; 
        Setting= $item["Setting"]; 
        Description= $item["File_x0020_Description"];
        HealthAreas= $item["Health_x0020_Area"]; 
        ResourceType= $item["Resource_x0020_Type"]; 
        Active= $item["Currently_x0020_active_x003f_"];     
        ExternalURL= $item["External_x0020_file_x0020_path"]; 
        }
    )
}
$json = $listArray | ConvertTo-Json
Write-Host $json

The other problem that you had with ConvertTo-Json is related to the fact that you called Write-Host on the result of ConvertTo-Json command. If you created a variable, assigned the value of ConvertTo-Json to it and then wrote it to host with Write-Host it would had worked fine (I’m not sure why this is the case - it works fine if you try it directly in console and it is totally different if you have it in script file).
The solution for this problem is shown in last two lines of my code above.

Now, if you want to create json from your $ListItems variable, it will throw an error, because the ConvertTo-Json command will try to serialize all of the properties of list items, and those properties are not initialized (you need to explicitly initialize the properties before accessing them). The error will be:

ConvertTo-Json : 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.

The solution is to create the custom objects, assign the values to its properties and convert them to json (the code that I’ve pasted above).

Answer 2

Not PowerShell, a manual way, easier for a one of..

• Open a ListView page, displaying all the Items and Fields you need

• Open F12 Dev tools

• execute JSON.stringify( ctx.ListData ); in the console

• copy the text string from the console
  starting with: "{"Row ending with: :""}"

• open https://jsonformatter.curiousconcept.com/

• paste you JSON text string

• press Process

With JavaScript and without jQuery

var listName=_spPageContextInfo.listTitle;
var xhr = new XMLHttpRequest(); 
xhr.open("GET",_spPageContextInfo.webAbsoluteUrl+"/_api/web/lists/getbytitle('"+listName+"')/Items", true);
xhr.setRequestHeader("Accept","application/json;odata=nometadata");
xhr.onreadystatechange = function (event) {
  if (xhr.readyState === 4 && xhr.status === 200) {
    var items=JSON.parse(this.responseText).value;
    console.info(listName ,' has ' , items.length , 'items' , items);
  }
};
xhr.send();

Answer 3

Add a script editor webpart , inside that add the following content:

<div id="container"></div>
<script src="path-to/jquery.min.js"></script>
<script src="path-to/downloadJSONdata.js"></script>

In the downloadJSONdata.js file, write the below code:

$.ajax({
            url: "https://sitecollectionurl/_api/web/lists/getbytitle('Contacts list')/items",
            method: "GET",
            headers: { "Accept": "application/json; odata=verbose" },
            success: function (data) {
                if(data.d.results.length>0){

                    var dataObject = "text/json;charset=utf-8," + encodeURIComponent(JSON.stringify(data.d.results));

                    var a = document.createElement('a');
                    a.href = 'data:' + dataObject;
                    a.download = 'data.json';
                    a.innerHTML = 'download JSON';

                    var container = document.getElementById('container');
                    container.appendChild(a);
                }
            },
            error: function (data) {
                console.log(data);
            }
});

Adapted from this JSFiddle link - Download JSON file

Answer 4

Try the following PowerShell, it uses the SharePoint REST API to return a JSON response and outputs it to a file.

You can use your current method to import credentials, and leverage any specific filters/ordering/etc available in the SP REST API if necessary or desired.

######################################
# Output List Items in JSON
######################################

######################################
### v EDIT ENVIRONMENT VARIABLES v ###

# login name
[string]$user = "[email protected]"

# login password
[string]$pass = "my_password"

# directory containing files for upload
[string]$webAbsoluteURL = "https://<web-absolute-url>"

[string]$listName = "List Name"

# output file
[string]$output = "C:\Users\admin\Desktop\List_Name.json"

### ^ EDIT ENVIRONMENT VARIABLES ^ ###
######################################

# load client assemblies
Add-Type -Path ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client").location)
Add-Type -Path ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.runtime").location)

$spoCred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($user,(ConvertTo-SecureString $pass -AsPlainText -Force))
$url = $webAbsoluteURL + "/_api/web/lists/GetByTitle('" + $listName + "')/items"
$request = [System.Net.WebRequest]::Create($url)
$request.Credentials = $spoCred
$request.Accept = "application/json;odata=verbose"
$request.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()
$data > $output

Hope this helps!

Answer 5

Using fieldvalues would work:

$listItems.FieldValues | ConvertTo-Json