Last active
April 2, 2024 22:25
Revisions
-
mderazon revised this gist
Apr 21, 2015 . 1 changed file with 1 addition and 2 deletions.There are no files selected for viewing
This file contains hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -1,7 +1,6 @@ /* * 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 */ @@ -15,7 +14,7 @@ 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 -
mderazon revised this gist
Apr 5, 2014 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -25,7 +25,7 @@ function saveAsCSV() { // 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) { -
mderazon revised this gist
Mar 20, 2014 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -1,7 +1,7 @@ /* * 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 */ -
mderazon created this gist
Mar 20, 2014 .There are no files selected for viewing
This file contains hidden or 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 charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,65 @@ /* * 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 section 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 waitig 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); } }