none
SharePoint Exporting To Excel Issue RRS feed

  • Question

  •  managed to look if versioning of files in SharePoint lists is enabled, now trying to export the information into an Excel spreadsheet, where I need the list name, url and if versioning is enabled columns and information to be populated in it. It is working, but I don't need it hardcoded, I need it to be able to look into all sites of the platform, all lists, all documents,get the files with enabled versioning and populate it without the need to enter the listname, url and versioning when calling the function, I need it to return that info alone, insert it into the Excel file and allow the Excel file to be downloaded, saved onto your computer. Do please have a look at the code I've written and let me know what I'm not doing right, as it doesn't work like this, thank you:

    var webAddress = $().SPServices.SPGetCurrentSite();
        var liHtml = [];
        var fieldsStatic = [];
        var fieldsDisplay = [];
        var sites = [];
        $(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(sites) {
        var ep = new ExcelPlus();
        var cellLetters = ['A', 'B', 'C'];
        ep.createFile('Versioning');
        for (var i = 0; i < sites.length; i++){
            ep.write({
                'cell': 'A1',
                'content': 'List Name'
            });
            ep.write({
                'cell': 'B1',
                'content': 'URL'
            });
            ep.write({
                'cell': 'C1',
                'content': 'Versioning'
            });
    
            sites.forEach(function(row) {
                ep.writeNextRow([row.listName, row.webURL, row.result]);
            });
    
            ep.saveAs('Versioning.xlsx');
        }
        return extractToExcel();
        }

    Tuesday, March 7, 2017 12:03 PM

All replies

  • Hi,

    $().SPServices.SPGetCurrentSite() based on site URL so you need run it under specific site collection.

    $.fn.SPServices.SPGetCurrentSite = function() {
    		// Do we already know the current site?
    		if(thisSite.length > 0) {
    			return thisSite;
    		}
    		
    		var msg = SOAPEnvelope.header +
    				"<WebUrlFromPageUrl xmlns='http://schemas.microsoft.com/sharepoint/soap/' ><pageUrl>" +
    				((location.href.indexOf("?") > 0) ? location.href.substr(0, location.href.indexOf("?")) : location.href) +
    				"</pageUrl></WebUrlFromPageUrl>" +
    				SOAPEnvelope.footer;
    		$.ajax({
    			async: false, // Need this to be synchronous so we're assured of a valid value
    			url: "/_vti_bin/Webs.asmx",
    			type: "POST",
    			data: msg,
    			dataType: "xml",
    			contentType: "text/xml;charset=\"utf-8\"",
    			complete: function (xData, Status) {
    				thisSite = $(xData.responseXML).find("WebUrlFromPageUrlResult").text();
    			}
    		});
    		return thisSite; // Return the URL
    	}; // End $.fn.SPServices.SPGetCurrentSite

    If you want to retrieve whole farm, you could use PowerShell, for example:

    foreach ($site in get-spsite) { 	
    		foreach ($web in $site.AllWebs) { 
    		Get-SPWeb $web.Url |
    		   Select -ExpandProperty Lists |
    		   Where { $_.GetType().Name -eq "SPDocumentLibrary" -AND $_.EnableVersioning -eq $true } |
    		   Select Title
    		}	
    } 

    Here is the link for your reference.

    https://davidfrette.wordpress.com/2010/03/12/sharepoint-listing-of-all-lists-in-a-site-collection-using-powershell/

    http://sharepointpromag.com/sharepoint/windows-powershell-scripts-sharepoint-info-files-pagesweb-parts

    At last, you could export the data to csv file, here is one thread for your reference.

    http://sharepoint.stackexchange.com/questions/103771/how-to-export-powershell-output-to-excel

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, March 8, 2017 6:27 AM
  • Hi Lee and thank you for that. I managed to get it automated, but now it doesn't push the info into the Excel file, just creates it with the columns without populating any info in it. Here's my code, could you have a look and let me know what's wrong with it and why it's not populating the info in the spreadsheet file, thank you:

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

    Thursday, March 9, 2017 10:13 AM
  • Hi,

    I tried to debug your code and found the sites object is empty, try to check the object is set correctly.

    You could debug JavaScript logic by developer tool( I’m using IE currently, so just press F12), you could check below link for details about this tool.

    https://msdn.microsoft.com/en-us/library/gg589507(v=vs.85).aspx

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, March 10, 2017 8:57 AM