2

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:

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

  • How about creating ISAPI service or use CSOM and create service which can provide you JSON output? Also Rest API can solve your problem.. – Ankitkumar Malde Feb 17 '17 at 6:08
  • ISAPI/CSOM would be over engineering what should be fairly routine. I'll take a look into the Rest API further but when I previously looked it didn't appear like it would provide what I was after. – Michael A Feb 17 '17 at 6:26
  • You can follow this link for rest api basics www.codeproject.com/Articles/990131/CRUD-Operation-to-List-Using-SharePoint-Rest-API – Ankitkumar Malde Feb 17 '17 at 7:10
1
+50

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

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();
  • Although appreciated this kind of approach isn't really in line with best practice (something repeatable that won't interfere with users and that can run in Azure)... Any thoughts on the powershell approach? Still upvoted in thanks for the current answer and given the ask I've put a bounty up also. – Michael A Feb 20 '17 at 5:31
1

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

  • +1 for HTML5 download (I didn't know that one yet) Note that it does not work in IE ,since IE sucks at keeping up with modern W3C standards – Danny '365CSI' Engelman Feb 17 '17 at 8:59
  • Oh that's awful man. But glad that it works in Edge. developer.microsoft.com/en-us/microsoft-edge/platform/status/… – Gautam Sheth Feb 17 '17 at 9:14
  • Although appreciated this kind of approach isn't really in line with best practice (something repeatable that won't interfere with users and that can run in Azure)... Any thoughts on the powershell approach? Still upvoted in thanks for the current answer and given the ask I've put a bounty up also. – Michael A Feb 20 '17 at 5:31
1

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 = "user@domain.com"

# 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!

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.