padraic00/export-to-csv.gs

## export-to-csv.gs
/*
 * script to export data in all sheets in the current spreadsheet as individual csv files
 * files will be named according to the name of the sheet
 * author: Michael Derazon
*/

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var gui = SpreadsheetApp.getUi();

  // detect outputs directory
  var folders = DriveApp.getFolders();
  var mktmp = true;
  while (folders.hasNext()) {
    var folder = folders.next();
    if (folder.getName() === 'toCsvOutput') {
      mktmp=false;
      break;
    }
  }
  if (mktmp) {
    DriveApp.createFolder('toCsvOutput');
  }

  // set filename from user input
  fileName = sheet.getName() + ".csv";
  var response = gui.prompt('Save as...', 'Leave blank for default: '+fileName,gui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() === gui.Button.CANCEL) {
    return;
  }
  var restxt = response.getResponseText();
  if (restxt !== '') {
    fileName = restxt;
  }

  var csvFile = convertRangeToCsvFile_(sheet);
  var file = DriveApp.getFoldersByName('toCsvOutput').next().createFile(fileName, csvFile);
  var downloadURL = file.getDownloadUrl().slice(0, -8);

  var html = HtmlService.createHtmlOutput('<html><script>'
                                          +'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
                                          +'var a = document.createElement("a"); a.href="'+downloadURL+'"; a.target="_blank";'
                                          +'if(document.createEvent){'
                                          +'  var event=document.createEvent("MouseEvents");'
                                          +'  if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'
                                          +'  event.initEvent("click",true,true); a.dispatchEvent(event);'
                                          +'}else{ a.click() }'
                                          +'close();'
                                          +'</script>'
                                          // Offer URL as clickable link in case above code fails.
                                          +'<body style="word-break:break-word;font-family:sans-serif;">Failed to open automatically. <a href="'+downloadURL+'" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>'
                                          +'<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
                                          +'</html>').setWidth( 90 ).setHeight( 1 );

  gui.showModalDialog( html, "Opening..." );
}

function convertRangeToCsvFile_(csvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;

    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}
	/*
	* script to export data in all sheets in the current spreadsheet as individual csv files
	* files will be named according to the name of the sheet
	* author: Michael Derazon
	*/

	function saveAsCSV() {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = ss.getActiveSheet();
	var gui = SpreadsheetApp.getUi();

	// detect outputs directory
	var folders = DriveApp.getFolders();
	var mktmp = true;
	while (folders.hasNext()) {
	var folder = folders.next();
	if (folder.getName() === 'toCsvOutput') {
	mktmp=false;
	break;
	}
	}
	if (mktmp) {
	DriveApp.createFolder('toCsvOutput');
	}

	// set filename from user input
	fileName = sheet.getName() + ".csv";
	var response = gui.prompt('Save as...', 'Leave blank for default: '+fileName,gui.ButtonSet.OK_CANCEL);
	if (response.getSelectedButton() === gui.Button.CANCEL) {
	return;
	}
	var restxt = response.getResponseText();
	if (restxt !== '') {
	fileName = restxt;
	}

	var csvFile = convertRangeToCsvFile_(sheet);
	var file = DriveApp.getFoldersByName('toCsvOutput').next().createFile(fileName, csvFile);
	var downloadURL = file.getDownloadUrl().slice(0, -8);

	var html = HtmlService.createHtmlOutput('<html><script>'
	+'window.close = function(){window.setTimeout(function(){google.script.host.close()},9)};'
	+'var a = document.createElement("a"); a.href="'+downloadURL+'"; a.target="_blank";'
	+'if(document.createEvent){'
	+' var event=document.createEvent("MouseEvents");'
	+' if(navigator.userAgent.toLowerCase().indexOf("firefox")>-1){window.document.body.append(a)}'
	+' event.initEvent("click",true,true); a.dispatchEvent(event);'
	+'}else{ a.click() }'
	+'close();'
	+'</script>'
	// Offer URL as clickable link in case above code fails.
	+'<body style="word-break:break-word;font-family:sans-serif;">Failed to open automatically. <a href="'+downloadURL+'" target="_blank" onclick="window.close()">Click here to proceed</a>.</body>'
	+'<script>google.script.host.setHeight(40);google.script.host.setWidth(410)</script>'
	+'</html>').setWidth( 90 ).setHeight( 1 );

	gui.showModalDialog( html, "Opening..." );
	}

	function convertRangeToCsvFile_(csvFileName, sheet) {
	// get available data range in the spreadsheet
	var activeRange = sheet.getDataRange();
	try {
	var data = activeRange.getValues();
	var csvFile = undefined;

	// loop through the data in the range and build a string with the csv data
	if (data.length > 1) {
	var csv = "";
	for (var row = 0; row < data.length; row++) {
	for (var col = 0; col < data[row].length; col++) {
	if (data[row][col].toString().indexOf(",") != -1) {
	data[row][col] = "\"" + data[row][col] + "\"";
	}
	}

	// join each row's columns
	// add a carriage return to end of each row, except for the last one
	if (row < data.length-1) {
	csv += data[row].join(",") + "\r\n";
	}
	else {
	csv += data[row];
	}
	}
	csvFile = csv;
	}
	return csvFile;
	}
	catch(err) {
	Logger.log(err);
	Browser.msgBox(err);
	}
	}