I recently found some code someone posted on their website which moves items back and forth between sheets depending on if one of the columns in the row is checked or not. I just changed a couple of things, like the reference to the checked column and the number of columns.

I don’t know much about writing this appscript code. I tried burying my head in it, but I’m lost. I understand the issue is that it’s trying to grab a row that doesn’t exist. Or at least that is what I think the issue is. However, sometimes when I add the checkmark to a row that’s in the middle, it moves the row and the row touching it (I don’t remember if it’s the one above or below). Sometimes the row completely disappears and doesn’t move to the other sheet. Sometimes it moves to the other sheet, but has no checkmark in it, when the items in that sheet without a checkmark should be moved back to the main sheet. It’s weird.

Below are the errors I’m getting and below that is the code. Any help would be greatly appreciated.

appscript errors

function onEdit() {

  var mainSheet = "To Do";
  var sheetToMoveTheRow = "Completed";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = SpreadsheetApp.getActiveRange();

  if(sheet.getName() == "To Do" && range.getColumn() == 1 && range.getValue() == true) {
    var row = range.getRow();
    var numColumns = sheet.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(row, 1, 1, numColumns).moveTo(target);
    sheet.deleteRow(row);}
  
  else if(sheet.getName() == "Completed" && range.getColumn() == 1 && range.getValue() == false) {
    var row = range.getRow();
    var numColumns = sheet.getLastColumn();
    var targetSheet = ss.getSheetByName("To Do");
    const aVals = targetSheet.getRange(1,1,targetSheet.getLastRow(),1).getValues();
    const targetRow = aVals.filter(String).length + 1;
    var target = targetSheet.getRange(targetRow , 1);
    sheet.getRange(row, 1, 1, numColumns).moveTo(target);
    sheet.deleteRow(row);}
    
}

I tried changing some of the variables inside the code. Sorry, I don’t know the proper terminology. I changed the values in a couple different getRange calls, but had no luck.