Find all files on SharePoint Online between two dates

YosiP · September 18, 2023, 9:37pm

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 = 
    "<View>
    <RowLimit Paged = 'TRUE'>100
    <Query>
    <Where>
    <And>
    <Geq>
    <FieldRef Name='Created' />
    <Value Type='DateTime'>2015-01-01T12:00:00Z</Value>
    </Geq>
    <Leq>
    <FieldRef Name='Created' />
    <Value Type='DateTime'>2015-01-30T12:00:00Z</Value>
    </Leq>
    </And>
    </Where>
    </Query>    
    </RowLimit>
    </View>";

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

Can anyone help me figure this out?

Olaf · September 18, 2023, 9:48pm

Joe,
Welcome to the forum.

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*

YosiP · September 19, 2023, 12:58pm

Olaf,
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.

Olaf · September 19, 2023, 3:20pm

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

YosiP · September 19, 2023, 3:36pm

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

Olaf · September 19, 2023, 4:58pm

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 |
        Get-Member

YosiP · September 19, 2023, 5:20pm

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

YosiP · September 19, 2023, 5:29pm

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

Olaf · September 19, 2023, 5:44pm

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!

YosiP · September 19, 2023, 5:57pm

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: 
Created
--------

Olaf · September 19, 2023, 6:10pm

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

OK, what’s the output of …

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

?

YosiP · September 19, 2023, 6:24pm

 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                                                                                                                    
MediaServiceGenerationTime                                                                                                                                               
MediaServiceEventHashCode                                                                                                                                                
Created                          12/8/2014 2:32:08 PM                                                                                                                    
_ExtendedDescription                                                                                                                                                     
TriggerFlowInfo                                                                                                                                                          
MediaServiceImageTags            {}

Olaf · September 19, 2023, 6:30pm

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?

YosiP · September 19, 2023, 6:32pm

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

Olaf · September 19, 2023, 7:02pm

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.

krzydoug · September 19, 2023, 9:49pm

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'
}

Olaf · September 19, 2023, 9:56pm

Why be simple when you can do it the hard way?

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

Olaf · September 19, 2023, 10:12pm

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

krzydoug · September 19, 2023, 10:58pm

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

YosiP · September 20, 2023, 12:48pm

What do you mean by “dictionary”?

Topic		Replies	Views
Comparing dates and then archive files greater than 14 days PowerShell Help	4	129	December 31, 2015
get all files created between x hours and y hours PowerShell Help	7	293	May 5, 2019
LastWriteTime for files PowerShell Help	4	343	March 20, 2021
LastWriteTime not seeing some files. PowerShell Help	5	171	May 6, 2020
Select objects in folder by date range PowerShell Help	9	145	September 5, 2016

Find all files on SharePoint Online between two dates

Related Topics