Google Sheets onEdit - clearing select content in the row above the active row in which the edit occurred

Question

I'm currently building out an onEdit script that clears the content of select cells upon edit. The issue I'm running into is that the cells to be cleared are dependent on the cell that was edited, so I can't use fixed ranges.

Please see Sheet2 of my spreadsheet here: https://docs.google.com/spreadsheets/d/1EoOIQxWyKWOvtlCrmJNI76FAxGhzgXrE4s0F05tw2MY/edit#gid=1388181285

Below is my current attempt at this:

 function onEdit(e){
  var submitColumn = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('H:H');
  var sh=e.range.getSheet();
  if (sh.getName()=='Sheet2' && e.range.columnStart==8 && e.range.rowStart>1 && e.value=="submitResponse") {
    var msh=e.source.getSheetByName("Master");
    msh.appendRow(sh.getRange(e.range.rowStart,1,1,7).getDisplayValues()[0]);
    var rowAbove = sh.getRange(e.range.rowStart,1,1,7).getLastRow()-1;
    submitColumn.clearContent();
    rowAbove.clearContent();
  }
}

I'm trying to clear out two things:

Upon edit clear out the cell in column H that I changed to "submitResponse." Currently my script clears out the full column, which is not exactly what I want but it gets the job done.
Clear out the select cells in the rows above my target row. These rows (ex: 11,16,21,26...) are user-entered and inform the data in my target row (ex: 12,17,22,27...). Upon edit I'm trying to clear out the specific user-entered cells that informed the row I copied to the Master sheet. Currently my script – if it were working as written – would clear out the full row of user-entered cells, which is not what I want. Let me clarify with an example: when I change H12 to submitResponse, I want the following to happen: 1) A12:G12 is copied to the Master sheet; 2) H12 is cleared; 3) B11,D11:F11 is cleared. ... There may be upwards of 50 of these potential "submitResponse" rows so I'm trying to avoid fixed ranges if possible.

Google Sheets onEdit - trying to copy a row to a new sheet by changing the value of a column within that same row

Cooper Cooper · Accepted Answer · 2020-05-15T16:44:06

function onEdit(e) {
  var sh=e.range.getSheet();
  if (sh.getName()=='Sheet2' && e.range.columnStart==8 && e.range.rowStart>1 && e.value=="submitResponse") {
    e.range.setValue('');//the edited cell
    var msh=e.source.getSheetByName("Master");
    msh.appendRow(sh.getRange(e.range.rowStart,1,1,7).getDisplayValues()[0]);//append data no change
    e.range.offset(-1,-5).clearContent();//clear cell B in row above
    sh.getRange(e.range.rowStart-1,4,1,3).clearContent();//clear D,E & F of row above
  }
}

Google Sheets onEdit - clearing select content in the row above the active row in which the edit occurred

1 Answers