How to delete Named Ranges with Tab names via Apps Script

Question

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!

Answer 1

I have an answer to my own question. There is probably more than one solution, but I have chosen to sidestep the challenge I am facing, and instead of specifying the Named Ranges by name, I am going to spec them by their position in the document's Named Ranges, and simply use remove() instead of removeNamedRange(namedRange). I had gotten so caught up in the recommended method involving forEach, that I had forgotten that the outcome of getNamedRanges() is not an object, but an array.

The solution then lies in amending my process of collecting the Names and other information from the result of getNamedRanges(). Instead of using forEach, I loop over the results of getNamedRanges(), and while I get the information I desire concerning each Named Range, I also log the loop iteration and thereby get the Index Number of each Named Range.

I proceed as before, pasting this information in a Tab where I can select which Ranges to delete.

My delete function then loops over the Named Ranges directly, in reverse order, and checks the loop iteration against the Ranges I have ticked off in that analysis Tab.

I have tested this in a sample document; you may view it here.

In this demo, all functions are within the same document, so I'm using getActive() instead of openByUrl.

This document has 3 Tabs named Sheet1, Sheet2 and Sheet3. Each Tab has 3 Named Ranges named Moe, Larry and Curly. There is also a Tab NamedRanges which the following function collects Named Range into:

function getnamedRanges() {
  var namedRanges = SpreadsheetApp.getActive().getNamedRanges();
  var namedRangeData = [];
  for (i=0; i<namedRanges.length; i++) {
    var namedRange = namedRanges[i];
    var nrName = namedRange.getName();
    var nrRange = namedRange.getRange().getA1Notation();
    namedRangeData.push([nrName,nrRange,i])
  }
  SpreadsheetApp.getActive().getSheetByName('NamedRanges').getRange(2,1,namedRangeData.length,3).setValues(namedRangeData)
}

Here's the Named Range Tab after running that function, and choosing 3 Named Ranges to delete:

Next, here is the function that removes the selected Named Ranges:

function deleteSelectedNamedRanges () {
  var namedRangeData = SpreadsheetApp.getActive().getSheetByName('NamedRanges').getDataRange().getValues();
  namedRangeData.shift(); // Remove header row data.
  var rangesToDelete = namedRangeData.filter(function(nrDatum) {if (nrDatum[3]==true) return nrDatum});

    // [3] equivalates to Column D, the checkboxes where I select which Named Ranges to delete.
    console.log (rangesToDelete.map(value => value[0])); // [ 'Sheet3\'!Moe', 'Sheet2\'!Curly', 'Sheet1\'!Moe' ]
    console.log (rangesToDelete.map(value => value[2])); // [ 0, 1, 5 ] // [2] is the index number of the Named Ranges.

    /* The order here derives from how values in Tab Named Ranges happen to be sorted. 
    In this instance, I have not changed that order, so the Named Ranges To Delete are in ascending order.
    For one thing, this is the opposite of what we want; 
    for another, I want to be able to sort the Named Range Tab freely before making my selections.
    So, we must sort this data in DESCENDING order. */

  rangesToDelete.sort(function(value1,value2){if (value1[2]<value2[2]) return 1; if (value1[2]>value2[2]) return -1; return 0});

    console.log (rangesToDelete.map(value => value[0])); // [ 'Sheet1\'!Moe', 'Sheet2\'!Curly', 'Sheet3\'!Moe' ]
  var rangesToDeleteIndexNumbers = rangesToDelete.map(value => value[2])
    console.log (rangesToDeleteIndexNumbers); // [ 5, 1, 0 ]
  var namedRanges = SpreadsheetApp.getActive().getNamedRanges();
  for (i=namedRanges.length-1; i>=0; i--) { 

    /* We must loop in descending order because deleting Named Ranges will change the index numbers
     of all Named Ranges that come after each we delete. */

    if (rangesToDeleteIndexNumbers.indexOf(i) !== -1) {namedRanges[i].remove(); console.log ('Removed NR # '+i)} 

    // Delete Named Range if this iteration number can be found in rangesToDeleteIndexNumbers.
  }
}

After running this function, you can see that the 3 Names Ranges have been removed, leaving 6 behind: