0

I'm writing a code to get if versioning of documents in a SharePoint site is Enabled, or Disabled. I managed to get that working, but now I need to push the info to an excel file, the information that needs to be populated in it is the list name, the url to it and if versioning is enabled, or disabled. I use SP Services and for the excel part I'm using a JavaScript library I found on the net which is called Excel Plus. I tried different things, but none worked so far, it's returning an empty Excel file only with the names of the columns created, without any info being populated from the SharePoint page. What I'm trying to do is to have this function that is going throughout all SharePoint subsites, libraries, lists and then to get the list name, url and if versioning is enabled, or disabled of each document and push it to the created Excel columns. Here's the full code I've written so far, your help will be very much appreciated:

       var webAddress="SharePoint Site Goes Here";
   var liHtml = [];
   var fieldsStatic =[];
   var fieldsDisplay =[];
    $(document).ready(function() {          
        $().SPServices({
            operation: "GetAllSubWebCollection",
            webURL:webAddress,
            async: false,
            completefunc: function(xData, Status) {                    
                $(xData.responseXML).SPFilterNode("Web").each(function(){
                    $('#outputDataDiv').append("<div class='webRecordTitle'>"
                    +$(this).attr("Title")+" : "
                    + $(this).attr("Url")+"</div>");
                    getListCollection($(this).attr("Url"));
                });
            }
        });

    function getListCollection(webAddress){
         $().SPServices({
            operation: "GetListCollection",
            webURL:webAddress,
            async: false,
            completefunc: function(xData, Status) {
                $(xData.responseXML).SPFilterNode("List").each(function(){
                    if(
                    $(this).attr("ServerTemplate")==101
                    && $(this).attr("Title")!=="Style Library"
                    && $(this).attr("Title")!=="Site Assets")
                    {
                    var outputListHtml = "<div class='listContainer'>"+
                    "<span class='listTitle'>"
                    +$(this).attr("Title")
                    + "</span><br />";
                    outputListHtml += "Total Item Count: "+$(this).attr("ItemCount")+" ";
                    outputListHtml +="</div>";
                    $('#outputDataDiv').append(outputListHtml);
                   }
                });
              }
           });
          } 
       })          

    function getList(webAddress, listName){
            $().SPServices({
            operation: "GetList",
            webURL: webAddress,
            listName: listName,
            async : false,
            completefunc: function(xData, Status) {
                $(xData.responseXML).find("Fields > Field").each(function() {
                    fieldsStatic.push($(this).attr("StaticName"));
                    fieldsDisplay.push($(this).attr("DisplayName"));
              })
            }
          });                        
        }
        getList(webAddress,"TestLibrary");       

    function getListItems(webAddress, listName){
            $().SPServices({
            operation: "GetListItems",
            webURL: webAddress,
            async: false,
            listName: listName,
            CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
            completefunc: function (xData, Status) {
                $(xData.responseXML).SPFilterNode("z:row").each(function() {
                    liHtml.push( "<li>" + $(this).attr("ows_Title") + "</li> ");
                    console.log(liHtml);
                });
              }
            })
        }
        getListItems(webAddress, "TestLibrary");

    var responseText = "" ;
    function getVersion(webAddress, listName){
            var result;
            $().SPServices({
            operation: "GetList",
            async: false,
            webURL: webAddress,
            listName: listName,
            completefunc: function (xData, Status) {
            var responseText = xData.responseText;
            if(responseText.indexOf('EnableVersioning="True"') > -1){
                    result = 'Enabled' ;

            }
            else {
                    result = 'Disabled'
            }
            }
        });
            return {listName: listName, webURL : webAddress, result: result };
    }

    function extractToExcel(sites){
            var ep=new ExcelPlus();
            var cellLetters=['A','B','C'];
            ep.createFile('Versioning');
            ep.write({'cell':'A1','content': 'List Name'});
            ep.write({'cell':'B1', 'content': 'URL'});
            ep.write({'cell':'C1', 'content': 'EnabledVersioning'});
            var array = [];




            ep.saveAs('Versioning.xlsx');
        };
  • Any help will be very much appreciated guys, as I'm stuck with that, thank you in advance! – Benny Blanco Feb 10 '17 at 8:01
  • Hey I'm the creator of ExcelPlus. Give me a couple of hours and I'll see if I can help. – AymKdn Feb 10 '17 at 8:24
  • Hey, nice to meet you in here:) It's very useful what you've created, glad I found it:) Would be very glad if you could help out with this, thank you in advance! – Benny Blanco Feb 10 '17 at 8:44
0

First, it's not good to use "async:false", because it blocks the browser (and because the new versions of the browsers have started to block this behavior). So you need to do everything in async. It's why I've had to change several lines into your code.

Here I'll use jQuery.Deferred() (for the async stuff) because I know it very well, it's available cross-browser with jQuery, and because Promises is not widely available yet.

All the things I've added/changed are commented. I tested and it works:

