Moving rows to another sheet google sheets

Question

Hi guy's i'm trying to create a to-do list at google sheets and i'm using a template from google

and i found a script at youtube to clear the done tasks and it's working.

But, i need to do one more thing, i need to move the completed tasks to another sheet and i tried so many scripts and i can't get it to work.

My main sheet is "Tarefas" and the completed sheet is "Finalizadas"

my sheet is: https://docs.google.com/spreadsheets/d/1DZgXfHoQ3hwbBCPpLAOoQ8Jd0u5OMeQpX_OWWlgpa2I/edit?usp=sharing

and the code to clear the rows is:

    function ClearRecords()
{
  // DECLARE ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // DECLARE SHEET
  var todoSheet = ss.getSheetByName("Tarefas");
  
  // LAST ROW ON TO DO SHEET
  var lastRowToDo = todoSheet.getLastRow();
  
  // GET LAST TASK
  for(var x = 4; x <= lastRowToDo; x++)
  {
    if(todoSheet.getRange(x,3).isBlank() == true)
    {
       var lastTaskRow = x - 1;
      
    }          
  }

  for(var i = 4; i <= lastTaskRow; i++)
  {
    if(todoSheet.getRange(i, 1).getValue() == true)
    {
       todoSheet.getRange("B" + i + ":C" + i).setValues([["",""]]);
       todoSheet.getRange(i, 1).setValue(false);

    }       
  }
  
  SpreadsheetApp.flush();
  var OpenRows = [];
  
  for(var i = 4; i <= lastTaskRow; i++)
  {
    if(todoSheet.getRange(i,3).isBlank() == true)
    {
       OpenRows.push(i); 
    }
    else
    {
       if(OpenRows.length > 0)
       {
         var nextRow = OpenRows.shift();
         var values = todoSheet.getRange("B" + i + ":C" + i).getValues();
         todoSheet.getRange("B" + nextRow  + ":C" + nextRow).setValues(values);
         todoSheet.getRange("B" + i + ":C" + i).setValues([["",""]]);
         OpenRows.push(i); 

       }  
    }
  }
  
}

Answer 1

There's a lot going on there in that script, but this can probably be done more easily by checking what rows have a checkbox checked, which indicates that a task is done. Then you move that row to the second sheet.

The last function in the spreadsheet you shared (teste()) is closer to this idea than the one you pasted here, so I made some changes to it:

function moveDoneTasks() {
  // DECLARE ACTIVE SPREADSHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // DECLARE SHEETS
  var todoSheet = ss.getSheetByName("Tarefas");
  var doneSheet = ss.getSheetByName("Finalizadas");

  // Not used
  //  var app = SpreadsheetApp;
  //  var sheetNameToWatch = ss.getSheetByName("Tarefas")
  //  var columnNumberToWatch = 8;
  //  var valueToWatch = "DONE" ;
  //  var sheetNameToMoveTheRowTo = "Finalizadas";
  //  var range = ss.getActiveCell();
  
  // LAST ROW ON TO DO SHEET
  // var lastRowToDo = todoSheet.getLastRow(); // This would get all rows of the sheet, not just the non-empty ones
  // Get non-empty rows in C column (Tarefa) - Based on https://stackoverflow.com/a/17637159/3751868
  var lastRowToDo = ss.getRange("C1:C").getValues().filter(String).length+1;  
  
  // MOVE TASKS WITH CHECKBOX CHECKED (✓)
  for(var x = 4; x <= lastRowToDo; x++){
    var rowCheckboxCell = todoSheet.getRange(x, 1, 1, 1);
    if(rowCheckboxCell.isChecked()) { // See if the checkbox in the first column of the row is checked
      // var targetSheet = ss.getSheetByName("Finalizadas"); // It is not necessary to do this inside the loop, since it doesn't change
      var targetRange = doneSheet.getRange(doneSheet.getLastRow() + 1, 1);
      // ss.getRange(range.getRow(), 1, 1, ss.getLastColumn()).moveTo(targetRange); // This is getting the range in the spreadsheet, not the Finalizadas sheet
      todoSheet.getRange(x, 2, 1, todoSheet.getLastColumn()-1).moveTo(targetRange);      
      // sheet.deleteRow(range.getRow()); // It is not necessary to delete the row because the moveTo() function already removes it from the original sheet
      rowCheckboxCell.uncheck(); // Uncheck moved tasks
    }
  } 
}

To make it easier to use, I'd suggest adding a menu to the spreadsheet to call the function:

function onOpen(){
 SpreadsheetApp.getUi()
 .createMenu('Avançado')
 .addItem('Mover tarefas finalizadas', 'moveDoneTasks')
 .addToUi();
}