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([['','','','','']]);
}
}