var webAddress = "SharePoint Site Goes Here";
var liHtml = [];
var fieldsStatic = [];
var fieldsDisplay = [];
$(document).ready(function() {
  $().SPServices({
    operation: "GetAllSubWebCollection",
    webURL: webAddress,
    async: true, // don't use FALSE
    completefunc: function(xData, Status) {
      var listRecords = []; // we'll use it for the Deferred section below

      $(xData.responseXML).SPFilterNode("Web").each(function() {

        listRecords.push($(this).attr("Url")); // store it to use in the Deferred section below

        $('#outputDataDiv').append("<div class='webRecordTitle'>" + $(this).attr("Title") + " : " + $(this).attr("Url") + "</div>");
      });

      // add some jQuery.Deferred() to automatically call `extractToExcel` when evertyhing has been loaded
      // you could also use Promises
      var dfd = jQuery.Deferred().resolve();
      var res = listRecords.map(function(url) {
        dfd = dfd.then(function() {
          return getListCollection(url);
        });
        return dfd
      });
      $.when.apply(this, res).done(function() {
        // we arrive here when all the getListCollection() for each website have been executed
        var finalResultForEeachSite = Array.prototype.slice.call(arguments);
        // we now combine the data for each site and we send it to Excel
        var sites = [];
        finalResultForEeachSite.forEach(function(result) {
          sites = sites.concat(result);
        })
        extractToExcel(sites); // so we can now extract to Excel
      })
    }
  });

  // change this function to return a Deferred object
  function getListCollection(webAddress) {
    var g_deferred=jQuery.Deferred(); // the deferred object

    $().SPServices({
      operation: "GetListCollection",
      webURL: webAddress,
      async: true, // it's not good to use FALSE here
      completefunc: function(xData, Status) {
        var lists = []; // record the different lists
        $(xData.responseXML).SPFilterNode("List").each(function() {
          if ($(this).attr("ServerTemplate") == 101 && $(this).attr("Title") !== "Style Library" && $(this).attr("Title") !== "Site Assets") {

            var listname = $(this).attr("Title"); // use a variable
            lists.push(listname);

            var outputListHtml = "<div class='listContainer'>" + "<span class='listTitle'>" + listname + "</span><br />";
            outputListHtml += "Total Item Count: " + $(this).attr("ItemCount") + " ";
            outputListHtml += "</div>";
            $('#outputDataDiv').append(outputListHtml);
          }
        });

        // now get the versionning for each list
        var dfd = jQuery.Deferred().resolve();
        var res = lists.map(function(listname) {
          dfd = dfd.then(function() {
            return getVersion(webAddress, listname);
          });
          return dfd
        });
        $.when.apply(this, res).done(function() { // this function is called when all the deferred calls are done
          // retrieve all the data returned by getVersion()
          var finalResult = Array.prototype.slice.call(arguments);
          g_deferred.resolve(finalResult);
        })
      }
    });

    return g_deferred; // return the deferred object
  }
})

/*
Don't need the below stuff
function getList(webAddress, listName) {
  $().SPServices({
    operation: "GetList",
    webURL: webAddress,
    listName: listName,
    async: false,
    completefunc: function(xData, Status) {
      $(xData.responseXML).find("Fields > Field").each(function() {
        fieldsStatic.push($(this).attr("StaticName"));
        fieldsDisplay.push($(this).attr("DisplayName"));
      })
    }
  });
}
getList(webAddress, "TestLibrary");

function getListItems(webAddress, listName) {
  $().SPServices({
    operation: "GetListItems",
    webURL: webAddress,
    async: false,
    listName: listName,
    CAMLViewFields: "<ViewFields><FieldRef Name='Title' /></ViewFields>",
    completefunc: function(xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
        liHtml.push("<li>" + $(this).attr("ows_Title") + "</li> ");
        console.log(liHtml);
      });
    }
  })
}
getListItems(webAddress, "TestLibrary");
var responseText = "";
*/

function getVersion(webAddress, listName) {
  var deferred=jQuery.Deferred(); // use deferred
  var result;
  $().SPServices({
    operation: "GetList",
    async: true, // don't use FALSE
    webURL: webAddress,
    listName: listName,
    completefunc: function(xData, Status) {
      var responseText = xData.responseText;
      if (responseText.indexOf('EnableVersioning="True"') > -1) {
        result = 'Enabled';
      } else {
        result = 'Disabled'
      }

      // resolve the deferred and provide the result
      deferred.resolve({
        listName: listName,
        webURL: webAddress,
        result: result
      });
    }
  });

  return deferred; // use deferred
  // the below stuff will return "undefined" for result as it hasn't been defined yet in async
  /*return {
    listName: listName,
    webURL: webAddress,
    result: result
  };*/
}

// this function is now called when all the requests have been done on the server
function extractToExcel(sites) {
  var ep = new ExcelPlus();
  var cellLetters = ['A', 'B', 'C'];
  ep.createFile('Versioning');
  ep.write({
    'cell': 'A1',
    'content': 'List Name'
  });
  ep.write({
    'cell': 'B1',
    'content': 'URL'
  });
  ep.write({
    'cell': 'C1',
    'content': 'EnabledVersioning'
  });
  // go thru the records and write them into the Excel, row by row
  // -> this part was missing in your code, so it's why your Excel file had only the headers :-)
  sites.forEach(function(row) {
    ep.writeNextRow([row.listName, row.webURL, row.result]);
  });

  ep.saveAs('Versioning.xlsx');
};
  • Thank you very much! I'll test it myself now and will let you know in a while, but you have a beer from me anyways:) – Benny Blanco Feb 10 '17 at 11:20
  • Hi again! It's not extracting the Excel file now, so can't tell if the code is working. I tried calling the extarctToExcel function in the console and an Uncaught TypeError appears saying that it cannot read property 'foreach' of undefined. Any suggestions? Thank you! – Benny Blanco Feb 10 '17 at 11:43
  • You cannot call extractToExcel without arguments.... If you want to just test this function you'll have to call it with something like : `extractToExcel([{listName:'hello', webURL:'world', result:'Enabled'}]) ... – AymKdn Feb 10 '17 at 12:56
  • Yes, I figured that out myself:) Thank you a lot, it is working fine! Again, you have a treat from me:)Cheers! – Benny Blanco Feb 10 '17 at 13:12
  • Hi AymKdn, want to ask you how can I made this code not be hardcoded, but to do it alone, without the need to manually write the listname, url and if the version is enabled, but to do it and extract the Excel file alone? Thank you! – Benny Blanco Mar 7 '17 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.