Clearing values on gsheet with script

Question

I am trying to clear some values in a defined named range taking into account also other value from another defined range of the same size. Here is my code below.

I get a Out of range on rng_stages.getCell(i,0).clearContent() I suspect I no longer have a pointer to the sheet itself. As you can see I am not familiar with cell value assignment.

Does onOpen only triggers when the workbook opens or it also triggers each time a sheet is open in the same workbook? The named ranges only exist in the first sheet.

Here is the code:

function onOpen() {
   var rng_stages = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ClaimStages');
   var rng_levels= SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ClaimLevels');
   var arr_stages = rng_stages.getValues();
   var arr_levels = rng_levels.getValues();

   for (var i = 0; i < arr_stages.length; i++) {
      if ((arr_stages[i][0] == 'Approved') && (arr_levels[i][0] == -1)) {
          rng_stages.getCell(i,0).clearContent()
      }
   }
}

Unfortunately my company prevents sharing Google docs outside the company. — Bladerunner

Cooper Cooper · Accepted Answer · 2021-05-06T00:03:00

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const rng_stages = ss.getRangeByName('ClaimStages');
  const rng_levels = ss.getRangeByName('ClaimLevels');
  const arr_stages = rng_stages.getValues();
  const arr_levels = rng_levels.getValues();
  for (let i = 0; i < arr_stages.length; i++) {
    if ((arr_stages[i][0] == 'Approved') && (arr_levels[i][0] == -1)) {
      rng_stages.getCell(i+1, 1).clearContent()
    }
  }
}

Range.getCell(row, column)

Rows and Columns begin at 1

Clearing values on gsheet with script

1 Answers