Deleting named ranges from active cells in google sheets with a script

Question

I am new to the google sheets environment and my limited knowledge of VBA does not help. I would like to be able to delete range names from a cell or multiple cells selected by the "curser" on google sheets.

I did manage code deleting all named ranges from a sheet but that is not what I want. Your assistance will be appreciated.

Illustrate Active Cell and Named range circled in red

I would like the script to delete the named range map_4bf9.... in live cell C17. Preferably I would like to be able to select say range C17:H18 and let the script delete the named ranges from all those cells which are next to each other.

To do it manually in google sheets you will go to Data/Named Ranges and then search for the range name from the listing to the right and then manually delete it.

Google sheets select named ranges

Search and manually delete

The script removing all named ranges on a sheet was as follows:

function delRange(){ var namedRanges = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getNamedRanges(); for(i=0;i<namedRanges.length;i++){ namedRanges[i].remove(); } }

Answer 1

This is not quite what you were asking for, but it comes close I believe.

Image of Original Data:

Image of Active Ranges

Image of range After Running Code:

function myFunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const remove = sh.getActiveRangeList();
  const rg = ss.getRangeByName('Array1');
  const row = rg.getRow();
  const col = rg.getColumn();
  let vs = rg.getValues();
  Logger.log('Before\n %s',JSON.stringify(vs).replace(/],/g,'],\n'));
  remove.getRanges().forEach(r => r.setValue(""));
  SpreadsheetApp.flush();
  vs = rg.getValues()
  Logger.log('After:\n %s',JSON.stringify(vs).replace(/],/g,'],\n'));
}

Execution log
9:01:23 AM  Notice  Execution started
9:01:23 AM  Info    Before
 [[17,20,26,0,10],
[10,31,22,24,4],
[19,31,16,1,29],
[3,5,17,22,8],
[15,0,30,11,30],
[7,5,23,10,3]]
9:01:23 AM  Info    After:
 [["",20,26,0,""],
[10,31,22,24,4],
[19,31,16,1,29],
[3,5,17,22,8],
[15,0,30,11,30],
["",5,23,10,""]]
9:01:25 AM  Notice  Execution completed

I think this version is closer to what you might be looking for because it just changes the values of the active cells in the array without actually changing the values in the range.

function myFunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const remove = breakUpRangeList(ss,sh,sh.getActiveRangeList());
  const rg = ss.getRangeByName('Array1');
  const row = rg.getRow();
  const col = rg.getColumn();
  let vs = rg.getValues();
  let bs = vs.slice();
  Logger.log('Before\n %s',JSON.stringify(vs).replace(/],/g,'],\n'));
  remove.getRanges().forEach(r => bs[r.getRow()-row][r.getColumn()-col] = "")
  Logger.log('After:\n %s',JSON.stringify(bs).replace(/],/g,'],\n'));
  return bs;
}

And it requires this helper function which breaks rangeLists into separate single cell rangelists

function breakUpRangeList(ss = SpreadsheetApp.getActive(), sh = ss.getSheetByName("Sheet0"), rgl) {
  let b = [];
  rgl.getRanges().forEach(rg => {
    rg.getValues().forEach((r, i) => {
      let row = rg.getRow() + i;
      r.forEach((c, j) => {
        let col = rg.getColumn() + j;
        b.push(sh.getRange(row, col).getA1Notation())
      })
    })
  })
  b = [...new Set(b)];
  //Logger.log(JSON.stringify(b));
  return sh.getRangeList(b);
}

Using the same named range as before here is an image of the active range:

And here is the execution logs for this version:

Execution log
9:42:53 AM  Notice  Execution started
9:42:54 AM  Info    Before
 [[9,14,28,32,0],
[9,34,28,28,32],
[18,2,33,0,11],
[19,2,15,14,19],
[3,9,4,21,21],
[32,24,22,7,28]]
9:42:54 AM  Info    After:
 [["","",28,32,0],
["","",28,28,32],
["","",33,0,11],
[19,2,15,14,19],
[3,9,4,21,21],
[32,24,22,7,28]]
9:42:56 AM  Notice  Execution completed

Notice that no data in the original range or array has been changed

I got to thinking that this might be what you want. It's a rangeList of the named range missing the active cells

function myFunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const remove = breakUpRangeList(ss, sh, sh.getActiveRangeList());
  const rg = ss.getRangeByName('Array1');
  const row = rg.getRow();
  const col = rg.getColumn();
  let vs = rg.getValues();
  let arr1 = vs.map((r, i) => r.map((c, j) => sh.getRange(i + row, j + col).getA1Notation())).flat();
  let arr2 = arr1.slice();
  remove.getRanges().forEach(r => { let idx = arr2.indexOf(r.getA1Notation()); if (~idx) { arr2.splice(idx, 1); } });
  Logger.log("arr2:\n %s",JSON.stringify(arr2));
  return sh.getRangeList(arr2);
}

I think that's about it. I can't think of any other workarounds