Google-sheets – Duplicate and rename sheet in one action on Google Sheets

formulasgoogle sheets

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?

Best Answer

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});
}