-
-
Save mderazon/9655893 to your computer and use it in GitHub Desktop.
| /* | |
| * 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); | |
| } | |
| } |
@mderazon thanks a lot for sharing this.
Could you be having a snippet code that can convert an individual sheet to a downloadable excel(xlsx) file.
I have tried the following code but the issue is, its downloading all sheets instead of an individual sheet.
function makeXlsx() { var sheetId = "1x53K43fytf55k4D0WqWckKCpX_1w0098-a8HM"; var url = "https://docs.google.com/spreadsheets/d/" + sheetId + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken(); var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx"); folder.createFile(blob); }
Hi
I'm using this code to save a whole lot of bank account details to a CSV file, but some bank accounts start with a zero and when the account number is saved to the csv file, the leading zero is dropped. Please can someone suggest a edit that will solve this.
Thanks
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?
@EdusanSanta: There's a way, but it's not as straightforward as you might like:
Here are some resources if you'd like to learn more:
Here's how I would implement your download of all sheets:
File > Download > CSVurl for each sheet.https://docs.google.com/spreadsheets/d/[DOCUMENT_ID]/export?format=csv&id=[DOCUMENT_ID]&gid=[SHEET_ID]DOCUMENT_IDviaSpreadsheetApp.getActiveSpreadsheet().getId()SHEET_IDfor each sheet viaSheet.getSheetId()