Google Apps: Duplicate template sheet and delete o

Question

Greetings stackoverflow community,

I am currently trying to build a dashboard using Google Spreadsheets for my team for their daily tasks on a weekly basis. By the end of the week the team members should "reset" the dashboard sheet, but I am having trouble with the following script. The script should duplicate the "TEMPLATE" sheet, delete the old dashboard and rename the new copy to "Dashboard":

function resetDashboard() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('TEMPLATE');
  sheet.copyTo(ss).setName('New Dashboard');
  ss.setActiveSheet(ss.getSheetByName('Dashboard'));
  ss.deleteActiveSheet();
  ss.setActiveSheet(ss.getSheetByName('New Dashboard'));
  ss.renameActiveSheet('Dashboard');
}

Note: The "Dashboard" sheet is the only visible sheet in the spreadsheet.

So far this script is working fine. When I reload the spreadsheet it is still working.

My problem is, that when I close the spreadsheet and open it again from the GoogleDocs overview the script is ONLY duplicating the "Template" sheet, but the rest of the function is not executed anymore.

Is this a bug? Is there a "cleaner" version / script that I could use?

Best regards, Rüdiger

Answer 1

I think the issue is that "momentarily" you're having two sheets with the same name. A SpreadsheetApp.flush() before renaming might to the job. Try this:

function resetDashboard() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.getSheetByName('TEMPLATE').copyTo(ss);
  ss.setActiveSheet(ss.getSheetByName('Master'));
  ss.deleteActiveSheet();
  SpreadsheetApp.flush(); //this guarantees that the old sheet is deleted before we proceed
  newSheet.setName('Master');
  ss.setActiveSheet(newSheet);
}

Answer 2

thank you for the advice with the flush() function; although it did not resolve the issue I found out the real reason why both our scripts were not working (mine actually did as well, just just did not notice).

It is an already reported issue with the deleteActiveSheet() function:

Issue 1298: deleteActiveSheet() and copyTo() require .sleep(2000) to work reliably

The only workaround at the moment seems to be the Utilities.sleep() function; an example can be found here.

Thank you for your help, which led me to this. :)

Cheers, Rüdiger