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.
Advertisement
Answer
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?
From:
let boqPipeworkRng = boqPipeworkSheet.getRange(5, 1, boqPipeworkSheet.getLastRow(), 14);
To:
const lastRow = boqPipeworkSheet.getLastRow(); if (lastRow < 5) return; let boqPipeworkRng = boqPipeworkSheet.getRange(5, 1, lastRow - 4, 14);