Office 365 Online Development Exporting People Search Results to Excel
In this post I’ll show how we can extract People Search results to Excel. This requirement seems straightforward initially but does require quite a bit of work to function correctly. Furthermore I did not want to write a SharePoint app and wanted the Excel export to work using JavaScript.
My initial research quickly led me to code in a now defunct article. This code shows using custom JavaScript in a custom display template. This approach uses ctx.ListData.ResultTables to get the data from the current page and create a csv file. The problem with this approach is that it will only export search results on the current page and not the entire result set based on search criteria entered. Moreover it only worked on IE.
Nevertheless it provided me with a decent starting point especially since I was already using Custom Display Templates based on People Search Results template. So all I had to do is reference my new JavaScript file and start coding.
So to improve upon the strategy mentioned in the above article, I first had to write a REST query in JavaScript and pass my current search criteria. The JavaScript method to return search results was called from a custom link. This provided me with the complete results set I was looking for to pass to the method that converted the data to csv. I also modified the code to parse any commas as csv is comma delimited and will not work as expected if any of the data in search results contained commas.
In order to make the script work on browsers other than IE, I had to use a trick of creating a temporary link and use the setAttribute method to attach the csv file as a hyperlink. The link is then called using JavaScript and removed from the DOM.
Here is the complete JavaScript method to export the People Search results to a csv file.
[code language=”javascript”]
function getExcel(ctx) {
var a = document.getElementById(“idExportSearchResults”);
var currentSearch = (location.href.indexOf(“k=”) > 0) ? location.href.split(“k=”)[1] : “”;
//if page count exists
currentSearch = (currentSearch.indexOf(“#s=”) > 0) ? currentSearch.split(“#s=”)[0] : currentSearch;
var decodedCurrentSearch = decodeURIComponent(currentSearch);
getSearchResultsUsingREST(decodedCurrentSearch);
}
[/code]
[code language=”javascript”]
function getSearchResultsUsingREST(queryText) {
Results = {
element: ”,
url: ”,
init: function (element) {
Results.element = element;
Results.url = _spPageContextInfo.webAbsoluteUrl + “/_api/search/query?querytext='” + queryText + “‘&sourceId=%27b09a7990-05ea-4af9-81ef-edfab16c4e31%27&rowlimit=3000&selectproperties=’LastName,FirstName,JobTitle,Department,WorkPhone,WorkEmail,BaseOfficeLocation'”;
},
load: function () {
$.ajax(
{
url: Results.url,
method: “GET”,
headers: {
“accept”: “application/json;odata=verbose”,
},
success: Results.onSuccess,
error: Results.onError
}
);
},
onSuccess: function (data) {
var results = data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results;
var propArray2 = [“LastName”, “FirstName”, “JobTitle”, “Department”, “WorkPhone”, “WorkEmail”, “BaseOfficeLocation”];
var exportedResult2 = new Array();
// Get only the required the managed properties.
for (var j = 0; j < results.length; j++) {
var obj = new Object;
for (var i = 0; i < propArray2.length; i++) {
if (results[j].Cells.results[i+2].Value != null)
{
if (results[j].Cells.results[i+2].Value.match(/”|,/))
{
results[j].Cells.results[i+2].Value = ‘”‘ + results[j].Cells.results[i+2].Value + ‘”‘;
}
}
obj[propArray2[i]] = results[j].Cells.results[i+2].Value ? results[j].Cells.results[i+2].Value : “”;
}
exportedResult2.push(obj)
}
showSave(ConvertToCSV(JSON.stringify(exportedResult2), propArray2), “ExportedSearchResult.csv”, “text/csv; charset=UTF-8″);
},
onError: function (err) {
alert(JSON.stringify(err));
}
}
Results.init($(‘#resultsDiv’));
Results.load();
}
[/code]
[code language=”javascript”]
function showSave(data, name, mimeType) {
resultBlob = new Blob([data], { type: mimeType });
if (window.navigator.userAgent.indexOf(“MSIE “) > 0 || !!window.navigator.userAgent.match(/Trident.*rv:11./))
{
navigator.msSaveBlob(resultBlob, name);
}
else //other browsers : Chrome/FireFox (Supported Data URIs)
{
//creating a temporary HTML link element (they support setting file names)
var link = document.createElement(‘a’);
var url = URL.createObjectURL(resultBlob);
link.setAttribute(“href”, url);
link.setAttribute(“download”, “ExportedSearchResult.csv”);
link.style.visibility = ‘hidden’;
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
}
[/code]
[code language=”javascript”]
function ConvertToCSV(objArray, headerArray) {
var array = typeof objArray != ‘object’ ? JSON.parse(objArray) : objArray;
var str = ”;
// Create header row.
var headerLine = ”;
for(var i = 0; i < headerArray.length; i++){
if (headerLine != “”) {
headerLine += ‘,’;
}
headerLine += headerArray[i];
}
str += headerLine + ‘rn’;
// Create CSV body.
for (var i = 0; i < array.length; i++) {
var line = ”;
for (var index in array[i]) {
if (line != ”) {
line += ‘,’;
}
line += array[i][index];
}
str += line + ‘rn’;
}
return str;
}
[/code]
