根据工作表行在给定列中的值来移动工作表行 [英] Move sheet rows on based on their value in a given column

问题描述

我有一个小脚本，现在可以通过简单的"on edit"触发器正常运行，但是我想从菜单运行它，因此您可以一次而不是一次地批量移动行.似乎编辑时"触发功能需要花费几秒钟的时间进行更新，并且在重新组合后，您可以在列表中失去位置.

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.

我的目标:

1. 从表单收集的数据将填充到工作表1
2. 用户将第8行(状态)更改为C或A
3. 您选择菜单选项"X"，脚本将这些行移至相关的工作表
   • "C"->表格2
   • "A"->表格3

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

虽然我是Apps脚本的新手，但我可以看到为什么它不起作用-我只是不知道如何更改它！当前脚本正在查看已编辑的行，但我希望它可以有效地扫描工作表以查找具有状态的任何行.

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.

原始代码:

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);
  }
}

推荐答案

您需要将程序的操作分开

You need to separate actions of the program

• 主要动作
• onEdit触发器
• 菜单操作

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);
    }
}

看一下onEdit触发器.

我必须重命名.

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);
}

菜单操作

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

附加项

作为补充，我建议使用onEdit触发器开关

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()')
    }
}

注意，

Colno应该是全局变量.

Be aware,

Colno should be a global variable.

对我来说很好.完整代码此处.

It works fine for me. Full code here.

这篇关于根据工作表行在给定列中的值来移动工作表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！