3

I want to list the all the files that were created, or modified in a given month in a SharePoint Online library. I would like to further recursively include any files in folders ( but excluding the folders themselves). There are a few issues with the (get-PnPListItems) query below

1) it doesn't work! It brings all the files in my test root library that were created yesterday.

 $query = `
 "<View><Where>  `
   <And>`
      <Geq>`
         <FieldRef Name='Modifed' /> <Value IncludeTimeValue='FALSE'` 
          Type='DateTime'>01/04/2017 12:00:00 AM</Value> `
      </Geq> `
      <Leq> `
        <FieldRef Name='Modifed' /> `
         <Value IncludeTimeValue='FALSE' Type='DateTime'>01/05/2017 12:00:00 
        AM</Value> `
     </Leq> `
    </And> `
   </Where></View>"

2) I ideally want to use variable parameters for the start and end dates I am thinking of this and adding days to get the following on the range. However, I guess this could be improved upon.

   $startDate = [Datetime]::new(2017,04,01) 
1

Your overall CAML query should be as below:

$query = 
 "<View><Where>  
   <And>
      <Geq>
         <FieldRef Name='Modifed' /> <Value IncludeTimeValue='FALSE'
          Type='DateTime'>2017-04-01T00:00:00Z</Value> 
      </Geq> 
      <Leq> 
        <FieldRef Name='Modifed' /> 
         <Value IncludeTimeValue='FALSE' Type='DateTime'>2017-05-01T00:00:00Z</Value> 
     </Leq> 
    </And> 
   </Where></View>"

You have to format the date to ISO 8601 format as below even though you have mentioned IncludeTimeValue=FALSE:

yyyy-MM-ddTHH:mm:ssZ

In SSOM, we used to do it using

var date = SPUtility.CreateISO8601DateTimeFromSystemDateTime(startDate) method

In case of SPO powershell, we need to first use the Get-Date method and then format the date string.

So, your code would be:

$startDate = Get-Date "01/04/2017" 
$startDatestring = $startDate.ToString("yyyy-MM-ddTHH:mm:ssZ");
$endDate = Get-Date "01/05/2017"
$endDatestring = $endDate.ToString("yyyy-MM-ddTHH:mm:ssZ"); 

So your CAML query would be somewhat as below where we pass the date formatted string :

$query = 
 "<View><Where>  
   <And>
      <Geq>
         <FieldRef Name='Modifed' /> <Value IncludeTimeValue='FALSE'
          Type='DateTime'>" + startDatestring + "</Value> 
      </Geq> 
      <Leq> 
        <FieldRef Name='Modifed' /> 
         <Value IncludeTimeValue='FALSE' Type='DateTime'>" + endDatestring + "</Value> 
     </Leq> 
    </And> 
   </Where></View>"
  • thanks very much the $query = "<View><Query><Where> ` <And> ` <Geq> ` <FieldRef Name='Modifed' /> <Value IncludeTimeValue='FALSE' ` Type='DateTime'>" + $startDatestring + "</Value> ` </Geq> ` <Leq> ` <FieldRef Name='Modifed' /> ` <Value IncludeTimeValue='FALSE' Type='DateTime'>" + $endDatestring + "</Value> ` </Leq> ` </And> ` </Where></Query></View>" The issue I have is Get-PnPListItems needs -fields to get Modifed returned but this doesn't work with -Query parameter – westerdaled May 13 '17 at 22:16
  • Instead you have the more cumbersome approach: $items = Get-PnPListItem -List Resources -Query $query -Web $locationWeb -Fields "FileLeafRef", "Modified" | %{new-object psobject -property @{Id = $_.Id; Name = $_["FileLeafRef"]; Modified = $_["Modified"]}} | select Id, Name, Modified; Foreach($item in $items) {write-host $item.Modified.ToString()} Oh well thanks anyway as I do prefer the CAML method. – westerdaled May 14 '17 at 15:30

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.