谷歌电子表格脚本onEdit

Question

我有一个非常简单的问题。

我发现这个脚本非常完美，而且我不懂编程。此脚本在名为"需要的"的工作表内的特定列中搜索任何日期值，当它检测到该值时，它会将该行移动到另一个名为"获取的"的工作表中。我只需要对其他源工作表做同样的操作。例如：我有4张表，分别为Need1、Need2、Need3和Need4。所有这些都必须将输入日期的行移动到同一个Acquired工作表中。我希望有人能帮助我。

function onEdit() {
  // moves a row from a sheet to another sheet when a date is entered in a column
  // adjust the following variables to fit your needs
  // version 1.0, written by --Hyde, 24 September 2014
  // see https://productforums.google.com/d/topic/docs/wtzlc_ludTw/discussion
  var sheetToWatch = "Needed";
  var columnNumberToWatch = /* column G */ 4; // column A = 1, B = 2, etc.
  var sheetToMoveTheRowTo = "Acquired";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  if ( sheet.getName() == sheetToWatch && cell.getColumn() == columnNumberToWatch && cell.getValue() instanceof Date) {
    var targetSheet = ss.getSheetByName(sheetToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(cell.getRow());
  }
}

Answer 1

简短回答：

function onEdit() {
  var sheetsToWatch = ["Needed1", "Needed2", "Needed3", "Needed4"];
  for (var i = 0; i < sheetsToWatch.length; i++) {
    var sheetToWatch = sheetsToWatch[i]; 
    // all good code goes here
  }     
}

整个代码是：

function onEdit() {
  // moves a row from a sheet to another sheet when a date is entered in a column
  // adjust the following variables to fit your needs
  // version 1.0, written by --Hyde, 24 September 2014
  // see https://productforums.google.com/d/topic/docs/wtzlc_ludTw/discussion
  var sheetsToWatch = ["Needed1", "Needed2", "Needed3", "Needed4"];
  for (var i = 0; i < sheetsToWatch.length; i++) {
    var sheetToWatch = sheetsToWatch[i];
    var columnNumberToWatch = /* column G */ 4; // column A = 1, B = 2, etc.
    var sheetToMoveTheRowTo = "Acquired";
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = SpreadsheetApp.getActiveSheet();
    var cell = sheet.getActiveCell();
    if ( sheet.getName() == sheetToWatch && cell.getColumn() == columnNumberToWatch && cell.getValue() instanceof Date) {
      var targetSheet = ss.getSheetByName(sheetToMoveTheRowTo);
      var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
      sheet.deleteRow(cell.getRow());
    }
  }
}

您可以将新的工作表名称添加到数组中：

var sheetsToWatch = ["Needed1", "Needed2", "Needed3", "Needed4", "New Name"];