Created
April 16, 2017 03:34
-
-
Save simongcc/ee5fe824432e3e71bf28c3598ed0254e to your computer and use it in GitHub Desktop.
Google script to export all csv in one 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 | |
* Note: This script uses the DocsList.createFile() method, which is only available for Google Apps accounts. | |
* 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 = DocsList.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