Append code to overwrite a PDF if a file of the same name already exists in the Google Drive folder

Question

I have this script to save my spreadsheet in the Google Drive Squads folder..

The name of the saved file is according to the value that is in cell H2 of the Gerais page, sometimes the name repeats and when saving, a new file is created instead of subscribing to the existing one, I would like to add in this code the option that if a file with the same name as this new one already exists, instead of having two files with the same name in the Google Drive Squads folder, the old one will disappear completely and only make the new file available

    //Create PDF
    SpreadsheetApp.flush();

    var theurl = 'https://docs.google.com/a/mydomain.org/spreadsheets/d/' + // Best to place the line break after '+'
      'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' +  // SHEET ID
        '/export?format=pdf' +
          '&size=0' +
            '&portrait=true' +
              '&fitw=true' + 
                '&top_margin=0' +            
                  '&bottom_margin=0' +         
                    '&left_margin=0' +        
                      '&right_margin=0' +     
                        '&sheetnames=false&printtitle=false' +
                          '&pagenum=false' +
                            '&gridlines=false' +
                              '&fzr=FALSE' +
                                '&gid=' +
                                  'XXXXXXXXXXXX'; //SHEET PAGE ID

    var token = ScriptApp.getOAuthToken();
    var docurl = UrlFetchApp.fetch(theurl, { headers: { 'Authorization': 'Bearer ' +  token } });
    var pdfBlob = docurl.getBlob();

    // Get filename from sheet "Gerais", cell "H2"
    var fileName = spreadsheet.getSheetByName("Gerais").getRange("H2").getValue();

    // Create file from blob and name it
    // The newFile is placed in the root folder by default
    var newFile = DriveApp.createFile(pdfBlob).setName(fileName);  

    // if folder exists use next 
    if (DriveApp.getFoldersByName("Squads").hasNext()){
      var folder = DriveApp.getFoldersByName("Squads").next();

      // if folder does not exist
    } else {
      var folder = DriveApp.createFolder("Squads");// new folder created in the root folder by default
    }

    folder.addFile(newFile); // add new file to folder
    DriveApp.removeFile(newFile); // remove file from root folder

I tried to create an interaction between IF and ELSE too to make the file name match the same as I did for the folder name but I was unsuccessful in trying

Oleg Valter Oleg Valter · Accepted Answer · 2019-09-28T18:59:53

Problem

Overwriting existing File in a Folder by its name (presumably unique).

Solution

How about checking if a file with the name written into fileName variable exists and if so, removing it before adding the newly created one? Modification will look something like this (your script has be authorized with one the scopes required for the API):

//prepare new file and Squads folder;

var existing = folder.getFilesByName(fileName); //returns file iterator;
var hasFile = existing.hasNext(); //check if iterator isn't empty;
if(hasFile) {
  var duplicate = existing.next(); //access file;

  //delete file;
  var durl = 'https://www.googleapis.com/drive/v3/files/'+duplicate.getId();
  var dres = UrlFetchApp.fetch(durl,{
    method: 'delete',
    muteHttpExceptions: true,
    headers: {'Authorization': 'Bearer '+token}
  });
  if(dres.getResponseCode()>=400) {
    //handle errors;
  }
}

//continue: add file, remove from root, etc;

Notes

The way you define query parameters is the common case of a pyramid of doom. Besides, each parameter is hardcoded, which makes the export inflexible. To avoid this, use objects to configure them (as query is nothing more than a set of key-value pairs):

var params = {
  format: 'pdf',
  size: 0,
  portrait: true,
  fitw: true,
  top_margin: 0,        
  bottom_margin: 0,        
  left_margin: 0,    
  right_margin: 0,   
  sheetnames: false,
  printtitle: false,
  pagenum: false,
  gridlines: false,
  fzr: 'FALSE',
  gid: 'XXXXXXXXXXXX'
};

//append params to the url;
var theurl = 'base?'+Object.keys(params).map(function(key){
  return key+'='+params[key];
}).join('&');

Updates

Corrected UrlFetchApp.fetch() call as method should be a parameters object property and not an argument.

Reference

Drive API delete reference;

Append code to overwrite a PDF if a file of the same name already exists in the Google Drive folder

1 Answers