Moving one row to bottom of a sheet after date has passed (updated)

Question

I posted a few months ago about looking for a macro that can help move a row of a sheet to the bottom of it once it passes a certain date. Original post here: Moving one row to bottom of a sheet after date has passed

I recently added two columns to this sheet and can't figure out how to update the script. Here's the updated sheet: https://docs.google.com/spreadsheets/d/1EPueop9bdky_J8VgpFdSUzzsMRieRUreeCRIy18ScTY/edit#gid=0

I'm also copying the script I've already updated below:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet(); // or const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  const row = sheet.getRange("A4:A" + sheet.getLastRow()).createTextFinder("Completed").findNext().getRow();
  const now = new Date().getTime();
  const moves = sheet.getRange("H4:H" + (row - 1)).getValues().reduce((ar, [f], i) => {
    if (f && f.getTime() < now) {
      const r = i + 4;
      ar.push(sheet.getRange(`A${r}:H${r}`));
    }
    return ar;
  }, []).reverse();
  const len = moves.length;
  if (len == 0) return;
  const lastRow = sheet.getLastRow();
  moves.forEach((r, i) => sheet.moveRows(r, lastRow - i + len - 1));
}

Unfortunately, I keep receiving the following error:

Error Exception: Those rows are out of bounds.

(anonymous) @ Code.gs:15

myFunction @ Code.gs:15

Would anyone be able to assist. It looks like this row is what's causing the issue, but I can't figure out how to adjust it.

moves.forEach((r, i) => sheet.moveRows(r, lastRow - i + len - 1));
}

I'm really just trying to have this work correctly, but can't figure out how to update it. I also want to figure out how to update the status from "Scheduled" to "Complete" when it moves to the row under Complete but that is a secondary issue.

Answer 1

When I saw your provided Spreadsheet, all rows have the data validation rules. By this, the last row retrieved by getLastRow() returns the bottom of the sheet. I think that this is the reason for your current issue of Error Exception: Those rows are out of bounds.. In your situation, how about the following modified script?

Modified script:

In this modification, the last empty row is retrieved using the sample script of https://stackoverflow.com/a/44563639. And, I adjust your script for your provided Spreadsheet.

function myFunction() {
  // ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  const sheet = SpreadsheetApp.getActiveSheet(); // or const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  const row = sheet.getRange("A4:A" + sheet.getLastRow()).createTextFinder("Completed").findNext().getRow();
  const now = new Date().getTime();
  const moves = sheet.getRange("H4:H" + (row - 1)).getValues().reduce((ar, [f], i) => {
    if (f && f.getTime() < now) {
      const r = i + 4;
      ar.push(sheet.getRange(`A${r}:H${r}`));
    }
    return ar;
  }, []).reverse();
  const len = moves.length;
  if (len == 0) return;
  const lastRow = sheet.get1stNonEmptyRowFromBottom(1);
  moves.forEach((r, i) => sheet.moveRows(r, lastRow + 1 - i));
}

Note:

• This sample script was tested using your provided Spreadsheet. When you change the Spreadsheet and/or your actual Spreadsheet is different from your provided Spreadsheet, this script might not be able to be used. Please be careful about this.