CODEWITHSUNDEEP
google-sheetsgoogle-apps-script
Alex D
Alex D

Reputation: 1

I'm trying to program a Apps script to Clear Contents of a named range when value of different cell is edited

I have a google sheet that I need to program a function that clears the contents of a range of cells, whenever the value of a specific cell(F10:G10) is edited. I am a novice in Apps script, but understand basic functionality. I believe I need to use the "onEdit" function to clear the contents of the other cells.

I found this script, but keep getting a TypeError (Cannot read properties of undefined (reading 'range") when trying to run. Please help.

RE: Change/Edit to Sheet name "Pricer", and edited cell(s) F10:G10, clear named range "Clear1"

Script:

function onEdit(e){
 const sheet = SpreadsheetApp.getActive().getSheetByName('Pricer');
  if(sheet.getName() == "Pricer" && e.range.getA1Notation()=='F10:G10' )
  {
    sheet.getNamedRanges('Clear1').clear();
  }
}```

Upvotes: 0

Views: 159

Answers (2)

Cooper
Cooper

Reputation: 64100

Try it this way:

function onEdit(e){
 //.source.toast('Entry');
 const sh = e.range.getSheet();
  if(sh.getName() == "Pricer" && e.range.columnStart > 5 && e.range.columnStart < 8 && e.range.rowStart == 10) {
    //e.source.toast("Gate1")
    e.source.getRangeByName('Clear1').clearContent();
  }
}

You cannot run these functions without supplying the event object. If you attempt to run from the script editor without supplying the event object you will get an error. The onEdit trigger is a simple trigger meaning that it cannot perform functions that require permission. If you need to perform commands that require permission you must create an installable trigger either programmatically or through the editors trigger panel.

There are several limitations running scripts this way. You will have difficulty using alert and prompt boxes. They are fairly difficult to debug because the onEdit is not easily run in debug mode so you are left having to edit the appropriate elements on the desired sheet."

Upvotes: 0

dataful.tech
dataful.tech

Reputation: 945

You need to use getRangeByName (documentation) instead of getNamedRanges (documentation) as the latter returns an array of named ranges. Try this script:

function onEdit(e){
 const sheet = SpreadsheetApp.getActive().getSheetByName('Pricer');
  if(sheet.getName() == "Pricer" && e.range.getA1Notation()=='F10:G10' )
  {
    SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Clear1').clear();
  }
}

I am not sure about the condition part:

  1. sheet.getName() == "Pricer" will always be true because earlier you get the sheet by the same name.
  2. e.range.getA1Notation()=='F10:G10' will be true only if all F10:G10 cells are edited in a single operation.

Upvotes: 1

Related Questions