Google Sheets - Script to “Replace” instead of “Create”, when file name is the same

Question

EDIT to try and make this easier to understand:

Here is what I did:

I created a Google Spreadsheet. I created a SCRIPT that saves it to a Google Drive Folder using a File Name based on the date of service and Customer Name.

This is the Script I currently have:

// This creates a custom Menu Function so that I can save the file. 

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Choice Menu')
  .addItem('Submit Form','saveAsSpreadsheet')
       .addToUi();  }      

// Saves Spreadsheet in Google Drive Folder

function saveAsSpreadsheet() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var destFolder = DriveApp.getFolderById("0B8xnkPYxGFbUMktOWm14TVA3Yjg");
  DriveApp.getFileById(sheet.getId()).makeCopy(getFilename(), destFolder);
   } 

//This Function uses a cell "G4" (Which is Date and Customer Name) to create a file name. This file name will be then used for the above Script.

function getFilename() {

    var spreadsheet = SpreadsheetApp.getActive();

    var sheet = spreadsheet.getSheetByName('Manifest');
       var cell = sheet.getRange('G4');
    var filename = cell.getValue();

    return filename;}

So My code works GREAT except for one Problem.

Here is my Problem:

When I Save the Spreadsheet a 2nd time using the above Script, it saves a new file. I want to save it as a new file unless the Filename is the same. IF the file name is the same, I want to delete the original file, and then save the new file.

What I tried: From what I understand reading the comments below, I need to run a code that "1. will need to run a query to see if any files exist with your chosen name, and then act accordingly. 2. If there are no files with that name, then go ahead and create one (using the function I listed above). If there is a file with the same name, 3. delete the original file and create a new one with the same name.

I tried for several days coming trying different options, and none of it worked, so I am looking to start over.

Appreciate any coding solutions or direction on where to go!

Can I ask you about it replace (not create a copy)? If there is a file with the duplicated filename in the folder, how do you want to do? 1. You want to delete the existing file and create new file with the same filename. In this case, the file ID is different from the original file. 2. You want to replace the file without changing the file ID. In the current stage, this cannot be achieved yet. — Tanaike
If the spreadsheet is simple enough, you could copy the sheets from the new file to the old file and delete the old sheets. This will not work if you refer to named ranges on other sheets or if you have formulas that refer to other sheets or anything more complex (at least not without significant coding). — CalamitousCode
@s1c0j1 , it looks like my sheet is to complex for that. The file consists of multiple sheets. — Ben
@Tanaike , I would like to do your option 1. I do not care that the fileID is different. — Ben
@Ben Thank you for replying. I noticed that an answer was posted. I think that it will resolve your issue. — Tanaike

Tanaike Tanaike · Accepted Answer · 2019-01-23T01:12:12

Your script is the container-bound script of Spreadsheet.
When the Spreadsheet with the searched filename is existing in a specific folder, you want to replace it to the active Spreadsheet.
- In the current stage, a file cannot be replaced while the file ID is not changed. So in this modified script, the active Spreadsheet is copied and the searched file is deleted.
When the searched file is not existing in a specific folder, you want to copy the active Spreadsheet to the folder.
You want to use the value returned from getFilename() as the filename.

If my understanding is correct, how about this modification? I think that there are several solutions for your situation. So please think of this as just one of them.

Modified script:

Please modify saveAsSpreadsheet() as follows.

function saveAsSpreadsheet() {
  var folderId = "0B8xnkPYxGFbUMktOWm14TVA3Yjg";
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByName(getFilename());
  if (files.hasNext()) {
    files.next().setTrashed(true);
  }
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  DriveApp.getFileById(sheet.getId()).makeCopy(getFilename(), folder);
}

Note:

In order to search the file, I used getFilesByName() because I thought the filename seems constant.
setTrashed() for removing the file is used for this script. In this case, the file is put in the trash box. If you want to directly delete the file, you can achieve it using Drive API. At that time, please tell me.

References:

Edit:

If you want to directly delete the file, please modify the script as follows.

From:

files.next().setTrashed(true);

To:

var params = {method: "delete", headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}};
UrlFetchApp.fetch("https://www.googleapis.com/drive/v3/files/" + files.next().getId(), params);

I used the method using UrlFetchApp because I thought that in your situation, Drive API has already been enabled. In this case, you can use the delete method of Drive API by only replacing the script.

Reference:

delete method of Drive API