This code runs with no error, but sometimes it gives me Exception: Those rows are out of bounds. error.
function formatBoqPipework() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const boqPipeworkSheet = ss.getSheetByName('BOQ Pipework');
let boqPipeworkRng = boqPipeworkSheet.getRange(5, 1, boqPipeworkSheet.getLastRow(), 14);
let boqPipeworkValues = boqPipeworkRng.getValues();
for (let a = boqPipeworkValues.length - 1; a >= 0; a--) {
if (boqPipeworkValues[a][0] === 'Pipework' || boqPipeworkValues[a][0] === '' || boqPipeworkValues[a][0] === 'Section Total') {
boqPipeworkSheet.deleteRow(a + 5);
}
}
}
This is actually called after a couple of other functions, but I am making sure the others finish before having this one called.
I even tried adding a Utilities.sleep(3000) at the beginning of this function.
I understand that there are proper ways to do that, but that's too advanced for me at this point.
Appreciate any help.
I thought that in your script, this script of let boqPipeworkRng = boqPipeworkSheet.getRange(5, 1, boqPipeworkSheet.getLastRow(), 14); is required to be modified.
In your current script, the values are retrieved from the row 5 to 4 more rows after the last row. In this case, it should be boqPipeworkSheet.getRange(5, 1, lastRow - 4, 14). But, when the value of boqPipeworkSheet.getLastRow() is less than 4, an error occurs. It is required to reflect this. So how about the following modification?
let boqPipeworkRng = boqPipeworkSheet.getRange(5, 1, boqPipeworkSheet.getLastRow(), 14);
const lastRow = boqPipeworkSheet.getLastRow();
if (lastRow < 5) return;
let boqPipeworkRng = boqPipeworkSheet.getRange(5, 1, lastRow - 4, 14);
Try it this way:
function formatBoqPipework() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sh = ss.getSheetByName('BOQ Pipework');
let vs = sh.getRange(5, 1, sh.getLastRow(), 1).getValues();
let d = 0;
for (let a = 0; a < vs.length; a++) {
if (vs[a][0] == 'Pipework' || vs[a][0] == '' || vs[a][0] == 'Section Total') {
sh.deleteRow(a + 5 - d++);
}
}
}