2

I have started with the following code to retrieve the List Items for JSON files found in my document library:

Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $siteUrl -UseWebLogin
    $CAMLQuery1 = "<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='FileLeafRef'></FieldRef>
                <Value Type='Text'>content.json</Value>
            </Eq>
        </Where>
    </Query></View>"
    $FolderItems = Get-PnPListItem -List $ListName -Query $CAMLQuery1
    ForEach($Item in $FolderItems)
    {
        Write-host $Item
    }

}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

How do I get the json content of the file I fetched through the CAML request? I tried to find some solution, but couldn't find anything.

2

As far as I know, there is not yet a specific PnP cmdlet to help with this. The trick is that SharePoint doesn't care that the contents of your file are JSON or plain Text, it still stores it as a binary file. So you have to retrieve it as a binary stream, then read that stream into a string variable. Then finally, if you want an actual PowerShell object from that JSON string, you'll need to use the ConvertFrom-Json cmdlet.

The following should get you what you need:

Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $siteUrl -UseWebLogin
    $ctx = get-pnpContext
    $CAMLQuery1 = "<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='FileLeafRef'></FieldRef>
                <Value Type='Text'>content.json</Value>
            </Eq>
        </Where>
    </Query></View>"
    $FolderItems = Get-PnPListItem -List $ListName -Query $CAMLQuery1
    ForEach($Item in $FolderItems)
    {
        Write-host $Item

        $fileinfo = [Microsoft.SharePoint.Client.File]::OpenBinaryDirect($ctx, $Item["FileRef"]) #ServerRelativeUrl
        $sr = new-object System.IO.StreamReader($fileInfo.Stream)

        $textcontent = $sr.ReadToEnd()      
        $jsonObject = ConvertFrom-Json -InputObject $textcontent

        $sr.Dispose()

        # do your actions with the $textcontent or #jsonOjbect here
    }

}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}
| improve this answer | |
2

You can refer the below PowerShell script :

$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

Reference URL :

How to GetListItems as JSON using Powershell with SharePoint Online?

| improve this answer | |

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.