0

I need any edits/changes to a specific cell, in a specific sheet (tab) to generate an onEdit trigger.

I have searched, and tried, dozens of potential solutions, but can’t seem to make any of them work.

My specific 'edit' cell is "H8" and it is located on a sheet (tab) called: “Land”

And just for this example, I want the trigger to delete the contents of Cell “A1” on sheet: “test”

Attempt #1: Based on this link: https://stackoverflow.com/a/48964929/11317736

function onEdit(e) { 
  if (e.range.getA1Notation() === 'Land!H8') {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test').getRange('A1').clear();
  }
}

ERROR CODE: TypeError: Cannot read property "source" from undefined. (line 2, file "Code")Dismiss

Attempt #2: Based on many different posts

function onEdit(e) {
      var range = e.range;
      var rangeEdit = e.range.getA1Notation();
      if(rangeEdit == "Land!H8"){
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test').getRange('A1').clear();
          }
    }

ERROR CODE: TypeError: Cannot read property "range" from undefined.

Any help would be greatly appreciated

player0
  • 124,011
  • 12
  • 67
  • 124
blafarm
  • 69
  • 1
  • 10
  • Possible duplicate of [How can I test a trigger function in GAS?](https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas) – Rubén Jul 05 '19 at 01:26

1 Answers1

3

Try this:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(e.range.columnStart==8 && e.range.rowStart==8 && sh.getName()=="Land") {
    //put your code here
    e.source.toast('You enter a new value of ' + e.value);//remove this line
  }else{
    return;
  }
}

Please note this function cannot be run from your script editor without you creating you own event object. The alternative is to copy it into your script editor and edit cell H8 of a sheet named "Land" to see if it's working for you. Note: an edit requires that you actually change the value of H8.

I already tested it and it's working. I guess that I should mention that just putting an e in the onEdit(e) does not create an event object.

Straight from the range documentation page:

  • clear() Clears the range of contents, formats, and data validation rules.
  • clear(options) Clears the range of contents, format, data validation rules, and/or comments, as specified with the given advanced options.
  • clearContent() Clears the content of the range, leaving the formatting intact.
  • clearDataValidations() Clears the data validation rules for the range.
  • clearFormat() Clears formatting for this range.
  • clearNote() Clears the note in the given cell or cells.
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thank you, but I am getting the following error when running your code exactly as you listed it: TypeError: Cannot read property "range" from undefined. (line 2, file "Code")Dismiss – blafarm Jul 05 '19 at 01:29
  • Read the comments below the code section in the answer. How are you testing it? I have heard this response dozens of times because people don't realize the importance of the event object represented by the parameter e in the function. – Cooper Jul 05 '19 at 01:50
  • Since I know that this function works because I tested it , all you have to do to test it is copy the function and post it into the Code.gs file. Making sure that there are no other functions named onEdit and then go back to the spreadsheet and go to the sheet name "Land" and change the value on H8 and look for the toast message in the lower right hand corner of the spreadsheet. – Cooper Jul 05 '19 at 01:58
  • Sorry Cooper. My initial test DID include the directions you subsequently provided. However, my earlier comment listing the Error Message I received was due to Saving your code, and then manually Running it within script environment. After testing so many alternatives, which did not work, I came to associate that Error Message with the notion that there was a problem with the code. However, that is not the case with your code. It works. Thank you very much. Question: Is there any way to reduce the latency between changing the value of H8 and the subsequent cell clearing? – blafarm Jul 05 '19 at 02:12
  • You could purchase Google and get your staff to give your code the highest priority. Other than that not really. It will vary. – Cooper Jul 05 '19 at 02:19
  • Also, I know this sound crazy, but: SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test').getRange('A1').clear(); is not only clearing the value in Cell A1 -- it is also clearing the formatting (Fill Color, Border, Merged Cells). Do you happen to know of a command that ONLY clears the cell value -- and not everything else. Thanks very much. Interestingly, all of my previous test did not do this. – blafarm Jul 05 '19 at 02:20
  • I found my mistake: clearContent. Thanks very much for all of your help. – blafarm Jul 05 '19 at 02:26