Moving one row to another based on value

Question

I am trying to move data from one Google Sheet tab to another based on a value of one cell (a trigger cell). The "trigger" cell is the number of days until the due date of the specific task. That value is found using a simple code to count the difference in days.

I am having some issues getting the code to run. I have another sheet that does a similiar-ish thing and using the same code and it seems to work fine. Could anyone please give a fresh pair of eyes to see what stupid thing I have done now? I have tried to rewrite it and still have the same problem.

I have rewrote the code, copied and pasted from the internet, read line by line, I just can't seem to find the issue.

function moveToDue() {

  var UpToDate = "Up To Date";
  var Due = "Due";
  var triggerDue = "21";
  var triggerCol = 26;

  var url = "https://docs.google.com/spreadsheets/d/blahblahblahblah";
  var ss = SpreadsheetApp.openByUrl(url);
  var sheet = ss.getSheetByName(UpToDate);
  var range = sheet.getActiveCell();

//Move to Due

  if (sheet.getName() == UpToDate && range.getColumn() == triggerCol && range.getValue() == triggerDue) {

    var targetSheet = ss.getSheetByName(Due);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
      }
}

UpToData & Due are the different tab titles.

triggerDue is the number of days before the due date it needs to be triggered to move (in this case 21).

triggerCol is the column the trigger value is in.

Copy of sheet: https://docs.google.com/spreadsheets/d/1-shzMIIlj247YFWY4Cc1bh6gW895lQWsjvc5gTi-fto/edit?usp=sharing

Answer 1

Answer:

There are a few code issues including incorrect usage of .getActiveCell() and some if and for logic.

Code fixes:

There are also a few things in your code that you could clean up - you don't need to make a check if (sheet.getName() == UpToDate) as you set this two lines before on var sheet = ss.getSheetByName(UpToDate); without changing anything.

I've re-written a lot of the function to make it run more efficiently. Also, .deleteRow() seems to not always work if run alongside moveTo() in a loop, so I've added a function to clean up rows afterwards.

Code:

function moveToDue() {
  var triggerDue = 21;
  var triggerCol = 26;
  var url = "https://docs.google.com/spreadsheets/d/blahblahblahblah";
  var ss = SpreadsheetApp.openByUrl(url);
  var upToDate = ss.getSheetByName("Up To Date");
  var targetSheet = ss.getSheetByName("Due");
  var daysUntilEnd = upToDate.getRange(3, 26, upToDate.getLastRow() - 2).getValues();

  for (var i = 0; i < (upToDate.getLastRow() - 2); i++) {
    if (daysUntilEnd[i][0] <= triggerDue) {
      var targetLocation = targetSheet.getRange(targetSheet.getDataRange().getLastRow() + 1, 1, 1, 26);
      var uTDRange = upToDate.getRange(i + 3, 1, 1, 26);

      uTDRange.moveTo(targetLocation);
    }    
  }
  deleteEmptyRows(upToDate);
}

function deleteEmptyRows(utds) {
  for (var i = 0; i < utds.getLastRow(); i++) {
    if (utds.getRange('A' + (i + 1)).getValue() == "") {
      utds.deleteRow(i + 1);
    }
  }
}

References:

• Google Apps Script Class Sheet
  • Method .getActiveCell()
  • Method .getRange(a1Notation)