Home > database >  Pulling array, based on the onEdit range
Pulling array, based on the onEdit range

Time:11-15

I have the following script. Its full purpose is to remove cells of columns B,C,D,E,F if the content in column A was deleted, only on the matching row. It functions as it should. However, it is a bit slow.

I couldn't figure out a way to do it myself. Which is why I am here. How could I go about adjusting it, so instead of removing the cells individually, I could instead grab a full range of them, in the matching row? Perhaps based on the offset of the initially adjusted cell.

  function onEdit(e) {
  if(e.range.columnStart === 1 
  && e.range.rowStart > 1 
  && e.range.getSheet().getName() == 'Sheet1'
  && e.range.getValue() == '') { 
    e.range.offset(0,1).deleteCells(SpreadsheetApp.Dimension.ROWS);
    e.range.offset(0,2).deleteCells(SpreadsheetApp.Dimension.ROWS);
    e.range.offset(0,3).deleteCells(SpreadsheetApp.Dimension.ROWS);
    e.range.offset(0,4).deleteCells(SpreadsheetApp.Dimension.ROWS);
    e.range.offset(0,5).deleteCells(SpreadsheetApp.Dimension.ROWS)
  }
}

CodePudding user response:

To implement a couple of additional checks for copy-paste and the like, and to observe onEdit(e) best practices, try this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (e.range.columnStart !== 1
    || e.range.rowStart <= 1
    || e.range.getSheet().getName() !== 'Sheet1'
    || (e.value || e.range.getValue())
    || e.range.getHeight()   e.range.getWidth() > 2) {
    return;
  }
  e.range.offset(0, 1, 1, 5).clearContent();
}

CodePudding user response:

Try:

 function onEdit(e) {
  if(e.range.columnStart === 1 
  && e.range.rowStart > 1 
  && e.range.getSheet().getName() == 'Sheet1'
  && e.range.getValue() == '') { 
    deletecols()
  }
}

function deletecols(){
   
   var sh = SpreadsheetApp.getActive().getActiveSheet()
   var range = sh.getActiveRange()
   var numrows = range.getNumRows()
   var row = range.getRow()
   sh.getRange(row,2,numrows,5).clearContent()

}

CodePudding user response:

My not be that much faster:

 function onEdit(e) {
   const sh = e.range.getSheet();
  if(e.range.columnStart == 1 && e.range.rowStart > 1  && sh.getName() == 'Sheet1' && e.value == '') { 
    sh.getRange(e.range.rowStart,2,1,5).setValues([['','','','','']]);
  }
}
  • Related