How do I to delete/trash/remove a csv file if exists

Question

I'm trying to create an export to csv apps script but it will not delete existing csv files sitting in the same folder. I would like to either trash them or overwrite them.

Here's the code I've already tried

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());
  // 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);

    var file = DriveApp.getFileById(ss.getId());
    var folder = file.getParents();
    folder = folder.next();

    // loop through files and delete ones with existing name
    var existingfiles  = folder.getFiles()
    for (var j = 0 ; j<existingfiles.length;j++){
      var existingfile = existingfiles[j].next()
      if (existingfile.getName()!=ss.getName()){
        //to delete
        //existingfile.setTrashed(true);
        folder.removeFile(existingfile);
      }
    }

    //create new file
    folder.createFile(fileName, csvFile);

  }      
}

I'd expect all files that don't share the same name as the spreadsheet in that folder to get removed, then a csv for each tab to get created. Instead, I get duplicates of each csv file.

score 1 · Answer 1 · answered Aug 09 '19 at 18:55

Thanks a lot for you help. The while loop helped a bunch. Here's what worked for me in the end.

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var file = DriveApp.getFileById(ss.getId());
  var folder = file.getParents();
  folder = folder.next();

  // loop through files and delete ones with existing name
    var xfiles=folder.getFiles();
    while(xfiles.hasNext()) {
      var fi=xfiles.next();
      if(fi.getMimeType()!=MimeType.GOOGLE_SHEETS){fi.setTrashed(true);}
    }

  // create new csv files
  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 new file
    folder.createFile(fileName, csvFile);

  }      
};

Cooper · Answer 2 · 2019-08-09T18:32:43.747

Try this:

function trashOthers() {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  var fldr=DriveApp.getFileById(ss.getId()).getParents();
  var n=0;
  while(fldr.hasNext()) {
     var folder=fldr.next();
     n++;
  }
  if(n>1){throw('More than one Folder');}
  for (var i=0;i<shts.length;i++) {
    var sh=shts[i];
    var fileName=sh.getName() + ".csv";
    var csvFile=convertRangeToCsvFile_(fileName, sh);
    var xfiles=folder.getFiles();
    while(xfiles.hasNext()) {
      xfiles.next().setTrashed(true);
    }
    folder.createFile(fileName, csvFile);
  }      
}

Perhaps this is better:

function deleteOthers() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var shts=ss.getSheets();
  var fldr=DriveApp.getFileById(ss.getId()).getParents();
  var n=0;
  while(fldr.hasNext()) {
     var folder=fldr.next();
     n++;
  }
  if(n>1){throw('More than one Folder');}
  for (var i=0;i<shts.length;i++) {
    var sh=shts[i];
    var fileName=sh.getName() + ".csv";
    var csvFile=convertRangeToCsvFile_(fileName, sh);
    var xfiles=folder.getFiles();
    while(xfiles.hasNext()) {
      var fi=xfiles.next();
      if(fi.getMimeType()!=MimeType.GOOGLE_SHEETS){fi.setTrashed(true);}
    }
    folder.createFile(fileName, csvFile);
  }      
}

Close, thanks! My Sheets file is in the same directory and isn't named the same as any of the individual sheets (eg. Sheet name is "Export Data" and sheets are S1, S2, S3). This script accidentally moved "Export Data" it to the trash as well. Do I just need to add something like ``` while(xfiles.hasNext()) { if ss.getname() != xfiles.next().getname() xfiles.next().setTrashed(true); } ``` — Kevin D, Aug 09 '19 at 18:24
Hey no worries. Thanks again. Getting closer. Now I'm only left with the last csv as where there are currently 3 in my Sheets workbook. — Kevin D, Aug 09 '19 at 18:39

score 0 · Answer 3 · answered Nov 11 '22 at 16:44

0

I used deleteOthers() and trashothers() both are deleting the google form which writes data into csv

answered Nov 11 '22 at 16:44

user3722434

3
3

How do I to delete/trash/remove a csv file if exists

3 Answers3