I'm trying to clean a spreadsheet of a slew of Named Ranges I no longer need, and leave behind the few I'm still using. All of these Named Ranges include the Tab name, because they originate on a template Tab (named tmp), from which other Tabs are duplicated. Even after I delete all the spinoff Tabs from the sheet and leave behind only tmp, the 'tmp'! appears in the names of the Ranges, both as displayed in the Named Ranges sidebar and as they come in on getNamedRanges().

When I try to selectively delete obviated Named Ranges, no matter how I spec the name of the Ranges, I get errors saying no such Named Ranges exist. Basically, I'm feeding back the same information getNamedRanges() and getRange().getSheet().getSheetName() give me, only to have it garbled along the way.

The problem is isolated in the following test snippet, and involves rendering the single quotes around the Tab name. I have tried several approaches, including escaping the single quotes with slashes, and have added to the code the Comments of the errors I got on the line targetDoc.removeNamedRange(namedRange).

const analyzerDoc = '1pYgcX2dxzHd4cCofy0RFZTzEl36QesiakMGIqCC2QlY'
const openAnalyzerDoc = SpreadsheetApp.openById(analyzerDoc)


function testDeleteNamedRange (){
  var docUrl = openAnalyzerDoc.getRangeByName('docUrl').getValue();
  var targetDoc = SpreadsheetApp.openByUrl(docUrl);
  // var namedRange = 'dyCl_MoodEntries'         // The named range "dyCl_MoodEntries" does not exist.
  // var namedRange = 'tmp!dyCl_MoodEntries'     // The named range "tmp!dyCl_MoodEntries" does not exist.
  // var namedRange = "'tmp'!dyCl_MoodEntries"   // The named range "'tmp'!dyCl_MoodEntries" does not exist.
  // var namedRange = "\'tmp\'!dyCl_MoodEntries" // The named range "'tmp'!dyCl_MoodEntries" does not exist.
  targetDoc.removeNamedRange(namedRange);
}

This bug is in the way of a longer function, which is working fine but for the part isolated in this test function.

The longer function gets the names and Tabs of Ranges to delete from this sheet:

What is the right way to do this? Thank you!

CodePudding user response：

This function will remove all of the named range that have their sheet name within the range name.

function deleteAllNamedRange() {
  const ss = SpreadsheetApp.getActive();
  ss.getNamedRanges().filter(r => ~r.getName().indexOf(r.getRange().getSheet().getName())).forEach(r => r.remove());
}

CodePudding user response：

I think I have the answer to my own question. Testing it will have to wait till tomorrow; I will edit this then.

It occurs to me that the outcome of getNamedRanges() is not an object, but an array. It should therefore be possible to specify a Named Range by its position in that array, and thereby completely sidestep the challenge I am facing by specifying it by its name.

The solution would then seem to lie in amending my process of collecting the Names and other information from the result of getNamedRanges(). Hereto I relied on the commonly recommended forEach. Perhaps there is a way to retrieve array position with forEach; I do not know, but in any case I ought to be able to do that by instead looping over the result of getNamedRanges().

I can then log those numbers in the same sheet where I am logging their Names, Ranges, and finding the formulas that reference them, and in which I am making my selections for removal. I should then be able to delete the selected ranges by referring to that set of numbers, by again looping over the array returned by getNamedRanges(), but in reverse order.

I will revise this post tomorrow and say whether or not it works.

CodePudding user response：

If you want to delete just the specific named ranges, you may benefit from trying the code below:

function testDeleteNamedRange() {
  var openAnalyzerDoc = SpreadsheetApp.openById('SPREADSHEET_ID');
  var namedRanges = ['NamedRange1', 'NamedRange2', 'NamedRange3', 'NamedRange4'];
  namedRanges.forEach(range => openAnalyzerDoc.removeNamedRange(range));
}

Before running the code

After executing the testDeleteNamedRange function

Explanation

The named ranges you want to delete have been added to an array; therefore, by looping through this array each one of the named ranges in removed successfully from the spreadsheet.

The removeNamedRange method expects an object of type String which is the name of the range which needs to be removed.

As for the getNamedRanges you have mentioned, this method ends up retrieving all the named ranges from a spreadsheet in the form of an array with NamedRange objects - NamedRange[].

Reference

• removeNamedRange(name);

• getNamedRanges().