below is a code that checks if the cells at columns [G, H, J] are empty and delete the row where the the condition is true. Nevertheless, the runtime of the code below is extremely slow, needs approx 15 minutes per 1500 table entries. is there any way to optimise it ? can I hash the rows that meet the condition below and then delete them all at once?
P.S: the original code can be found here https://gist.github.com/dDondero/285f8fd557c07e07af0e which I adapted it to my use case.
function deleteRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var lastRow =sheet.getRange(1,1).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow() + 1;
var values = rows.getValues();
var row;
var rowsDeleted = 0;
for (var i = 0; i < lastRow; i++) {
row = values[i];
if (row[9] == '' && row[7] == '' && row[6] == ''
) {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
}
function DeleteEmptyRows() {
const sheet = SpreadsheetApp.getActiveSheet()
const values = sheet.getDataRange()
.getValues()
.filter(row => row[9] !== '' && row[7] !== '' && row[6] !== '')
sheet.getDataRange().clearContent()
return sheet.getRange(1, 1, values.length, values[0].length)
.setValues(values)
}
If you have any background colors attached to rows, let me know and I can make an adjustment for you.
This code will filter out all rows with the empty cells specified, clear the sheet, and then set the values.
function deleteRows() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
const rg = sh.getDataRange();
const vs = rg.getValues().filter(r => !(!r[6] || !r[7] || !r[9]));
rg.clearContent();
sh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
}