Need help with Google moving rows on open for Google Sheets Script

Question

I am fairly new to Google script. My knowledge of Java is limited as well (I believe the two are similar). I have a script that moves rows when a checkbox is checked. It also adds some rows and does some other things, please see below for the script. No issues with the script as it is now. What I would like to do is to modify the script so that it stops moving rows when they are checked ( I know how to do that) and to instead move all of the checked rows as a time event. I know I need to add a time trigger and know how to do that however what I do not know is how to tell the script to check what rows are checked. So, basically I would like the script to move all of the checked rows to another sheet, for example at 7 am, and also to add new rows. The number of added rows should be equal to the number of moved rows. If I could get any examples, tips or directions, it would be greatly appreciated.

The script I am using now (found and modified from scripts available online):

​function MoveOnCompleted(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ToDo");
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ToDo");
  var data = "B3:B"
  var datarange = s.getRange(data);
  var r = datarange

  if(s.getName() == "ToDo" && r.getColumn() == 2 && r.getValues() == 1) {
    var TimeStamp = r.offset(0,1);
    var DateTime = new Date();
    TimeStamp.setValue(DateTime);
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
    s.deleteRow(row);
    s.insertRowAfter(2300);
    var tableRange = "A3:G";
    var range = s.getRange(tableRange);
    range.sort([{column: 5, ascending: true}]);
  }
}

Answer 1

Your data on sheet "ToDo" includes a checkbox in Column B. If the checkbox is checked, then you want to copy that data to the next row on sheet "Completed", and then delete the relevant row from "ToDo".

This solution is in two parts:

• Part 1 - copy rows from "ToDo" to "Completed
  • get the last row, define a range from the first row of data to the last row, and then loop through the rows testing for a true value on the checkbox (true = checked)
  • when a true value is found, the row values are added to a temporary array
  • at the end of the loop, the temporary array is added to the bottom of the "Completed" sheet.
• Part 2 - delete the checked rows
  • a second loop is used to evaluate the checkbox in each row for a value = "true";
  • the loop runs-backwards; from the last row to the first row in the range. This enables the script to delete existing row numbers without the problem of the row number constantly changing if the loop were to run from top to bottom.
  • as a row is found to have the checkbox checked, the row is deleted.

---

function wa14508003() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("ToDo");
  var targetSheet = ss.getSheetByName("Completed");
  
  // get the last row and column of data on "to do"
  var sheetLR = sheet.getLastRow();
  var sheetLC = sheet.getLastColumn();  
  var targetLR = targetSheet.getLastRow();
  
  // assume that there are headers in rows 1 & 2
  var range = sheet.getRange(3,1,sheetLR-3+1,sheetLC);
  // Logger.log("DEBUG: the range is "+range.getA1Notation());
  // get the values  
  var values = range.getValues();

  // create a temporary array to hold the "moved" rows
  var temp = []
  
  for (var i=0;i<sheetLR-3+1;i++){
    var thisrow = i+3;
    if ( values[i][1] == true){
      // temporary array for this row
      var thisrow = [];

      //Logger.log("DEBUG: do something")
      for (var c = 0; c<sheetLC; c++){
      thisrow.push(values[i][c]);
      }
      // add this row to the temporary array
      temp.push(thisrow);    
    }else
    {
      //Logger.log("do nothing")
    }
 }

  // update the checked rows to the targhet sheet
  var targetrange=targetSheet.getRange(targetLR + 1, 1,temp.length,sheetLC);
  targetrange.setValues(temp)

  // now delete the rows 
  // get the checkbox column
  var checkboxRange = sheet.getRange(3, 2, sheetLR-3+1, 1);
  // Logger.log("DEBUG: checkbox range = "+checkboxRange.getA1Notation())
  var checkboxValues = checkboxRange.getValues();
  // Logger.log(checkboxValues); // DEBUG

  // delete from the bottom up so that row numbers retain content
  for (var r=sheetLR-3;r>=0;r--){
    // Logger.log("DEBUG: r = "+r+", row = "+(r+3)+", checkbox value  = "+checkboxValues[r]);
    if (checkboxValues[r] == "true"){
      // Logger.log("DEBUG: delete this sheet")
      sheet.deleteRow(r+3);
    }
   else
   {
     // Logger.log("DEBUG: don't delete this sheet")
   }
 }

 // now insert blank rows to make up  for the ones deleted
 SpreadsheetApp.flush();
 var sheetnewLR = sheet.getLastRow();
 sheet.insertRowsAfter(sheetnewLR,temp.length);
 return;
}

---