Script Not Working to Move Row into new Sheet

Question

I would like a script to move a row from one sheet to another (within the same workshop) based on a TIME TRIGGER rather than onEdit (because onEdit doesn't work with IMPORTRANGE).

I found a script that worked brilliantly on edit:

function onEdit(event) {
  // assumes source data in sheet named Active
  // target sheet of move to named Completed
  // getColumn with condition is currently set to column 12 or L
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Active" && r.getColumn() == 12 && r.getValue() == "8") {
    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).moveTo(target);
    s.deleteRow(row);

  }
}

and a few that could work on time triggers and then combined/manipulated them to get this:

function PushToCompleted() {
  // assumes source data in sheet named TEST
  // target sheet of move to named Test2
  // getColumn with condition is currently set to column 12 or L
  var sss = SpreadsheetApp.openById('19WFdvLlWE-oI0OT8c6uBmgu2jSvdX0upM9nobscn5G4');
  var ss = sss.getSheetByName("TEST");
  var r = ss.getDataRange();;

  if(ss.getName() == "TEST" && r.getColumn() == 12 && r.getValue() == "8") {
    var row = r.getRow();
    var numColumns = ss.getLastColumn();
    var targetSheet = sss.getSheetByName("Test2");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);

  }
}

I then went in to set up the time trigger to every minute. But it's not working- not on manual run nor with the every minute time trigger. I'm VERY new with scripts, so would imagine there's something wrong within the script.

Any ideas?

Answer 1

Answer:

Your conditional statement is always evaluating to false, and so the code within the if block is never being executed.

More Information:

As per the Apps Script documentation on the Sheet.getDataRange() method:

Returns a Range corresponding to the dimensions in which data is present.

This is functionally equivalent to creating a Range bounded by A1 and (Range.getLastColumn(), Range.getLastRow()).

This means that when you call ss.getDataRange(), you are getting the entire range in which data is present, starting at A1. If we go another step further and check out the documentation for Range.getColumn():

Returns the starting column position for this range.

That means that for a Range object, obtained by using the Sheet.getDataRange() method, getColumn() will always return 1.

As well as this, as you need to do this for each row and check the value in column L for each, you need to put your check inside a loop.

Fix:

From your comment:

If column #12 (L) has the number 8 in it, then i want the entire row to me moved to sheet Test2.

Instead of simply getting the data range and directly comparing the column value in your conditional, you need to check the whole L column for cells that contain the value 8 and copy those rows:

function pushToCompleted() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var originSheet = ss.getSheetByName("TEST");
  var targetSheet = ss.getSheetByName("Test2");
  var range = originSheet.getDataRange()
  var values = range.getValues();
  
  var count = 1;
  values.forEach(function(row) {
    if (row[11] == "8") {
      targetSheet.appendRow(originSheet.getRange("A" + count + ":" + count).getValues()[0])
      originSheet.deleteRow(count);
      count--;
    }
    count++;    
  });
}

Run down of this code:

• Set your origin and target sheets to variables
• Get the whole data range of your origin sheet
• Set a counter for the current row you're looking at
• Loop through each row of your data range and check column L for the value 8:
  • If the value of the cell in column L is 8, then append the whole row to Sheet Test2
  • Delete the row from the origin sheet
  • Also readjust the counter as now your origin sheet now has one less row

I hope this is helpful to you!

References:

• Sheet.getDataRange()
• Class Range | Apps Script | Google Developers
• Range.getColumn()
• Sheet.appendRow(rowContents)

Answer 2

Try this:

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()=="Active" && e.range.columnStart==12 && e.value=="8") {
    var targetSheet= e.source.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).moveTo(target);
    sh.deleteRow(e.range.rowStart);
  }
}

You can't run this script from the script editor unless you supply the event object.

onedit event obj

onEdit(e) runs when a user changes a value in a spreadsheet.