I'm having trouble with what seems like a very simple problem. Yet, I don't know how to fix it.

I am trying to clear cell A2 if and only if A1 is empty. I'm sure there is an easy solution but I don't seem to notice it. I'll share a sample spreadsheet for all of you to visualize my objective.

https://docs.google.com/spreadsheets/d/1qiq0w4xcUDO8pkFyeMO2ma_KR48evSsVAP6o50O0RtI/edit#gid=0

This is what I tried.

function onEdit(e) {
  var sheet = SpreadsheetApp.getActive().getSheetByName("TEST");
  var range = e.source.getActiveRange();

  if(e.range.getRow() == 1 && e.range.getColumn() == 1) {
    if(sheet.getRange("A1").getValue() == "") {
      sheet.getRange('A2').clearContent();    
    }
  }   
 }

Is there any problem with what I'm doing?

Help would be awesome.

CodePudding user response：

The below works for me. I think you might be getting the selected range which may be different? Also you might not want to run this on other sheets? If performance becomes an issue, it might make sense to reduce the && statements and use more if statements.

function onEdit(e) {
  const range = e.range;
  const zzSheetName = "TEST";
  const ss = range.getSheet();

  if(ss.getName() == zzSheetName
    && ss.getRange("A1").isBlank()
    && range.getRow()==1 
    && range.getColumn()==1
    && (!ss.getRange("A2").isBlank())
    ){
      ss.getRange('A2').clearContent();    
    }
 }

CodePudding user response：

Clear A2 if A1 is empty

function onEdit(e) {
  var sh = e.range.getSheet();
  if (sh.getName() == "TEST" && e.range.columnStart == 1 && e.range.rowStart == 1 && e.value === '') {
    e.range.offset(0, 1).clearContent();
  }
}