检测用户在谷歌电子表格中插入行或列并在脚本中作出反应 [英] Detect user inserting row or column in a google spreadsheet and reacting in a script
问题描述
在Google Apps脚本中，其中一个基本工具是电子表格中的onEdit触发器，它使我们能够检测用户何时编辑单元格并对其作出反应。
当用户插入行或列时如何？有没有办法来检测？
这会触发一个onEdit吗？如果是这样的话，我想在ScriptDb中保留一些行数或列数，然后检查每一次会做什么，但是会花费很多时间，因为getMaxRows（）已经非常慢了，并且向ScriptDb伸出的时间是
你怎么看？ 有一个不触发 onEdit（）的编辑动作数量，这不是一个全面的列表，有还有更多报道的排除事项：
$ b
如果您想知道电子表格中有多少行，则需要大约120ms才能执行：
var numCols = SpreadsheetApp.getActiveSheet（）。getRange（1：1）。getLastColumn（）;
var numRows = SpreadsheetApp.getActiveSheet（）。getRange（A：A）。getLastRow（）;
我已经证明将值写入表单比使用ScriptDB更快。你可以预计一个微不足道的时间来写一个小范围，大约1ms。
因此，如果你能够检测到一行或一列被添加，它会花费你不到2秒钟的时间注册变化。这个 onEdit（）演示了一种技术来衡量电子表格的范围，并报告表格尺寸的变化。 （要测试，添加或删除行或列，然后进行编辑，触发 onEdit（）。）它还包含计时器 - 随时可以尝试其他测量方法/或存储值，以查看哪些最适合您。 function onEdit（）{
//使用开始&安培;停止到时间操作
var start = new Date（）。getTime（）;
//我们希望工作表的大小，因此会选择
//整张工作表中的范围。无法使用getDataRange（），因为它只选择占用的单元格。
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）;
//有事情改变了吗？
var oldSize = SpreadsheetApp.getActiveSheet（）。getRange（A1：B1）。getValues（）;
if（oldSize [0] [0]！= numCols || oldSize [0] [1]！= numRows）{
//是的，他们做了 - 让我们存储新维度
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来衡量，+ timeToStore +ms来存储。） ）;
}
}
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 ?
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.
What do you think ?
There are a number of editing actions that do not trigger onEdit(), this isn't a comprehensive list, there are many more reported exclusions:
- Form submissions Issue 4568.
Inserting or Deleting Rows or Columns Issue 1363- Changes in appearance (Font, Format, Alignment, Borders) Issue 2476
- Range fill (e.g. start with 1,2,3; select those cells, and drag the 'handle' to fill more cells with 4,5,6...) Issue 399
- Values written by scripts. Issue 338
- Find / Replace Issue 1754
- Issue 1568, Issue 1119
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();
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.
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屋！
