How to get list for items within a week using PowerShell scripts?

Question

I am writing a PowerShell script to get bookings from SharePoint 2007. Booking is made by creating an item in Calender list. The list has already been used for over 6 years. I want to write a script to send SMTP email reminders to users for bookings after 3 days. I used the following code to get the list items.

$service = New-WebServiceProxy -Uri $uri -UseDefaultCredential
...
$xmlDoc = new-object System.Xml.XmlDocument            
$query = $xmlDoc.CreateElement("Query")            
$viewFields = $xmlDoc.CreateElement("ViewFields","Week")            
$queryOptions = $xmlDoc.CreateElement("QueryOptions")            
$query.set_InnerXml("FieldRef Name='Full Name'")
$rowLimit = "50000"
...
if($service -ne $null){
try{
    $list = ($service.GetListItems($listName, $viewName, $query, $viewFields, $rowLimit, $queryOptions, "")).data.row
    $list | export-csv "$path\list.csv"
}catch {
    Write-host "Error !! Cannot update the list.csv."
}

I don't want to get all the items in this 6 years every time i run the script, can i get items only in the next 3 days?

Answer 1

You could loop through your results and use some simple math to compare the dates, using CSOM will make the logic much easier to adapt in the future. You might be able to use the CAML query to do the date filtering too but that's not my forte:

Import-Module 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll'
$siteUrl = "http://Your.Site/Subsite"
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)  
$lookupList = $ctx.Web.Lists.GetByTitle('Bookings')
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXml = "<View>
    <RowLimit></RowLimit>
</View>"
$listItems = $lookupList.getItems($query)
$ctx.Load($listItems)
$ctx.ExecuteQuery()
foreach($item in $listItems)
{
    if([DateTime]$item["DueDate"] -lt [DateTime]::Now.Add([TimeSpan]::FromDays(3)) -and [DateTime]$item["DueDate"] -gt [DateTime]::Now)
    {
        $item["Title"]
    }
}