Find all files on SharePoint Online between two dates

I am trying to write a script in PowerShell that copies all my files from one site to another site. I am trying to keep costs down by only taking so many files at the same time. I figured I would do this with a date range on Created Date. But I cannot get it to use date to filter the list nor can it get it to sort the list by Created. Here is what I have tried:

Get-PnPListItem -List $ListName  -PageSize 500  | Where-object {$_.FieldValue.Created -GT [DateTime]'02/16/2015'} 
Get-PnPListItem -List $ListName -PageSize 500 | where-object {($_.FileSystemObjectType -eq "File" -and $_.Created -gt [DateTime]'02/16/2015')} 
Get-PnPListItem -List $ListName -PageSize 500 | where-object { $_.FileSystemObjectType -eq "File" -and $_['Created_x0020_Date'] -gt $Created } 
$ListItems = $ListItems | Sort $_.Created # also tried $_['Created_x0020_Date']

If I use a CAML query I always get the exceed the list view threshold error even with defined in the query.

    $Query = 
    <RowLimit Paged = 'TRUE'>100
    <FieldRef Name='Created' />
    <Value Type='DateTime'>2015-01-01T12:00:00Z</Value>
    <FieldRef Name='Created' />
    <Value Type='DateTime'>2015-01-30T12:00:00Z</Value>

     Get-PnPListItem -List $ListName  -PageSize 500  -Query $Query

Can anyone help me figure this out?

Welcome to the forum. :wave:t3:

I have no experiences with SharePoint. But if the propertys name is really Created this should do the trick to get it sorted:

$ListItems |
    Sort-Object -Property Created

Once you have the property Created as direct property and once as sub property of the property FieldValue. What’s the correct one? And are you actually sure the name of the property is Created and that it is of the type [DateTime]?

What’s the output of

Get-PnPListItem -List $ListName  -PageSize 500  | 
    Get-Member -Name *created*

I tried your Get-Member on created and modified and neither of them returned anything I suspect that is because they are in a subfield called FieldValues so that is why you see FieldValues. Created. The idea for the uses of these is from multiple website examples. If I -expandproperty on FieldValues it is listed on the output. I guess I was hoping there was something obviously wrong with my code.

And what is the output of that? Do you still need help or have you solved your issue already?

I am sorry who do I do get-member on a field in a subfield?

You mentioned …

So you pipe this output to Select-Object and provide the subproperty name … Created seemingly.

.... |
Select-Object -ExpandProperty FieldValues |
    Select-Object -Property Created |

TypeName: Selected.System.Collections.Generic.Dictionary`2[System.String,System.Object]

Name MemberType Definition

Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Created NoteProperty object Created=null

here is proof that it is populated
Created 12/8/2014 2:32:08 PM

Please format your code, error messages AND CONSOLE OUTPUT as code.

Hhhhmmm … looks like it is a hashtable with a string in it. What’s the output of …

.... |
Select-Object -ExpandProperty FieldValues |
    Select-Object -Property Created |
        Select-Object -Property Created


… formatted as code please! :point_up:t3:

I used -expandproperty and this is what I got as part of the results
Created 12/8/2014 2:32:08 PM
But if I use your code

.... |
Select-Object -ExpandProperty FieldValues |
    Select-Object -Property Created |
        Select-Object -Property Created
Then I get this: 

That’s all? Nothing underneath the “--------”?

OK, what’s the output of …

.... |
Select-Object -ExpandProperty FieldValues |
    Select-Object -ExpandProperty Created 


 Get-PnPListItem -List 'Documents'  -Id 4722 |
 Select-Object -ExpandProperty FieldValues | 
    Select-Object -ExpandProperty Created 
Select-Object : Property "Created" cannot be found.
At line:3 char:5
+     Select-Object -ExpandProperty Created
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (System.Collecti...,System.Object]:PSObject) [Select-Object], PSArgumentException
    + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

It takes to long to do the whole list so I just picked one id out and ran it on that. And like I said if I just do this:

Get-PnPListItem -List 'Documents'  -Id 4722 |
 Select-Object -ExpandProperty FieldValues

I get this, it of course is just part of the results

Modified                         12/8/2014 2:32:09 PM                                                                                                                    
Created                          12/8/2014 2:32:08 PM                                                                                                                    
MediaServiceImageTags            {}

You should save this list in a variable. So you don’t have to run the query again and again.

And you can still do that on the list coming from a variable.

The output of …

Get-PnPListItem -List 'Documents'  -Id 4722 |
    Select-Object -ExpandProperty FieldValues |
        Select-Object -ExpandProperty Created

Should be only the date, right?

if I do this query I get nothing as you can see there is a file with a great tan date of 01/01/2015 I get nothing back.

Get-PnPListItem -List 'Documents'  -id 29970 | Where {$_.FieldValue.Created -ge '01/01/2015'} ;$listItems.count

If the type you get back from this query is not of [DateTime] you have to convert it before you can compare it to a [DateTime].

If you already saved your complete list in the variable $ListItems you may try this:

$Limit = Get-Date -Day 1 -Month 1 -Year 2015

$ListItems | | 
    Where-Object {
        ([datetime]::ParseExact($_.FieldValue.Created,'M/d/yyyy h:m:s tt',[System.Globalization.CultureInfo]::GetCultureInfo('en-US'))) -lt $Limit

This should give you all documents before the 1st of January 2015.

It is a datetime. Fieldvalues is a dictionary. I haven’t figured out the silly camel query yet, but this should get YosiP going without it.

Get-PnPListItem -List $ListName -PageSize 500 | Where-Object {
    $_.fieldvalues['Created'] -gt [DateTime]'01/01/2015' -and
    $_.fieldvalues['Created'] -lt [DateTime]'01/31/2015'

You can even omit the cast to datetime since the LHS is a datetime

Get-PnPListItem -List $ListName -PageSize 500 | Where-Object {
    $_.fieldvalues['Created'] -ge '01/01/2015' -and
    $_.fieldvalues['Created'] -le '01/31/2015'
1 Like

Why be simple when you can do it the hard way? :man_shrugging:t3: :roll_eyes:

Sometimes I wonder if MSFT programers do those stuff on purpose. :smirk:

1 Like

Shouldn’t the second comparison operator be -lt or -le? :wink:

1 Like

Copy pasta fail - thanks for always having my back Olaf.

1 Like

What do you mean by “dictionary”?