Google表格中基于公式的单元格更改未触发onEdit脚本 [英] Formula-based cell changes in Google Sheets is not firing onEdit script

问题描述

我对Google脚本编写是完全陌生的，但是我在这里使用了各种帖子来拼凑出我需要的东西:当某些列发生变化时，这些东西会在行中添加时间戳.这是我当前正在使用的:

I'm completely new to Google script writing, but I've used various posts here to piece together what I need: something that will add a time stamp to a row when a certain column changes. Here's what I'm currently using:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "test" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 16 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date());
    }
  }
}

当我手动更改数据时，这非常理想；但是，脚本正在监视的列从另一张表中提取数据，因此无法触发触发器/脚本.我该如何解决这个问题，以便带有公式的单元格(引用其他工作表)仍会触发我的脚本?

This works perfectly when I manually change the data; however, the column that the script is monitoring pulls data from another sheet and this fails to fire the trigger/script. How can I get around this so that cells with formulas (that reference other sheets) will still fire my script?

任何帮助将不胜感激.谢谢！

Any help is greatly appreciated. Thanks!

推荐答案

onEdit触发器仅在实际用户编辑电子表格时有效.取决于您的用例，但是您可以使用时间驱动触发并将其设置为重复出现，并使用功能监视列中的更改，这是一个示例:

The onEdit trigger works only when an actual user edits the spreadsheet. Depends of your use case, but you can be use a Time-driven trigger and set it in a recurring interval and with a function monitor the column for changes, here's an example:

function monitorColumn() {
  // Add the trigger from the Resources menu in the Script Editor
  // Script Editor > Resources > Current oroject's triggers > Add trigger
  // [monitorColumn] - [Time-driven] - [Hour timer] - [Every hour]

  var s = SpreadsheetApp.getActiveSpreadsheet();
  var ss = s.getSheetByName("test"); // Get the sheet by name

  // Get the values of the columns P & Q with getRange(row, column, numRows, numColumns)
  var columnValues = ss.getRange(1, 16, ss.getLastRow(), 2).getValues();

  // Loop through the values
  for (var i = 0; i < columnValues.length; i++) {

    // If cell in column P is empty and cell in column Q is not empty set todays date
    if(columnValues[i][0] != "" && columnValues[i][1] == ""){

      // While a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].
      ss.getRange(i+1,17).setValue(new Date());
    }
  }
}

这篇关于Google表格中基于公式的单元格更改未触发onEdit脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！