Sharepoint search rest api - get all pages first published in the last 6 months

Question

Using Postman to test rest apis I'm getting all news posts from a few sites with this:

https://xxx.sharepoint.com/_api/search/query?querytext='SPContentType:"Site Page" AND IsDocument:True AND FileExtension:aspx AND PromotedState:2 AND  Path:https://xxx.sharepoint.com/sites/xxx OR Path:https://xxx.sharepoint.com/sites/xxx OR Path:https://xxx.sharepoint.com/sites/xxx/SitePages'&sortlist='FirstPublishedDate:descending'&startRow=0&rowlimit=100&selectproperties='Title,Description,RefinableString03,FirstPublishedDate,PictureThumbnailURL,WebId,Path'

Now I need to limit the response to only those pages with a first published date occurring in the last 6 months. Can't use Today token in rest api calls and I've tried adding a variable in pre-request script:

var today = new Date();
today.setUTCHours(0,0,0,0); 

And then adding this in a variety of ways using search or regular SP rest to the request url:

$filter=FirstPublishedDate le datetime'" + today.toISOString() + "';

but obviously that expression throws an error "not valid".

Answer 1

Hi @matt howell ,

As far as I know, we could use the KQL syntax to include "today" in the query. Like this:

querytext='SPContentType:"Site Page" AND IsDocument:True AND FileExtension:aspx AND PromotedState:2 AND FirstPublishedDate<today'  

You could refer to this article: https://learn.microsoft.com/en-us/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference

Besides, the js code using filter also works in my end. Please share the code how do you build the url. I notice that you miss a double quote in the end. Could it be the issue.

Below is my rest api:

$.ajax({  
    url: "https://xxx.sharepoint.com/sites/xxx/_api/web/lists/GetByTitle('Site Pages')/items?$filter=FirstPublishedDate le datetime'" + today.toISOString() + "'",  
    ...  
});  

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @matt howell ,

Per my test, kql reserved keyword 'today' works for me, please make sure that you put FirstPublishedDate=today before or condition instead of the end. As the below:

https://xxx.sharepoint.com/_api/search/query?querytext='SPContentType:"Site Page" AND IsDocument:True AND FileExtension:aspx AND PromotedState:2 AND FirstPublishedDate=today AND  Path:https://xxx.sharepoint.com/sites/xxx OR Path:https://xxx.sharepoint.com/sites/xxx OR Path:https://xxx.sharepoint.com/sites/xxx/SitePages'&sortlist='FirstPublishedDate:descending'&startRow=0&rowlimit=100&selectproperties='Title,Description,RefinableString03,FirstPublishedDate,PictureThumbnailURL,WebId,Path'  

Below is my test, results only show site pages published today.

Besides, we cannot get 6 months back from today in kql, today-183 days would not wok in kql. To get 6 months back from today, you have to use code get the date and build the api url with the parameter.

For js code, you should add a variable in pre-request script like this:

var today=new Date();  
today.setUTCHours(0,0,0,0);   
pm.environment.set('today', today.toISOString());  

In postman, we use parameter in the request with the format {{parameter-name}}
Then the request url shoule be like: $filter=FirstPublishedDate le '{{today}}'