Not deleting file from Google Drive using Google Script + Google SpreadSheet

1
votes

I have a script in my Google SpreadSheet that save my Google Sheet to a XLSX format on every edit i made. It is working fine, but its is creating a new file with the same name (many files with the same name, not overwriting, in the same folder).

I´d like to remove the old file and save the new version OR just overwrite the file that use the same name

My code is:

function getGoogleSpreadsheetAsExcel(){

  try {

    var ss = SpreadsheetApp.getActive();
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";
    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();
    blob.setName(ss.getName() + ".xlsx");

    // set the folder where I am working using the id
    var folder = DriveApp.getFolderById(“FOLDER_ID");

    var xname = “blablabla.xlsx"; //set the file name
    var existing = folder.getFilesByName(xname);
    if (existing.hasNext()) {
      Browser.msgBox(‘file exists, delete to save the new version after.', Browser.Buttons.OK);

    // 'DELETE THE OLD FILE FROM GOOGLE DRIVE' CODE GOES HERE      


      Browser.msgBox(‘file deleted.', Browser.Buttons.OK);
      var arquivo = folder.createFile(blob); //save the new version
      Browser.msgBox(‘new version was saved.', Browser.Buttons.OK);
    }

    else {
      Browser.msgBox(‘file does not exists, saving.', Browser.Buttons.OK);
      var arquivo = folder.createFile(blob); //save the file
      Browser.msgBox(‘file saved.', Browser.Buttons.OK); //file saved
    }


  } catch (f) {
    Logger.log(f.toString());
  }
}

Help me to delete or overwrite the old version from Google Drive

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes
  • When the file of blablabla.xlsx is existing in the specific folder of FOLDER_ID, you want to overwrite the file of blablabla.xlsx.
  • Or, when the file of blablabla.xlsx is existing in the specific folder of FOLDER_ID, you want to delete the existing file and create it as new file.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Before you use this modified script, please enable Drive API at Advanced Google services.

Pattern 1:

In this pattern, the existing file is overwritten by the blob.

Modified script:

When your script is modified please modify as follows.

if (existing.hasNext()) {
  Browser.msgBox(‘file exists, delete to save the new version after.', Browser.Buttons.OK);

// 'DELETE THE OLD FILE FROM GOOGLE DRIVE' CODE GOES HERE      


  Browser.msgBox(‘file deleted.', Browser.Buttons.OK);
  var arquivo = folder.createFile(blob); //save the new version
  Browser.msgBox(‘new version was saved.', Browser.Buttons.OK);
}

var arquivo; // Added
if (existing.hasNext()) {
  Browser.msgBox('file exists, delete to save the new version after.', Browser.Buttons.OK);
  Browser.msgBox('file deleted.', Browser.Buttons.OK);
  var id = Drive.Files.update({}, existing.next().getId(), blob).id; // Modified
  arquivo = DriveApp.getFileById(id); // Modified
  Browser.msgBox('new version was saved.', Browser.Buttons.OK);
}

Pattern 2:

In this pattern, the existing file is deleted and the blob is created as new file.

Modified script:

When your script is modified please modify as follows.

if (existing.hasNext()) {
  Browser.msgBox(‘file exists, delete to save the new version after.', Browser.Buttons.OK);

// 'DELETE THE OLD FILE FROM GOOGLE DRIVE' CODE GOES HERE      


  Browser.msgBox(‘file deleted.', Browser.Buttons.OK);
  var arquivo = folder.createFile(blob); //save the new version
  Browser.msgBox(‘new version was saved.', Browser.Buttons.OK);
}

var arquivo; // Added
if (existing.hasNext()) {
  Browser.msgBox('file exists, delete to save the new version after.', Browser.Buttons.OK);
  Browser.msgBox('file deleted.', Browser.Buttons.OK);
  Drive.Files.remove(existing.next().getId()); // Modified
  arquivo = folder.createFile(blob); // Modified
  Browser.msgBox('new version was saved.', Browser.Buttons.OK);
}

Note:

  • At above modified scripts, when the script is run, the existing file is overwritten by the blob and/or deleted. So please be careful this.
  • In your script, ‘ is used as the single quote. In this case, an error occurs. Please modify to '. Please be careful this.

References:

If I misunderstood your question and this was not the direction you want, I apologize.