1

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}]);
  }
}
  • Welcome. I notice you are inserting after row#2300, would you please explain your rationale for that. – Tedinoz Aug 28 at 12:04
  • Hi. Now that you mention it, I think I did that due to not fully understanding on how to insert a row after the last row with the data. – SystemWorks Aug 29 at 1:22
0

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

| improve this answer | |
  • I have used the code and it works. Exactly what I was looking for. Now, I just need to analyze it so that I get full understanding of it. Thank you. – SystemWorks Aug 31 at 2:28
  • By looking at the script, it copies the rows and then it deletes the rows it moved. I'd like to add for the script to insert the same number of deleted rows back into the sheet after deleting to avoid the sheet running out of rows. Or does it do that already? Not sure, but I think it does not. – SystemWorks Aug 31 at 2:32
  • I've edited the answer. Just four lines of code at the very end of the script, immediately prior to return;. If this answer was helpful, you might consider "accepting" it. – Tedinoz Aug 31 at 7:52
  • Answer accepted. Got the general idea, just still not sure about Logger.log functions but will look into them. Always good to learn. Thank you. – SystemWorks Aug 31 at 8:50
  • You were right - deleting the rows does reduce the total number of rows on the sheet. – Tedinoz Aug 31 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.