-
-
Save padraic00/e7c11a66f6f48915b7cca781799d4c98 to your computer and use it in GitHub Desktop.
Google apps script to export to individual csv files all sheets in an open spreadsheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* 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); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment