sorting - 如何优化 Google Sheet 的排序/时间戳脚本

Question

我对 Google Sheets 中的脚本还是很陌生，我想我在这里构建的东西不是很优化.. 但它可以工作（在大多数情况下）。

我的目标是有一个脚本（onEdit），它会自动在 R 列中添加时间戳（对于来自新截止日期/新的非截止日期的任务），并根据 G 列中设置的状态对任务进行排序（已完成，升级到柏林或请求的数据) 将我的电子表格添加到相应的选项卡中。

问题是脚本有时没有反应，所以任务没有移动，也没有添加时间戳。我假设，因为很多人同时触发脚本。

我目前在一个项目中有以下所述的脚本。我能做些什么来改进它并让它运行得更顺畅吗？

{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

//--------------------------------------------------------------------------------------------------------//

  //NEW DEADLINES -- START

    if(s.getName() == "New Deadlines" && r.getColumn() == 7 && r.getValue() == "Completed") 

    {
      var nextCell = r.offset(0, 11);
      nextCell.setValue(new Date());
      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);
    }

    if(s.getName() == "New Deadlines" && r.getColumn() == 7 && r.getValue() == "Escalation to Berlin") 

    {
      var nextCell = r.offset(0, 11);
      nextCell.setValue(new Date());
      var row = r.getRow();
      var numColumns = s.getLastColumn();
      var targetSheet = ss.getSheetByName("Escalation to Berlin");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      //s.deleteRow(row);
    }

    if(s.getName() == "New Deadlines" && r.getColumn() == 7 && r.getValue() == "Requested Data") 

    {
      var nextCell = r.offset(0, 11);
      nextCell.setValue(new Date());
      var row = r.getRow();
      var numColumns = s.getLastColumn();
      var targetSheet = ss.getSheetByName("Requested Data");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      //s.deleteRow(row);
    }

    //SORTING NEW DEADLINES END

  //NEW DEADLINES -- END

//--------------------------------------------------------------------------------------------------------//

  //NEW NON DEADLINES -- START

    if(s.getName() == "New None-Deadlines" && r.getColumn() == 7 && r.getValue() == "Completed") 

    {
      var nextCell = r.offset(0, 11);
      nextCell.setValue(new Date());
      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);
    }

    if(s.getName() == "New None-Deadlines" && r.getColumn() == 7 && r.getValue() == "Escalation to Berlin") 

    {
      var nextCell = r.offset(0, 11);
      nextCell.setValue(new Date());
      var row = r.getRow();
      var numColumns = s.getLastColumn();
      var targetSheet = ss.getSheetByName("Escalation to Berlin");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      //s.deleteRow(row);
    }

    if(s.getName() == "New None-Deadlines" && r.getColumn() == 7 && r.getValue() == "Requested Data") 

    {
      var nextCell = r.offset(0, 11);
      nextCell.setValue(new Date());
      var row = r.getRow();
      var numColumns = s.getLastColumn();
      var targetSheet = ss.getSheetByName("Requested Data");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      //s.deleteRow(row);
    }

  //NEW NON DEADLINES -- END

//--------------------------------------------------------------------------------------------------------//

  //SORTING COMPLETED -- START

    if(s.getName() == "Completed" && r.getColumn() == 7 && r.getValue() == "Escalation to Berlin") 

    {
      var row = r.getRow();
      var numColumns = s.getLastColumn();
      var targetSheet = ss.getSheetByName("Escalation to Berlin");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      //s.deleteRow(row);
    }

    if(s.getName() == "Completed" && r.getColumn() == 7 && r.getValue() == "Requested Data") 

    {
      var row = r.getRow();
      var numColumns = s.getLastColumn();
      var targetSheet = ss.getSheetByName("Requested Data");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      //s.deleteRow(row);
    }

  //SORTING COMPLETED -- END

//--------------------------------------------------------------------------------------------------------//

  //SORTING ESCALATION TO BERLIN -- START

    if(s.getName() == "Escalation to Berlin" && r.getColumn() == 7 && r.getValue() == "Completed") 

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

    if(s.getName() == "Escalation to Berlin" && r.getColumn() == 7 && r.getValue() == "Requested Data") 

    {
      var row = r.getRow();
      var numColumns = s.getLastColumn();
      var targetSheet = ss.getSheetByName("Requested Data");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      //s.deleteRow(row);
    }


  //SORTING ESCALATION TO BERLIN -- END

//--------------------------------------------------------------------------------------------------------//

  //SORTING Requested Data -- START

    if(s.getName() == "Requested Data" && r.getColumn() == 7 && r.getValue() == "Completed") 

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

    if(s.getName() == "Requested Data" && r.getColumn() == 7 && r.getValue() == "Escalation To Berlin") 

    {
      var row = r.getRow();
      var numColumns = s.getLastColumn();
      var targetSheet = ss.getSheetByName("Escalation To Berlin");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).moveTo(target);
      //s.deleteRow(row);
    }  

  //SORTING Requested Data *New -- END
  
//--------------------------------------------------------------------------------------------------------//

}

标签： sortinggoogle-apps-scriptgoogle-sheetstimestamp-with-timezone

Answer 1

我没有对此进行测试，但这样的事情应该可以工作：

function onEdit(e) {
  e.source.toast('entry');//debug
  const tshts = ["Completed", "Escalation to Berlin", "Requested Data"];
  const sh = e.range.getSheet();
  const idx = tshts.indexOf(e.value);
  if (sh.getName() == "New Deadlines" && e.range.columnStart == 7 && ~idx) {
    e.source.toast('flag1');//dubug
    e.range.offset(0, 11).setValue(new Date());
    const tsh = e.source.getSheetByName(tshts[idx]);
    const tgt = tsh.getRange(tsh.getLastRow() + 1, 1);
    sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(tgt);
  }
}