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

Question

I am looking for a macro that can help move a row of a sheet to the bottom of it once it passes a certain date. Basically this will be used for a meeting tracker and I'm trying to find a way to automatically move meetings to a "Completed" section once the date (located on Column F) passes.

I've created macros before to move things between sheets, but I'm unfamiliar with how to move things on the same sheet. Would anyone be able to help?

Here's the sheet: https://docs.google.com/spreadsheets/d/1EPueop9bdky_J8VgpFdSUzzsMRieRUreeCRIy18ScTY/edit#gid=0

I would like to move rows based on the date in Column F. Once it passes I would like it to move to the "Completed" section of the sheet. This is an active spreadsheet so the row "Completed" it's on could change as meetings are being added.

Answer 1

function moveActiveRowToBottom() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const r = sh.getActiveRange().getRow();
  const vs = sh.getRange(r,1,1,sh.getLastColumn()).getValues();
  sh.getRange(sh.getLastRow() + 1, 1, vs.length, vs[0].length).setValues(vs);
  sh.deleteRow(r);
}

Answer 2

I believe your goal is as follows.

• You want to check the date of column "F" of the sheet. When the date of column "F" is smaller than today, you want to move the row to the last row.
• You want to achieve this in the same sheet in a Google Spreadsheet. And, the sheet has a row of "Completed" in column "A", you want to check the date of the above rows of the "Completed" row.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet, and save the script. When you use this script, please run the function of myFunction().

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("F4:F" + (row - 1)).getValues().reduce((ar, [f], i) => {
    if (f && f.getTime() < now) {
      const r = i + 4;
      ar.push(sheet.getRange(`A${r}:F${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));
}

• When this script is run, the column "F" of the rows from 4 to the "Completed" row is checked. And, when there are moving rows, the rows are moved to the last row of the sheet.

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.

• When I saw your sample Spreadsheet, it seems that the sheet name is Sheet 1. If you want to use the sheet using the sheet name, please be careful about this.

References:

• reduce()
• forEach()
• moveRows(rowSpec, destinationIndex)