Skip to content
Advertisement

Why does this script keep giving me Rows out of bound error?

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);
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement