Google apps script to export to individual csv files all sheets in an open spreadsheet

export-to-csv.gs

/*
 * 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);
  }
}

@EdusanSanta: There's a way, but it's not as straightforward as you might like:

• Browsers require the user to perform an action (i.e. click) to initiate a download - you can't "push" a download to the user. App Scripts don't have any special way to achieve this either.
• If you want to download multiple files (csv files cannot contain multiple sheets/tabs), this means one click per file.
• Downloads are usually done through the use of download links (MDN doc here - see "download" attribute), using either data urls, object urls, or traditional links pointing to a file on a server.
• Data urls can have size limitations in some browsers, so use with caution. The data also needs to be encoded - either url encoded, or base64 encoded.
• In general, object urls are preferred - but I don't think the API is available in app scripts.

Here are some resources if you'd like to learn more:

• Create a Downloadable Link using HTML5 Download Attribute
• Programmatic file downloads in the browser
• Using HTML5/JavaScript to generate and save a file

Here's how I would implement your download of all sheets:

• Use app scripts to build a custom popup/dialog with a downloadable link for each sheet.
• I would hijack the functionality already offered by Google Docs, and build links that point to the File > Download > CSV url for each sheet.
• Here's what the urls looks like: https://docs.google.com/spreadsheets/d/[DOCUMENT_ID]/export?format=csv&id=[DOCUMENT_ID]&gid=[SHEET_ID]
• You can get the DOCUMENT_ID via SpreadsheetApp.getActiveSpreadsheet().getId()
• You can get the SHEET_ID for each sheet via Sheet.getSheetId()

@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?