Google-sheets – Need help with Google Sheets moving rows and setting formatting upon move completion for Google Sheets Script

googlegoogle sheetsgoogle-apps-script

I have a script which I use to copy rows with checked checkboxes to a "Copmpleted sheet". The script is set to run on a timer and it works without any issues. The script and description can be seen here;

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

I have since added another script (a modified version of the script above) to copy daily tasks from Task sheet to ToDo list. Almost everything is working without issues. The problem is that the copied data looses checkbox formatting. I have tried to solve the issue but was not able to. Only a part of "pushed" over data has checkboxes set. If I run the script it will copy over the data but will not set the checkboxes. If I run the script again it will set the checkboxes on data copied over earlier but not the currently copied data. I am guessing the issue is either with "datarange" or loop script ending too soon but I just cannot figure it out. The script is below.

Row 1 has a header in it.

I would also like to add a condition saying (see below) but I am not sure of syntax or where to put it

if ( values[i][0] !== "") AND ( values[i][1] == 1st of the month ) copy this row

else if

( values[i][0] !== "") copy this row

Trying to set up copying of daily task every day and only once a month tasks only once on the 1st of each month.

function CopyTasks() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("ToDoRepeatTasks");
  var targetSheet = ss.getSheetByName("ToDo"); 
  
  // get the last row and column of data on "ToDoRepeatTasks" and "ToDo"
  var sheetLR = sheet.getLastRow();
  var sheetLC = sheet.getLastColumn();  
  var targetLR = targetSheet.getLastRow();
  var ToDoLC = targetSheet.getLastColumn();
  var ToDoLR = targetSheet.getLastRow();
  
  // assume that there are headers in row 1
  var range = sheet.getRange(2,1,sheetLR-2+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-2+1;i++){
    var thisrow = i+2;
    if ( values[i][0] !== ""){
      // 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 rows to the targhet sheet
  var targetrange=targetSheet.getRange(targetLR + 1, 1,temp.length,sheetLC);
  targetrange.setValues(temp);

   // modification ------------------------------------------------------------------
  
var checkboxRange = targetSheet.getRange(2, 2, ToDoLR-1+1, 1);
  // Logger.log("DEBUG: checkbox range = "+checkboxRange.getA1Notation())
  var checkboxValues = checkboxRange.getValues();
  // Logger.log(checkboxValues); // DEBUG

  // change from the bottom up so that row numbers retain content
  for (var r=ToDoLR-1;r>=0;r--){
    // Logger.log("DEBUG: r = "+r+", row = "+(r+2)+", checkbox value  = "+checkboxValues[r]);
    if (checkboxValues[r] == "false"){
      // Logger.log("DEBUG: set checkboxes")
      checkboxRange.insertCheckboxes();
    }
   else
   {
     // Logger.log("DEBUG: don't do anything")
   }
 }
  }

Best Answer

Solved with the following script;

function CopyTasks() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("ToDoRepeatTasks");
  var targetSheet = ss.getSheetByName("ToDo"); 
  
  // get the last row and column of data on "ToDoRepeatTasks" and "ToDo"
  var sheetLR = sheet.getLastRow();
  var sheetLC = sheet.getLastColumn();  
  var targetLR = targetSheet.getLastRow();
  var ToDoLC = targetSheet.getLastColumn();
  var ToDoLR = targetSheet.getLastRow();
  var currentDate = new Date();
  var firstOfMonth = currentDate.getDate();
  
  // assume that there are headers in row 1
  var range = sheet.getRange(2,1,sheetLR-2+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-2+1;i++){
    var thisrow = i+2;
    if ( values[i][0] !== "" && values[i][4] !== "Monthly") {
      // 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 if ( values[i][0] !== "" && values[i][4] == "Monthly" && firstOfMonth == 1){
      // 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);    
    
    {
      //Logger.log("do nothing")
    }
 }

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



}
}

And have also added a separate script to address some of the issues mentioned;

function Test() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("ToDo");
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
var searchRange = sheet.getRange(2,1,lastRow - 1,lastColumn - 1);
var rule = SpreadsheetApp.newDataValidation().requireCheckbox();
var todaysDate = new Date();
var dynamicRow = ("=ROW()-1");
  
var Avals = sheet.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length;
var columnA = sheet.getRange(2,1,Alast).getValues();

var rangeValues = searchRange.getValues();
  for ( i = 0; i < lastColumn - 1; i++) {
    for ( j = 0; j < lastRow - 1; j++) {
      if(rangeValues[j][i] === false){
        sheet.getRange(j+2,i+1).setDataValidation(rule);
      }
  
                       
    };
  };
  
  for ( i = 0; i < lastColumn - 1; i++) {
    for ( j = 0; j < lastRow - 1; j++) {
      if(rangeValues[j][i] === "Daily"){
        sheet.getRange(j+2,i+1).setValue(todaysDate);
      }
  
                       
    };
  };
  
  for ( i = 0; i < lastColumn - 1; i++) {
    for ( j = 0; j < lastRow - 1; j++) {
      if(typeof columnA[j][i] === "number"){
        sheet.getRange(j+2,i+1).setValue(dynamicRow);
      }
  
                       
    };
  };
  
}

It works well and I noticed no issues while testing.

Related Topic