excel - 如何在表格上搜索已编辑的行以比较值并相应地更改不同列上的时间戳？

Question

我一直在寻找一种方法来比较同一行中不同列的值，以了解我应该更新哪个单元格。
电子表格是一个简单的库存管理模型(它非常简单，我一直在手动操作)，但我想要一种“更快”(*)自动更新每个项目的金额和时间戳(它们是两种:一种用于增加库存，一种用于退出)。
到目前为止的障碍是:

onEdit()功能不适用于宏之类的自动更改，因此不在讨论范围内；

我需要扫描整个电子表格以找到 D 列上的每个填充单元格，其中包含我在 C 列中添加或减去的值；
- 为此，我已经设置过滤列“从 Z 到 A”以获取所有带有值的单元格，但更改的项目数量可能会有所不同，因此我无法设置具有固定行数的搜索。

由于我的工作表有超过 90 个条目(可能会增加)，每个条目至少 6 列，因此 for循环 if语句花费的时间太长，(*)但执行时间并不是目前主要关注的问题。

代码如下，我将附上我正在使用的工作表的图片。

/** @OnlyCurrentDoc */

function geral() {
  filtro();
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('G2').activate();
  spreadsheet.getCurrentCell().setFormula('=C2+D2');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('G2:G92'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getRange('C2').activate();
  spreadsheet.getRange('G2:G').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('G:G').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
 
  //adds the input OR output timestamp depending on the value in D column
  //!!WORK IN PROGRESS!! --> here's where it gets tricky, and that's what I got so far (which doesn't work)

/*
  for (var i = 2; i < 100; i++) {
    spreadsheet.getRange('J2').setValue("TESTE");
    var cell1 = spreadsheet.getRange('????').getValue(); //from this point on, I don't know how to proceed
    var cell2 = spreadsheet.getRange('????').getValue();
    spreadsheet.getRange('J2').setValue("TESTE2");
    if(cell1 > cell2){
      spreadsheet.getRange('????').activate();
      spreadsheet.getActiveCell().setValue(new Date());
    }
    else if(cell1 < cell2){
      spreadsheet.getRange('????').activate();
      spreadsheet.getActiveCell().setValue(new Date());
    }
  }
*/
  spreadsheet.getRange('D2:D').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  
};


function filtro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('D:D').activate();
  spreadsheet.getActiveSheet().sort(4, false);
};

编辑 :在@IrvingJayG. 发表评论后，我发现了一些错误和不必要的额外步骤，所以我没有做所有的复制粘贴删除舞蹈然后比较结果，而是使用下面的伪代码:

//Ci's value pre-exists in the sheet, where i is the row index
//manually input Di value
//set formula for Gi = Ci+Di
//and then compare either Ci and Gi, or Di and 0 
    if(Di > 0){
    //the following steps can be defined as a new function for each case, (e.g. updateIn() and updateOut())
        copy Gi to Ci;
        update Ei with new Date(); 
        delete Gi and Di;
    }
    else if(Di < 0){
        copy Gi to Ci;
        update Fi with new Date();
        delete Gi and Di;
    }  

不幸的是，它仍然没有解决我的问题，只是大大简化了代码。
Sheet example

Answer 1

推荐:
我创建了一个示例表(基于您附加的示例表)，其中包含 6 行数据和列 D 上的 4 个随机示例单元格值。然后，我在下面创建了一个示例脚本，您可以在其中使用引用:
注意:此脚本将扫描工作表上包含数据的每一行(例如，如果您有 30 行数据，它将逐行扫描每一行，直到到达第 30 行)并且一旦您有一堆数据，它可能会减慢的数据。这就是问题所在，因为这是预期的行为
sample 表:

脚本:

  function onOpen() { //[OPTIONAL] Created a custom menu "Timestamp" on your Spreadsheet, where you can run the script
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('Timestamp')
          .addItem('Automate Timestamp', 'mainFunction')
          .addToUi();
    }

  function mainFunction() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('D:D').activate();
      spreadsheet.getActiveSheet().sort(4, false);
      automateSheetCheck();
    }

  function automateSheetCheck(){
      var d = new Date();
      var formattedDate = Utilities.formatDate(d, "GMT", "MM/dd/yyyy HH:mm:ss");
      var spreadsheet = SpreadsheetApp.getActive();
      var currentRow = spreadsheet.getDataRange().getLastRow(); //Get the last row with value on your sheet data as a whole to only scan rows with values
      for(var x =2; x<=currentRow; x++){ //Loop starts at row 2
        if(spreadsheet.getRange("D"+x).getValue() == ""){ //Checks if D (row# or x) value is null
          Logger.log("Cell D"+x+" is empty"); //Logs the result for review
        }else{
          var res = spreadsheet.getRange("C"+x).getValue() + spreadsheet.getRange("D"+x).getValue(); //SUM of C & D values
          if(spreadsheet.getRange("D"+x).getValue() > 0){ // If D value is greater than 0, E cell is updated with new timestamp and then C value is replaced with res
            Logger.log("Updated Timestamp on cell E"+x + " because D"+x+ " with value of "+ spreadsheet.getRange("D"+x).getValue() +" is greater than 0"); //Logs the result for review
            spreadsheet.getRange("E"+x).setValue(formattedDate);
            spreadsheet.getRange("C"+x).setValue(res); //Replace C value with "res"
            spreadsheet.getRange("D"+x).setValue(""); //remove D value
          }else{ // If D value is less than 0, F cell is updated with a new timestamp
            Logger.log("Updated Timestamp on cell F"+x + " because D"+x+ " with value of "+ spreadsheet.getRange("D"+x).getValue() +" is less than 0"); //Logs the result for review
            spreadsheet.getRange("F"+x).setValue(formattedDate);
            spreadsheet.getRange("C"+x).setValue(res); //Replace C value with "res"
            spreadsheet.getRange("D"+x).setValue(""); //remove D value
          }
        }
      }
    }

结果:
运行脚本后，将是示例表上的结果:

这是执行日志，您可以在其中查看运行代码后发生的情况: