Google apps script to export all sheets as csv files

export-sheets-as-csv.gs

/*
 * script to export data of all sheets as csv files
 * files are stored in a Google Drive folder
 * files named according to the name of the sheets
 * original author: Michael Derazon (https://gist.github.com/mderazon/9655893)
*/

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{name: "Save as CSV", functionName: "saveAsCSV"}];
  ss.addMenu("CSV", csvMenuEntries);
};

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // create a folder from the name of the spreadsheet
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());

  let sheets = ss.getSheets();
  for(let i = 0; i < sheets.length; i++){
    let 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);

    if(csvFile){
      // create a file in the Docs List with the given name and the csv data
      var file = folder.createFile(fileName, csvFile);
    }else{
      Logger.log("skipped empty sheet: " + sheet.getName());
    }
    
  }
  
  //display folder link
  showurl(folder.getUrl());
}

function showurl(downloadURL) {
  //Change what the download button says here
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '" target="_blank">Click here to access folder</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Folder with CSV files is ready!');
}

function convertRangeToCsvFile_(csvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getDisplayValues();
    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++) {
          let entryData = data[row][col];
          
          // escape quotes with extra quotes
          entryData = entryData.replace(/\"/g, "\"\"")

          if (entryData.toString().match("[\,\n\r]")) {
            entryData = "\"" + entryData + "\"";
          }

          data[row][col] = entryData;
          console.log(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);
  }
}