question

matthowell-6829 avatar image
0 Votes"
matthowell-6829 asked MichaelHan-MSFT edited

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

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".

office-sharepoint-onlinesharepoint-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelHan-MSFT avatar image
0 Votes"
MichaelHan-MSFT answered matthowell-6829 commented

Hi @matthowell-6829,

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://docs.microsoft.com/en-us/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference

112726-image.png

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.





image.png (76.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @MichaelHan-MSFT The kql reserved keyword 'today' doesn't seem to work - I get the same results if I use FirstPublishedDate<today or `FirstPublishedDate=today.
Also, using kql how would I get 6 months back from today? Like today-183 days.....not mentioned in the doc you referenced.

As for the js code, I'm using Postman so just enter the request url and put the js variable for today in the pre request script tab. Does that sound right?

0 Votes 0 ·
MichaelHan-MSFT avatar image
0 Votes"
MichaelHan-MSFT answered MichaelHan-MSFT commented

Hi @matthowell-6829,

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.

113299-image.png

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

113306-image.png




image.png (36.8 KiB)
image.png (57.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@matthowell-6829,
iIs there anything update? I am willing to hear from you.
If my Answer is helpful, please click "Accept Answer" and upvote it.

0 Votes 0 ·