CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
RoosterMagic22
RoosterMagic22

Reputation: 69

How do I edit my script to stop out of bound errors

I have a google sheet where I need to clear lines from rows 9 and down, the problem is my script is returning the following error;

"Exception: Those rows are out of bounds, delete rows @ Delete Rows.gs:4+ errors"

I'm assuming it's because I need to reverse the script to delete from the bottom up, can anyone provide an edit for my script so that it stops the out-of-bound errors?

This is the script I have

function deleterows() {
  var ss = SpreadsheetApp.getActive().getSheetByName('Timesheet');
  var lastrow= ss.getLastRow();
  ss.deleteRows(10, lastrow-1);
};

Upvotes: 0

Views: 68

Answers (2)

Cooper
Cooper

Reputation: 64062

Works for any start row

function deleterows() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName('Timesheet');
  const sr = 10
  ss.deleteRows(sr, sh.getLastRow() - sr + 1);
};

Upvotes: 1

Wicket
Wicket

Reputation: 38160

Replace

ss.deleteRows(10, lastrow-1);

by

ss.deleteRows(10, lastrow - 9);

The above is because using lastrow-1 the script is trying to delete more rows than those available counting from row 10.

Let say that the last row of the sheet is 1000:

  • 1000 - 1 = 999, the start row is 10, it will try to delete from row 10 to 1008, but the sheet only has 1000 rows.
  • 1000 - 9 = 991, start row is 10, it will delete from row 10 to row 1000, in other words 991 rows including row 10.

Upvotes: 3

Related Questions