Push SharePoint Content To Excel Spread Sheet

Question

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

Answer 1

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