2

I'm trying to push some info collected from a SharePoint site into an Excel file, the code generates an Excel file, but without pushing the info needed in it. I'm trying to look in all sites, all lists if files have versioning enabled, or disabled and then populate the web url, list name and if files have versioning enabled, or disabled into the spreadsheet file, but it doesn't push the info into it, just creates the columns without populating the info inside. Do please have a look at my code and let me know what's not right with it, thank you in advance:

    var webAddress = $().SPServices.SPGetCurrentSite();
    var liHtml = [];
    var fieldsStatic = [];
    var fieldsDisplay = [];
    var sites = [];
    var listName = [];
    $(document).ready(function() {
    $().SPServices({
        operation: "GetAllSubWebCollection",
        webURL: webAddress,
        async: true, 
        completefunc: function(xData, Status) {
        var listRecords = []; 

        $(xData.responseXML).SPFilterNode("Web").each(function() {
            listRecords.push($(this).attr("Url"));
            $('#outputDataDiv').append("<div class='webRecordTitle'>" + $(this).attr("Title") + " : " + $(this).attr("Url") + "</div>");
        });

        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() {
            var finalResultForEeachSite = Array.prototype.slice.call(arguments);
            var sites = [];
            finalResultForEeachSite.forEach(function(result) {
            sites = sites.concat(result);
            })
            extractToExcel(sites); 
        })
        }
    });


    function getListCollection(webAddress) {
        var g_deferred=jQuery.Deferred(); 

        $().SPServices({
        operation: "GetListCollection",
        webURL: webAddress,
        async: true, 
        completefunc: function(xData, Status) {
            var 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"); 
                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);
            }
            });

            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() { 
            var finalResult = Array.prototype.slice.call(arguments);
            g_deferred.resolve(finalResult);
            })
        }
        });

        return g_deferred; 
    }
    })

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

        deferred.resolve({
            listName: listName,
            webURL: webAddress,
            result: result
        });
        }
    });

    return deferred;
    }

    function extractToExcel() {
    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': 'Versioning'
            });
    for (var i = 0; i < sites.length; i++){
          for (var k = 0; k < listName.length; k++){
              var versioning = getVersion(sites[i], listName[k]);
              ep.writeNextRow([row.listName, row.webURL, row.result]);
        }
    }

            ep.saveAs('Versioning.xlsx');
    }
  • The answer I' provided one month ago works. I tried it myself and the Excel file contains all the data: sharepoint.stackexchange.com/a/207698/17341 – AymKdn Mar 10 '17 at 8:28
  • Hi AymKdn, I tested the code you provided to me again today, but it's not exporting the Excel file alone when it reads the code. In order for me to force it to extract the spreadsheet file with the populated info I have to call the function and hardcode what to extract. I do appreciate what you wrote, don't get me wrong, I'm new to SP Services and such SharePoint coding in general, so I'm not trying to say I'm right and you're wrong. Do please check the code I've provided now, the one above and see that it is exporting the Excel file alone the way I edited it, but it dosn't push the info in it – Benny Blanco Mar 10 '17 at 12:27
  • That's what I'm trying to achieve, after it reads the info in the SP pages, to extract the Excel on it's own and to have the web URL of the documents, the listName and if the document has version enabled, or disabled.I'm testing the code by triggering an aspx file loaded with the links to the scripts,as well as a div with an "outputDataDiv" and an ul with a "taskUL" ids to push the info inside so I can see all it loads, but the Excel file comes out without any info populated in it, just creates the column names. I know there's something wrong with my code, that's why I'm asking in here. – Benny Blanco Mar 10 '17 at 12:34
1

$().SPServices.SPGetCurrentSite() will be asynchronous in the modern browsers. So you cannot use it the way you want it.

Same thing for getVersion(). It's asyn, so you cannot use it in extractToExcel like that...

You really need to read on the web about asynchronous requests!

So I changed your code using the one I've already provided but adding the SPGetCurrentSite part:

// we create a function to find the Current Site
function SPGetCurrentSite() {
  var deferred = jQuery.Deferred()
  var msg = "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><WebUrlFromPageUrl xmlns='http://schemas.microsoft.com/sharepoint/soap/' ><pageUrl>"+window.location.href.replace(/&/g,"&amp;")+"</pageUrl></WebUrlFromPageUrl></soap:Body></soap:Envelope>";
  $.ajax({
    url: "/_vti_bin/Webs.asmx",
    type: "POST",
    data: msg,
    dataType: "xml",
    contentType: 'text/xml;charset="utf-8"',
    complete: function (xData) {
      deferred.resolve($(xData.responseXML).find("WebUrlFromPageUrlResult").text());
    }
  });
  return deferred;
}

var liHtml = [];
var fieldsStatic = [];
var fieldsDisplay = [];
var sites = [];
var listName = [];

// retrieve the current URL
SPGetCurrentSite().done(function(webAddress) {
  // we can now start the job because we have the webAddress
  doTheJob(webAddress);
});

// we don't want the code to be executed when the page is loaded, but only when we get the info we need
function doTheJob(webAddress) {
  $().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

      // for each websites
      $(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
  }
}

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
}

// 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': 'Versioning'
  });
  // 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) {
    if (row) ep.writeNextRow([row.listName, row.webURL, row.result]);
  });

  ep.saveAs('Versioning.xlsx');
};

And please read the comments in the code!

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.