Google Sheets script, copyto and replace existing sheet

Question

I've been searching for the past few weeks and haven't been able to figure out this script. What I'm trying to do is figure out a way for me to duplicate a pictorial calendar on a master Google Sheets file to another Google Sheets file that I manage. Every time there's an update on the master calendar, I'd love for it to automatically update in the other Google Sheets file that I manage. Right now I have to copy paste everything, reformat, and reimport images. I did find a script that somewhat works, but it has some issues that cause it to be just as much work.

The script is a copyTo script, it allows me to copy a sheet titled "Pictorial Calendar" from the master Google Sheets file (called "Master Calendar") to a different Google Sheets file (called "My Calendar"). I even set up a trigger that allows the script to run whenever I make an edit.

However, it keeps creating new sheets titled "Copy of Pictorial Calendar 1" "Copy of Pictorial Calendar 2" etc.) and I can't figure out how to make the script replace the existing sheet instead of creating new sheets.

I also would love to be able to change the duplicated sheet to just be called "Pictorial Calendar" in the "My Calendar" file. The script I'm using is:

function copyMasterCalendar() {
 var source = SpreadsheetApp.getActiveSpreadsheet();

 var sheet = source.getSheets()[2];

 var destination = SpreadsheetApp.openById("1exiUWVypFpYeHMkXHO3sMUTGupiC2gQjZIF0Ss44-pU");

 sheet.copyTo(destination);
}

Any and all help would be much appreciated! Thank you!

mongoose36 mongoose36 · Accepted Answer · 2016-06-28T02:11:37

If I understood you correctly try this...

function copyMasterCalendar() {
  var source = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = source.getSheetByName('Pictorial Calendar'),
      destination = SpreadsheetApp.openById("1exiUWVypFpYeHMkXHO3sMUTGupiC2gQjZIF0Ss44-pU"),
      destSheet = destination.getSheetByName('Pictorial Calendar'),
      copydSheet = sheet.copyTo(destination);
  copydSheet.getDataRange().copyTo(destSheet.getDataRange());
  destination.deleteSheet(copydSheet);
}

Google Sheets script, copyto and replace existing sheet

1 Answers