Last active
April 2, 2024 22:25
-
-
Save mderazon/9655893 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 onOpen() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var csvMenuEntries = [{name: "export as csv files", functionName: "saveAsCSV"}]; | |
ss.addMenu("csv", csvMenuEntries); | |
}; | |
function saveAsCSV() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheets = ss.getSheets(); | |
// create a folder from the name of the spreadsheet | |
var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime()); | |
for (var i = 0 ; i < sheets.length ; i++) { | |
var sheet = sheets[i]; | |
// append ".csv" extension to the sheet name | |
fileName = sheet.getName() + ".csv"; | |
// convert all available sheet data to csv format | |
var csvFile = convertRangeToCsvFile_(fileName, sheet); | |
// create a file in the Docs List with the given name and the csv data | |
folder.createFile(fileName, csvFile); | |
} | |
Browser.msgBox('Files are waiting in a folder named ' + folder.getName()); | |
} | |
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
Hello - this is working but when I open the file in a text editor, there are many blank rows with:
"","","","","","","","","","",""
Is there a way to get it to stop at the end of the last row with data instead?