Problem script for Export a spreadsheet in CSV

Question

I have a problem with a script to export sheets to CSV:

I would like my folder to be created in the parent folder of the Sheets on google drive and not in the root of my google drive.

I also can't seem to modify the script to overwrite old versions of the folder instead of creating new ones.

Can you help me

Best regards

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'); 
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('Le fichier a été nommé ' + 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); 
  } 
};

Cédric GUEZENNEC Cédric GUEZENNEC · Accepted Answer · 2020-02-03T09:45:51

Thank you very much, that's exactly it! it works perfectly! Thank you.

I will have one last complaint in my data import script, I will want to import a CSV file into a "Backup" sheet but I will want to keep the previous data of the type "add to active sheet"

My script

function Backup() {
   var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A4').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Backup'), true);
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('A1').activate();
  var file = DriveApp.getFilesByName("Report.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString(), ",");
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
};

Problem script for Export a spreadsheet in CSV

1 Answers