google-apps-script - 如何更改代码以应用于命名工作表选项卡，而不是 Google 电子表格中的第一个工作表选项卡

Question

我有一段很棒的代码，当在另一列中输入值“已分配”时，它会将一行数据复制到电子表格中的某个工作表中。因此，用户将选择它必须移动到的工作表，然后将状态更改为已分配并移出。它工作得很好，但现在我想修改它以在该电子表格中的不同工作表上工作。如何更改代码以在我指定的不同工作表上工作，而不仅仅是第一个工作表？我在下面粘贴了适用于第一个选项卡的工作代码。我想制作另一个脚本以应用于其他选项卡以使用不同的值并移动到另一个。我对此很陌生，因此将不胜感激。

我知道我需要更新 ss.getActiveSheet() 以包含 getSheetByName(); 但我不确定如何编辑代码的结尾位以使其正常工作。

 function onEdit(e) {
    var ss = e.source;
    var s = ss.getActiveSheet();
    var r = e.range;

   // to let you modify where the action and move columns are in the form 
   responses sheet
    var actionCol = 19;
    var nameCol = 18;

    // Get the row and column of the active cell.
    var rowIndex = r.getRowIndex();
    var colIndex = r.getColumnIndex();

    // Get the number of columns in the active sheet.
    // -1 to drop our action/status column
    var colNumber = s.getLastColumn()-1;

    // if our action/status col is changed to ok do stuff
    if (e.value == "Assigned" && colIndex == actionCol) {
    // get our target sheet name - in this example we are using the priority 
    column
    var targetSheet = s.getRange(rowIndex, nameCol).getValue();
    // if the sheet exists do more stuff
    if (ss.getSheetByName(targetSheet)) { 
      // set our target sheet and target range
      var targetSheet = ss.getSheetByName(targetSheet);
      var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, 
colNumber);
      // get our source range/row
      var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
      // new sheets says: 'Cannot cut from form data. Use copy instead.' 
      sourceRange.copyTo(targetRange);
      // ..but we can still delete the row after
      s.deleteRow(rowIndex);
      // or you might want to keep but note move e.g. r.setValue("moved");
     }
     }
     }

标签： google-apps-script

Answer 1

我相信你可以做这样的事情：

function onEdit(e) {
  var ss = e.source;
  var r = e.range;
  var s = r.getSheet().getName(); // Get the sheet on which the change was made
  var sheetname = s.getName();

  // to let you modify where the action and move columns are in the form responses sheet

  var actionCol, nameCol, valueToCheck;

  switch (sheetname) {
    case "xxxx":
      actionCol = 19;
      nameCol = 18;
      valueToCheck = "Assigned";
      break;
    case "yyyy": 
      actionCol = 19;
      nameCol = 18;
      valueToCheck = "Assigned";
      break;
    default:
      return ; // if none of the names aboves, no instructions, so quit withoud doing anything
  }

  // Get the row and column of the active cell.
  var rowIndex = r.getRowIndex();
  var colIndex = r.getColumnIndex();

  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
  var colNumber = s.getLastColumn()-1;

  // if our action/status col is changed to ok do stuff
  if (e.value == valueToCheck && colIndex == actionCol) {
    // get our target sheet name - in this example we are using the priority column
    var targetSheet = s.getRange(rowIndex, nameCol).getValue();
    // if the sheet exists do more stuff
    if (ss.getSheetByName(targetSheet)) { 
      // set our target sheet and target range
      var targetSheet = ss.getSheetByName(targetSheet);
      var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
      // get our source range/row
      var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
      // new sheets says: 'Cannot cut from form data. Use copy instead.' 
      sourceRange.copyTo(targetRange);
      // ..but we can still delete the row after
      s.deleteRow(rowIndex);
      // or you might want to keep but note move e.g. r.setValue("moved");
    }
  }
}

在switch操作中，只需根据您的需要编辑值，并为您需要考虑的每张纸重复case部分（直到，包括命令）。break;