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

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.

CodePudding user response：

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);

CodePudding user response：

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  );
    }
  }
}