Google Sheets getRangeByName isn't working after duplicating sheet

Question

I use the following function to duplicate a sheet, and it works great.

function duplicateDataSheet() {
  const date = getTodayDateAsString();  
  const newDataName = `${date}_data`;
  const oldDataName = 'data';
  Logger.log(`newDataName = ${newDataName}`);
  const spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName(oldDataName), true);
  spreadsheet.duplicateActiveSheet();
  const newDataSheet = spreadsheet.getActiveSheet();  
  newDataSheet.setName(newDataName);
};

The sheet named 'data' has a Named Range called 'csv_paste_area'.

After duplicating the sheet, I then call a custom function of mine:

clearRangeByName('csv_paste_area');

But it ends up clearing the range of the most recently duplicated version of the 'data' sheet rather than the true 'csv_paste_area' Named Range, which exists on the original 'data' sheet!

This has been driving me crazy!

It feels like a bug in Google Sheets. I wonder if there is a work-around.

Answer 1

I think I figured out a work-around.

I updated my clearRangeByName function to accept an optional sheetName argument:

function clearRangeByName(rangeName, sheetName = null) {
  Logger.log(`clearRangeByName: ${rangeName}`);
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const originalActiveSheetName = spreadsheet.getActiveSheet().getSheetName();
  if (sheetName && sheetName !== originalActiveSheetName) { // because of this weirdness https://webapps.stackexchange.com/a/89963/27487
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(sheetName));
  }
  const range = spreadsheet.getRangeByName(rangeName);
  clearData(range); // custom function
  if (sheetName && sheetName !== originalActiveSheetName) { // because of this weirdness https://webapps.stackexchange.com/a/89963/27487
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(originalActiveSheetName));
  }
}

Then I call it like clearRangeByName('csv_paste_area', 'data');

Thanks to https://webapps.stackexchange.com/a/89963/27487 for the hint.

Update

Actually, I found a bug reported in 2013 (which linked to https://stackoverflow.com/a/15823668/470749, by the way) where Google said they would clarify documentation at https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getRangeByName(String), which now says:

getRangeByName(name)
Returns a named range, or null if no range with the given name is found. If multiple sheets of the spreadsheet use the same range name, specify the sheet name without additional quotation marks — for example, getRangeByName('TaxRates') or getRangeByName('Sheet Name!TaxRates'), but not getRangeByName('"Sheet Name"!TaxRates').

So I can remove the clunky if statements from my clearRangeByName function and just use a scoped range name when calling getRangeByName.