google-apps-script - 获取和比较非并发字段的更有效方法 Google 表格（Google 应用脚本）

Question

我正在研究一个本质上是更改日志的解决方案，我正在使用编辑事件触发器来捕获正在更改的数据。之后，我需要创建一个单独的工作表，该工作表基本上为每个更改了数据的人显示一个单独的行，但呈现的数据是最新数据。因此，如果工作表上已经存在唯一 ID，则首先将其删除，然后将新数据移动到工作表中。

我拥有的脚本正在运行，但由于它效率不高，因此用户可以通过输入来“击败”程序运行时间。

我能想到的唯一解决方案是将值读入数组，但由于我是编程世界和应用程序脚本的新手，所以我很肯定有一个我还不知道的解决方案。

接受任何想法！

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sName = e.source.getActiveSheet().getSheetName(); 
  var sNameActive = SpreadsheetApp.getActiveSheet();

  if(sName == "D_Comp") { // enter the if statement only if changes we made on a sheet not named logging
    var value;
    var mA1 = e.range.getA1Notation().split(":")[0]; //range of change in A1 notation. 
    var col = e.range.getColumn(); // column number of where change occured
    var row = e.range.getRow(); // row number of where the change occured
    var time = new Date().toTimeString();   // time the change was made
    var user = e.user; 
    var oldvalue; // defining the variable that will store the old value within the sheet 
    oldvalue = e.oldValue; // old value that was in the cell before hand 
    var aCell = e.source.getRange(mA1);
    value = aCell.getValue(); 
    var colhdr = ss.getActiveSheet().getRange(1, col).getValue(); // getting the header value
    var Emplid = ss.getActiveSheet().getRange(row, 1).getValue(); // getting the emplid
    var GUID = ss.getActiveSheet().getRange(row, 2).getValue(); // getting the GUID
    var effdate= ss.getActiveSheet().getRange(row, 28).getValue(); // getting the change date
    var Salary = ss.getActiveSheet().getRange(row, 44).getValue(); // get base salary value for workers row
    var Location = ss.getActiveSheet().getRange(row, 36).getValue(); // get location value for workers row
    var Department = ss.getActiveSheet().getRange(row, 16).getValue(); // get department numerical value for workes row
    var DepName = ss.getActiveSheet().getRange(row, 17).getValue(); // get departmner name for workers row
    var SOB = ss.getActiveSheet().getRange(row, 48).getValue(); // get phone stipend value for workers row
    var Stp_phone = ss.getActiveSheet().getRange(row, 50).getValue(); // get phone stipend value for workers row
    var Stp_home = ss.getActiveSheet().getRange(row, 51).getValue(); // get home stipend for workers home
    var Chng_rsn = ss.getActiveSheet().getRange(row, 29).getValue();


    if(oldvalue == undefined) { // we are replacing any underfined old value with a blank
      oldvalue = '';
    }


    var data = [GUID, time, oldvalue, value, colhdr, user]; // output into table on Logging sheet 
    e.source.getSheetByName("Logging").appendRow(data);

    var comp_sheet = ss.getSheetByName("Comp_Changes") //setting the variable of comp_sheet to the comp_Change_full_rows
    var data2 = [Emplid, GUID, Chng_rsn, effdate, Salary, Location, Department, DepName, SOB, Stp_phone, Stp_home]; // populating the array data2
    var lastrow = comp_sheet.getLastRow();


    //loop to check if the emplid already exists on the comp final change sheet or not
    for (var x = 1; x <= lastrow; x++){
      var eval_emplid = comp_sheet.getRange(x, 1).getValue(); 

      //if a match is found, delete the old row
      if (eval_emplid == Emplid) {
        comp_sheet.deleteRow(x);
      }
    }

    comp_sheet.appendRow(data2); // appending the comp full rows sheet with the data in data2
  }
}

标签： google-apps-scriptgoogle-sheetstriggers