Google Scripts Editor-Overwrite backup file

Question

I am working with Google Scripts Editor. The purpose of my script is to backup a spreadsheet from Google Sheets to my local as a csv. I would like each new backup to overwrite the previous backup file. Currently the script results in subsequent backup files as the same name but as a copy (Ex. filename (1).csv and filename (2).csv) Any help in overwriting would be appreciated.

  function myFunction() {// UPDATE THE FOLDER ID for e.g. "My Drive > Docs > Backups"
  var backupFolder = DriveApp.getFolderById("foldername");
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId();
  var file = Drive.Files.get(spreadsheetId);
  var url = file.exportLinks[MimeType.CSV];

  // UPDATE THE SHEET-NAME TO BE EXPORTED - e.g. sheetName = "Malawi Ops"
  // -- LEAVE IT BLANK TO EXPORT THE ENTIRE SPREADSHEET - i.e. sheetName = ""
  var sheetName = "";
  if (sheetName.length) {
    var sheet = spreadsheet.getSheetByName(sheetName);
    var sheetId = sheet.getSheetId();
    url += "&gid=" + sheetId;
  }

  var token = ScriptApp.getOAuthToken();
  var options = { headers: { Authorization: "Bearer " + token } };
  var response = UrlFetchApp.fetch(url, options);
  var doc = response.getBlob();
  var backupDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH-mm-ss");
  var backupName = spreadsheet.getName() + ".csv";
  var tempFile = DriveApp.createFile(doc).setName(backupName);
  tempFile.makeCopy(backupName, backupFolder);
  tempFile.setTrashed(true);

}

In your question, you say The purpose of my script is to backup a spreadsheet from Google Sheets to my local as a csv.. But in your script, the Google Spreadsheet is converted to an Excel file and it is put to backupFolder in your Google Drive. From this situation, I cannot understand about your goal. Can I ask you about the detail of it? — Tanaike
Code is edited to reflect exporting as csv. I am trying to backup to local as a csv. What I am unclear about is whether I can force an overwrite of the original file on my local via the Google Scripts Editor and what that code would look like. Currently each time I run the script, I end up with a copy of the original, instead of overwriting. — heather
Thank you for replying. Unfortunately, in the current stage, your goal cannot be achieved. Because the Google Scripts Editor cannot be used the files in the local PC. I deeply apologize for this. — Tanaike

Jacques-Guzel Heron Jacques-Guzel Heron · Accepted Answer · 2020-06-12T08:37:02

I understood that you are using your code to make CSV backups of your Sheets, and you want each new backup to override the old one. If I understood it well, you are very close from reaching your goal.

After copying the new backup, you only need to delete the old one. This isn't directly overwriting, but has the same result. Immediately after you created your new CSV, you can search for the old one and use File.isTrashed() on it. Please, don't hesitate to ask me any doubts.

Google Scripts Editor-Overwrite backup file

1 Answers