Google sheet onEdit when value of a certain cell c

问题:

I want cell J2:K2 to be cleared ONLY when value in I1 changes.

I have the following script so far :

function onEdit(e) {

    if (e.range.getA1Notation() === 'i1') {
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1').getRange('j2:k2').clear() 
    }
}

I thought onEdit would only fires up when I1 is changed however range J2:K2 keeps getting cleared when I try to write values in either J2 or K2. Can someone please help me?

回答1:

Simple fix. This works for me:

function onEdit(e) { 

  if (e.range.getA1Notation() === 'I1') {
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1').getRange('j2:k2').clear();
  }
}

The problem was your if statement condition was not getting met. This is because .getA1Notation() returns a string description of the range which needs to be exact. Replacing i1 with I1 fixes the problem.

Hope this helps!