Move sheet rows on based on their value in a given column

Question

I have this little script that works fine at the moment with the simple "on edit" trigger, but I would like to run it from the Menu instead, so you can move the rows in batches rather one at a time. It seems that "on edit" trigger functions takes a few seconds to update, and you can loose your place in the list when it reshuffles.

My goal:

1. Data collected from a form populates Sheet 1
2. User changes row 8 (status) to either C or A
3. You select menu option "X" and the script moves the rows to the relevant sheet
   • "C" --> Sheet 2
   • "A" --> Sheet 3

While I'm very new to Apps Script, I can see why it doesn't work - I just don't know how to change it! The current script is looking at the rows that have been edited, but I would like it to effectively scan the sheet for any that have the status instead.

Original Code:

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var Colno = 8 //Column to be checked

  if (s.getName() == "Sheet1" && r.getColumn() == Colno && r.getValue() == "C") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Sheet2");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  } else if (s.getName() == "Sheet1" && r.getColumn() == Colno && r.getValue() == "A") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Sheet3");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Answer 1

You need to separate actions of the program

• The main action
• The onEdit trigger
• The menu action

Let's look at the main action

function mover(s, r) {
    var ss = s.getParent();
    if (s.getName() == "Sheet1" && r.getColumn() == Colno && r.getValue() == "C") {
        SpreadsheetApp.getActiveSpreadsheet().toast('if');
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        var targetSheet = ss.getSheetByName("Sheet2");
        var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
        s.getRange(row, 1, 1, numColumns).moveTo(target);
        s.deleteRow(row);
    } else if (s.getName() == "Sheet1" && r.getColumn() == Colno && r.getValue() == "A") {
        SpreadsheetApp.getActiveSpreadsheet().toast('elseif');
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        var targetSheet = ss.getSheetByName("Sheet3");
        var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
        s.getRange(row, 1, 1, numColumns).moveTo(target);
        s.deleteRow(row);
    }
}

Look at the onEdit trigger.

I have to rename it.

function onEditTrg(event) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = event.source.getActiveSheet();
    var r = event.source.getActiveRange();
    mover(s, r);
}

The menu action

function menuCall() {
    var s = SpreadsheetApp.getActiveSheet();
    var r = SpreadsheetApp.getActiveRange();
    mover(s, r);
}

Additionals

As a complement, I suggest using a switch of onEdit trigger

function onOpen() {
    SpreadsheetApp.getUi().createMenu('Mover')
        .addItem('Move current', 'menuCall')
        .addItem('Switch onEdit()', 'switchOnEdit')
        .addToUi();
}

function switchOnEdit() {
    var isExist = false;
    var tgrs = ScriptApp.getUserTriggers(SpreadsheetApp.getActiveSpreadsheet());
    for (var i = 0; i < tgrs.length; i++) {
        if (tgrs[i].getHandlerFunction() == 'onEditTrg' && tgrs[i].getEventType() == ScriptApp.EventType.ON_EDIT) {
            isExist = true;
            ScriptApp.deleteTrigger(tgrs[i]);
        }
    }
    if (isExist) {
        SpreadsheetApp.getActiveSpreadsheet().toast('onEdit() is off', 'Switcher onEdit()')
    } else {
        ScriptApp.newTrigger('onEditTrg')
            .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
            .onEdit()
            .create();
        SpreadsheetApp.getActiveSpreadsheet().toast('onEdit() is on', 'Switcher onEdit()')
    }
}

Be aware,

Colno should be a global variable.

The menu view

It works fine for me. Full code here.