1

I'm looking to run some script to clear the contents of a cell in column E (i.e. E3) when a cell in column I (i.e. I3) is edited ("Complete" is selected from drop down list). I found this script on another forum but am new to script editing and am not sure how to make it fit my needs (and if I can). Any and all help would be much appreciated.


// when a cell in column A (A:A) is changed, clear the corresponding cell in column B
function onEdit(e) {
  if(e.range.columnStart === 1) { 
    e.range.offset(0,1).clearContent();
  }
}
1

When a cell in column I is edited with the value "Complete, you want to clear the contents of the cell on the same row in Column E.

Note some of the differences between this answer and your script:

  • each of the watched values are defined (column and value) as is the target column. While this is NOT essential, it does makes it easier to manage.
  • there is a single IF statement in which the conditions are described.
    • each of the conditions is separated by && ("AND")
  • The target cell doesn't need to be defined as an offset; it can just be defined as the same row, and a given column.
    • sheet.getRange(e.range.rowStart, clearCol).clearContent()

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // identify the range and values to watch and clear
  var watchCol=9; // Column I
  var watchValue = "Complete";
  var clearCol = 5; // Column E
  // 

  // Logger.log("DEBUG: the column = "+e.range.columnStart+", and the value = "+e.value)
  if (e.range.columnStart === watchCol && e.value === watchValue) { 
    // Logger.log("DEBUG: the conditions were met");   
    sheet.getRange(e.range.rowStart, clearCol).clearContent()
  }

  return false;
}
3
  • Thanks @Tedinoz I see that we've defined the row, however, I was wanting this to work for all rows (i.e. for any row where column I contains "Complete", the contents in Column E will be cleared). Will your script address this? I ran the above script and but nothing is happening when I change I2 to "Complete". Do you have any troubleshooting advice for a noob? Thanks!! – Dannielle Cripps Dec 17 '20 at 5:13
  • FYI: "i.e. I3" <> "e.g. I3". I was wanting this to work for all rows Yes, I did wonder about that; it is a simple change and is a good reason why one uses the "row,column" format of getRange(). – Tedinoz Dec 17 '20 at 6:18
  • So, this now works on any row at all. The IF statement tests only for the Column number (9) and the change value ("Complete"). and the row number for the cell to be cleared = e.range.rowStart = the row that was edited. As I said, a fairly simple and (I hope) logical change. – Tedinoz Dec 17 '20 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.