Duplicate and rename sheet in one action on Google Sheets

Question

I have a script which references the name of a sheet in Google Spreadsheets. When I duplicate a sheet with a cell that uses that function, the function is called right away and returns something like Copy of Sheet1. When I rename Copy of Sheet1 to Sheet2, the function is not called again because the result is cached.

If I pass a parameter like Today() into the function and simply ignore it, the function will be recalculated every day but that is suboptimal for a couple of reasons:

• I don't need to recalculate that formula every day. I will not be changing the name of the sheets, so I just need it to calculate once.
• I would rather not wait an entire day for the formula to be calculated after duplicating a sheet.

What I would like to do is duplicate a sheet and give it a name before the formulas in the sheet are calculated. Is this possible? If so, how can it be done? If not, do you have any other suggestions?

Answer 1

This can be done with a script, using insertSheet method which takes the new sheet name and the template after which it's to be created. The first function adds a custom menu item when the spreadsheet is opened, the second performs duplication when that menu item is invoked.

As far as I can tell, the contents are copied over in the same way as they are with "Duplicate" function.

function onOpen() {
  var menu = [{name: "Duplicate and name", functionName: "dupName"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function dupName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var name = Browser.inputBox('Enter new sheet name');
  ss.insertSheet(name, {template: sheet});
}