CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-drive-api
Nick Chand
Nick Chand

Reputation: 83

Overwrite existing file in a specific Google Drive folder

I need the var file = dir.createFile(blob).setContent('Stores_Month.pdf'); to every time, overwrite the existing Stores_Month.pdf file in the specified Google Drive folder.

function runCBUEBUquarterly() {

  var file = DriveApp.getFilesByName("Data_Stores_Quarter.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActive().getSheetByName('Data_Month');
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);


 /* Highlight all sort range and sort by AS - Okanagan */ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActive().getSheetByName('Stores_Month');
  var range = sheet.getRange("A5:AS10");

 // Sorts by the values in column (AS)
  range.sort({column: 45, ascending: false});


   /* Highlight all sort range and sort by AS - LM & Whistler */ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActive().getSheetByName('Stores_Month');
  var range = sheet.getRange("A13:AS24");

 // Sorts by the values in column (AS)
  range.sort({column: 45, ascending: false});


 /* Save as PDF to Google Drive */ 
  SpreadsheetApp.flush();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActive().getSheetByName('Stores_Month');

  var url = ss.getUrl();

  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/,'');

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
  //below parameters are optional...
  '&size=letter' + //paper size
  '&portrait=false' + //orientation, false for landscape
  '&fitw=false' + //fit to width, false for actual size
  '&pagenumbers=true' + //fit to width, false for actual size    
  '&sheetnames=false&printtitle=false' + //hide optional headers and footers
  '&gridlines=false' + //hide gridlines
  '&fzr=true' + //do not repeat row headers (frozen rows) on each page
  '&gid=' + sheet.getSheetId(); //the sheet's Id

  var token = ScriptApp.getOAuthToken();

  var response = UrlFetchApp.fetch(url + url_ext, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });

  var blob = response.getBlob().setName(sheet.getName() + '.pdf');

  //from here you should be able to use and manipulate the blob to send and email or create a file per usual.
  //In this example, I save the pdf to drive



  var dir = DriveApp.getFoldersByName('CBU & EBU - Quarterly Tracker').next();
  var file = dir.createFile(blob).setContent('Stores_Month.pdf');

}

Upvotes: 2

Views: 1636

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

You'll probably need to use something like the following code. I haven't tested it, but try it out, and see what happens:

var dir, file, newContent;

//dir = DriveApp.getFoldersByName('CBU & EBU - Quarterly Tracker').next();
dir = DriveApp.getFolderById('put the folder ID here');//Get folder by ID

file = dir.getFilesByName('Stores_Month.pdf').next();

if (file) {//There is an existing file by that name
  newContent = blob.getDataAsString();
  Logger.log('blob: ' + blob);
  Logger.log('blob.getDataAsString(): ' + blob.getDataAsString());

  file.setContent(newContent);//The new content must be a string
} else {//no file by that name was found
  file = dir.createFile(blob).setName('Stores_Month.pdf');
}

Upvotes: 1

Related Questions