Skip to content

Instantly share code, notes, and snippets.

@mderazon
Last active April 2, 2024 22:25
Show Gist options
  • Save mderazon/9655893 to your computer and use it in GitHub Desktop.
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
/*
* 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);
}
}
@isaacmunuhe
Copy link

@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); }

@arniearm
Copy link

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

@missinglina
Copy link

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment