4

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?

2

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});
}
  • Thanks! I was nervous that any workaround wouldn't properly duplicate the named ranges on the sheet but this worked perfectly! – Daniel Feb 16 '16 at 19:25

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.