检测用户在谷歌电子表格中插入行或列并在脚本中做出反应 [英] Detect user inserting row or column in a google spreadsheet and reacting in a script

问题描述

在 Google Apps Script 中，基本工具之一是电子表格中的 onEdit 触发器，它使我们能够检测用户何时编辑单元格并对其做出反应.

In Google Apps Script, one of the basic tools is the onEdit trigger in a spreadsheet, which enables us to detect when a user edits a cell, and react to it.

当用户插入一行或一列时怎么样?有没有办法检测到?

How about when a user inserts a row or column ? Is there a way to detect that ?

这会触发 onEdit 吗?如果是这样，我想在 ScriptDb 中维护行数或列数，然后每次检查都可以，但时间会非常昂贵，因为 getMaxRows() 已经很慢了，而与 ScriptDb 联系就像嗯.

Would that trigger an onEdit ? If so, I guess maintaining in the ScriptDb a count of the number of rows or column, and then checking each time would do, but would be very time costly, since getMaxRows() is already pretty slow, and reaching out to ScriptDb is as well.

你怎么看?

推荐答案

有很多编辑动作不会触发onEdit()，这不是一个完整的列表，有更多报告的排除项:

There are a number of editing actions that do not trigger onEdit(), this isn't a comprehensive list, there are many more reported exclusions:

• 表单提交 问题 4568.
• 插入或删除行或列问题 1363
• 外观变化(字体、格式、对齐方式、边框)问题 2476
• 范围填充(例如从 1,2,3 开始；选择这些单元格，然后拖动手柄"以使用 4,5,6...填充更多单元格...)问题 399
• 由脚本编写的值.问题 338
• 查找/替换 问题 1754
• 问题 1568、问题 1119

如果您确实想知道电子​​表格中有多少行，这大约需要 120 毫秒来执行:

If you do want to know how many rows are in a spreadsheet, this takes about 120ms to execute:

var numCols = SpreadsheetApp.getActiveSheet().getRange("1:1").getLastColumn();
var numRows = SpreadsheetApp.getActiveSheet().getRange("A:A").getLastRow();

我已经证明将值写入到一张表 比使用 ScriptDB.您可以预期写入一个小范围的时间很短，大约 1 毫秒.

I've already shown that it's faster to write a value to a sheet than to use ScriptDB. You can expect an insignificant time to write a small range, around 1ms.

因此，如果您可以检测到正在添加的行或列，那么注册更改所需的时间不到十分之二秒.这个 onEdit() 演示了一种测量电子表格范围的技术，并报告工作表尺寸的变化.(要测试、添加或删除行或列，然后进行触发 onEdit() 的编辑.)它还包含计时器 - 随意尝试其他测量和/或存储值的方法，以看看什么最适合你.

So, if you could detect a row or column being added, it would cost you less than 2 tenths of a second to register the change. This onEdit() demonstrates a technique to measure the extent of a spreadsheet, and reports changes in sheet dimensions. (To test, add or delete rows or columns, then make an edit that triggers onEdit().) It also contains timers - feel free to experiment with other ways of measuring and/or storing values, to see what works best for you.

function onEdit() {
  // Use start & stop to time operations
  var start = new Date().getTime();

  // We want the size of the sheet, so will select ranges across and down the
  // whole sheet. Cannot use getDataRange(), as it selects only occupied cells.
  var numCols = SpreadsheetApp.getActiveSheet().getRange("1:1").getLastColumn()
  var numRows = SpreadsheetApp.getActiveSheet().getRange("A:A").getLastRow();

  var stop = new Date().getTime();
  var timeToMeasure = (stop-start);

  // Did things change?
  var oldSize = SpreadsheetApp.getActiveSheet().getRange("A1:B1").getValues();
  if (oldSize[0][0] != numCols || oldSize[0][1] != numRows) {
    // Yes, they did - Let's store the new dimensions
    start = new Date().getTime();

    SpreadsheetApp.getActiveSheet().getRange("A1:B1").setValues([[numCols,numRows]]);

    var stop = new Date().getTime();
    var timeToStore = (stop-start);  

    Browser.msgBox("Sheet is "+numCols+" by "+numRows+"."
                  +" ("+timeToMeasure+"ms to measure, "+timeToStore+"ms to store.)");
  }
}

这篇关于检测用户在谷歌电子表格中插入行或列并在脚本中做出反应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！