A few weeks ago I finally posted a blog on using one of my favorite jQuery libraries FullCalendar.io to create custom calendar views in SharePoint:
Using FullCalendar.io to Create Custom Calendars in SharePoint
I wanted to follow up that post with a similar solution, but instead of using REST to query a SharePoint list for calendar events, I wanted to use SharePoint’s REST Search functionality to create a rollup calendar view. This allows me to create a calendar view for events across multiple lists, sites, or even site collections. And because it’s search based, the results will be security trimmed to that events the user has access to.
Using the Search REST API is a little less straightforward than just querying a list directly using REST and there’s a couple of challenges to understand. Let me break some of those down for you and we’ll go over them in more detail in the video below as well.
The Query
Below is the REST query I used to find all the tasks for my Site Collection:
/_api/search/query?querytext=’SPContenttype:Task PATH:https://myurl.sharepoint.com/sites/stuff((RefinableDate00>2017-07-01 AND RefinableDate00<2017-07-31)OR(RefinableDate00<2017-07-01 AND RefinableDate00>2017-07-01))&selectproperties=’SiteTitle,Title,Url,AssignedTo,ID,RefinableDate02,RefinableDate01’&rowlimit=250
First of all, you can find out more about the Search REST API by going to SharePoint Search REST API overview. I wont’ bore you will all those details, we’ll just dissect the call here so you can understand what’s going on.
/_api/search/query?querytext=‘SPContenttype:Task PATH:https://myurl.sharepoint.com/sites/stuff((RefinableDate00>2017-07-01 AND RefinableDate00<2017-07-31)OR(RefinableDate00<2017-07-01 AND RefinableDate00>2017-07-01))&selectproperties=’SiteTitle,Title,Url,AssignedTo,ID,RefinableDate02,RefinableDate01’&rowlimit=250
First we want to tell the query they we only want the “Task” content type. To be fair the colon means “contains”, so this query is actually saying give me all content types that contain the name Task. So, if you have another content type with the word Task in it, it would be returned by this query (and maybe that’s what you want?). Specifying the content type is critical because we only want to display Tasks in my calendar example. If you want to return some other content type, change this parameter accordingly.
/_api/search/query?querytext=‘SPContenttype:Task PATH:https://myurl.sharepoint.com/sites/stuff((RefinableDate00>2017-07-01 AND RefinableDate00<2017-07-31)OR(RefinableDate00<2017-07-01 AND RefinableDate00>2017-07-01))&selectproperties=’SiteTitle,Title,Url,AssignedTo,ID,RefinableDate02,RefinableDate01’&rowlimit=250
Next we are telling the query that we only want to return results from within my site collection and sub sites that contain this specific path. If we removed the PATH parameter, search would return content from sites collections and sites we have access to (again, maybe what you want?). It is very important when specifying the path to specify the full URL. You cannot say “PATH:/sites/stuff”. This will not yield any results.
/_api/search/query?querytext=’SPContenttype:Task PATH:https://myurl.sharepoint.com/sites/stuff((RefinableDate00>2017-07-01 AND RefinableDate00<2017-07-31)OR(RefinableDate00<2017-07-01 AND RefinableDate00>2017-07-01))&selectproperties=’SiteTitle,Title,Url,AssignedTo,ID,RefinableDate02,RefinableDate01’&rowlimit=250
Next we need to tell the query which Tasks to return. In this example I only want to return the Tasks that have a start date or due date the is either in the month of July or extends through the month of July. Note the required date format. You’ll also note that I’m not querying off of the Site Columns “Start Date” or “Due Date”. That is because these fields are not queryable. What I ended up doing was mapping both my Start Date and Due Date fields to the RefinableDate00 field.
I also made sure the radio button “Include content from all crawled properties” was selected. This allows me to create a very simple query that includes multiple dates. I’ll explain this more in the video…. if I remember to.
/_api/search/query?querytext=’SPContenttype:Task PATH:https://myurl.sharepoint.com/sites/stuff((RefinableDate00>2017-07-01 AND RefinableDate00<2017-07-31)OR(RefinableDate00<2017-07-01 AND RefinableDate00>2017-07-01))&selectproperties=’SiteTitle,Title,Url,AssignedTo,ID,RefinableDate02,RefinableDate01′&rowlimit=250
Next we need to tell the Search query which fields we want to retrieve. In this instance I want to retrieve the name of the site the Task came from (SiteName), the Task Name (Title), the URL to the display form (Url), the person the task is assigned to (AssignedTO), the ID of the Task, the Start Date (RefinableDate02) and the Due Date (RefinableDate01). You may be wondering why I’m using RefinableDate01 and 02 instead of Start Date and Due Date? That is because these fields are not retrievable and I need to map them to fields that are retrievable.
/_api/search/query?querytext=’SPContenttype:Task PATH:https://myurl.sharepoint.com/sites/stuff((RefinableDate00>2017-07-01 AND RefinableDate00<2017-07-31)OR(RefinableDate00<2017-07-01 AND RefinableDate00>2017-07-01))&selectproperties=’SiteTitle,Title,Url,AssignedTo,ID,RefinableDate02,RefinableDate01′&rowlimit=250
Finally, we are going to tell search to return up to 250 results. This is the max you can return by one search query.
The Results
The results from the REST Search query are also not nearly as intuitive as just querying a list with REST. Instead of getting our a nice clean JSON object under d.results, our results are nested much deeper in data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results
What’s more, there is not one JSON object with a property for each field, we have multiple JSON objects with the with a Key and Value property where the Key is the property name and Value is the value of the property. So, I use a switch statement to get all the values I’m looking for out of my search results:
var rows = data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results;
for (index in rows)
{
var cells = rows[index].Cells.results;
for (index2 in cells)
{
var cell = cells[index2];
switch(cell.Key) {
case “AssignedTo”:
assignedTo = cell.Value;
break;
case “Title”:
title = cell.Value;
break;
case “Url”:
url = cell.Value;
break;
case “RefinableDate02”:
start = cell.Value;
break;
case “RefinableDate01”:
due = cell.Value;
break;
case “ID”:
id = cell.ID;
break;
case “SiteTitle”:
site = cell.Value;
break;
default:
break;
}
}
Now that we have our query and understand how to parse the results, the rest of the script is straightforward and follows the same logic as the previous blog post.
The Code
You can download the script and modify it for your own needs by downloading it off my GitHub repository at: https://github.com/mrackley/SPFullCalendarRollup
A couple of other differences from this blog post and the previous are as follows:
- Instead of binding to the click event, I’m using the URL parameter to take the user to the display form for the Task
- I removed the update code for dragging and dropping events to update due dates.
- I took advantage of Waldek’s code from the 1.1 branch to add color coding based on the Site Name the Task came from
That’s pretty much all there is to it! Below is the link to the video walking through the script. Thanks for stopping by and good luck!
A Couple of Final Notes
Working with Search can be frustrating, especially in Office 365. When you map a field to a managed property, you won’t get results immediately. You’ll need to wait until Search runs again. This generally takes 10-15 minutes but I’ve seen it take much longer before as well. The same is true whenever you add an item to a list. It’s not going to show up in Search Results until it’s been index by search. You’ll have to be patient.
Finally, if you are searching across multiple Site Collections you’ll need to map the Managed Properties the same across all the Site Collections for your query to work as you’d expect. If you have access, you can also map the managed properties from the SharePoint Admin Panel in Office 365.
Again, good luck! Make some cool stuff…
Dear Sir,
How configuration to my sharepoint calendar?