在Google表格的列中查找第一个空行的更快方法 [英] Faster way to find the first empty row in a Google Sheet column
问题描述
我编写了一个脚本，每隔几个小时将一个新行添加到Google Apps电子表格中.
I've made a script that every few hours adds a new row to a Google Apps spreadsheet.
这是我用来查找第一个空行的功能:
This is the function I've made to find the first empty row:
function getFirstEmptyRow() {
var spr = SpreadsheetApp.getActiveSpreadsheet();
var cell = spr.getRange('a1');
var ct = 0;
while ( cell.offset(ct, 0).getValue() != "" ) {
ct++;
}
return (ct);
}
它可以正常工作，但是当到达约100行时，它变得非常缓慢，甚至十秒钟. 我担心当到达数千行时，它会太慢，可能超时或更糟. 有更好的方法吗?
It works fine, but when reaching about 100 rows, it gets really slow, even ten seconds. I'm worried that when reaching thousands of rows, it will be too slow, maybe going in timeout or worse. Is there a better way?
推荐答案
此问题现在已经具有超过 12,000次浏览-由于新工作表的性能特征有所不同，现在该进行更新了比 Serge进行初次测试时.
This question has now had more than 12K views - so it's time for an update, as the performance characteristics of New Sheets are different than when Serge ran his initial tests.
好消息:整体性能要好得多！
与第一个测试一样，只需读取工作表的数据一次，然后在阵列上进行操作，就会带来巨大的性能优势.有趣的是，Don的原始功能比Serge测试的修改版本要好得多. (看来while比for快，这是不合逻辑的.)
As in the first test, reading the sheet's data just once, then operating on the array, gave a huge performance benefit. Interestingly, Don's original function performed much better than the modified version that Serge tested. (It appears that while is faster than for, which isn't logical.)
示例数据的平均执行时间仅为 38ms ，低于之前的 168ms .
The average execution time on the sample data is just 38ms, down from the previous 168ms.
// Don's array approach - checks first column only
// With added stopping condition & correct result.
// From answer https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRowByColumnArray() {
var spr = SpreadsheetApp.getActiveSpreadsheet();
var column = spr.getRange('A:A');
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct+1);
}
测试结果:
这里是结果，在具有100行x 3列的电子表格中进行了50次迭代总结(填充了Serge的测试功能).
Test results:
Here are the results, summarized over 50 iterations in a spreadsheet with 100 rows x 3 columns (filled with Serge's test function).
函数名称与下面脚本中的代码匹配.
The function names match the code in the script below.
最初的要求是找到第一个空行.以前的脚本实际上都没有实现这一目标.许多人只检查一列，这意味着它们可以给出假阳性结果.其他人只能找到所有数据之后的第一行，这意味着会丢失非连续数据中的空行.
The original ask was to find the first empty row. None of the previous scripts actually deliver on that. Many check just one column, which means that they can give false positive results. Others only find the first row that follows all data, meaning that empty rows in non-contiguous data get missed.
这是一个符合规格的功能.它已包含在测试中，虽然比闪电般快的单列检查器慢，但它以可观的68毫秒出现，比正确答案高出50％！
Here's a function that does meet the spec. It was included in the tests, and while slower than the lightning-fast single-column checker, it came in at a respectable 68ms, a 50% premium for a correct answer!
/**
* Mogsdad's "whole row" checker.
*/
function getFirstEmptyRowWholeRow() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var row = 0;
for (var row=0; row<values.length; row++) {
if (!values[row].join("")) break;
}
return (row+1);
}
完整脚本:
如果您想重复测试，或者将自己的功能添加到混音中作为比较，只需将整个脚本并在电子表格中使用它即可.
Complete script:
If you want to repeat the tests, or add your own function to the mix as a comparison, just take the whole script and use it in a spreadsheet.
/**
* Set up a menu option for ease of use.
*/
function onOpen() {
var menuEntries = [ {name: "Fill sheet", functionName: "fillSheet"},
{name: "test getFirstEmptyRow", functionName: "testTime"}
];
var sh = SpreadsheetApp.getActiveSpreadsheet();
sh.addMenu("run tests",menuEntries);
}
/**
* Test an array of functions, timing execution of each over multiple iterations.
* Produce stats from the collected data, and present in a "Results" sheet.
*/
function testTime() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.getSheets()[0].activate();
var iterations = parseInt(Browser.inputBox("Enter # of iterations, min 2:")) || 2;
var functions = ["getFirstEmptyRowByOffset", "getFirstEmptyRowByColumnArray", "getFirstEmptyRowByCell","getFirstEmptyRowUsingArray", "getFirstEmptyRowWholeRow"]
var results = [["Iteration"].concat(functions)];
for (var i=1; i<=iterations; i++) {
var row = [i];
for (var fn=0; fn<functions.length; fn++) {
var starttime = new Date().getTime();
eval(functions[fn]+"()");
var endtime = new Date().getTime();
row.push(endtime-starttime);
}
results.push(row);
}
Browser.msgBox('Test complete - see Results sheet');
var resultSheet = SpreadsheetApp.getActive().getSheetByName("Results");
if (!resultSheet) {
resultSheet = SpreadsheetApp.getActive().insertSheet("Results");
}
else {
resultSheet.activate();
resultSheet.clearContents();
}
resultSheet.getRange(1, 1, results.length, results[0].length).setValues(results);
// Add statistical calculations
var row = results.length+1;
var rangeA1 = "B2:B"+results.length;
resultSheet.getRange(row, 1, 3, 1).setValues([["Avg"],["Stddev"],["Trimmed\nMean"]]);
var formulas = resultSheet.getRange(row, 2, 3, 1);
formulas.setFormulas(
[[ "=AVERAGE("+rangeA1+")" ],
[ "=STDEV("+rangeA1+")" ],
[ "=AVERAGEIFS("+rangeA1+","+rangeA1+',"<"&B$'+row+"+3*B$"+(row+1)+","+rangeA1+',">"&B$'+row+"-3*B$"+(row+1)+")" ]]);
formulas.setNumberFormat("##########.");
for (var col=3; col<=results[0].length;col++) {
formulas.copyTo(resultSheet.getRange(row, col))
}
// Format for readability
for (var col=1;col<=results[0].length;col++) {
resultSheet.autoResizeColumn(col)
}
}
// Omiod's original function. Checks first column only
// Modified to give correct result.
// question https://stackoverflow.com/questions/6882104
function getFirstEmptyRowByOffset() {
var spr = SpreadsheetApp.getActiveSpreadsheet();
var cell = spr.getRange('a1');
var ct = 0;
while ( cell.offset(ct, 0).getValue() != "" ) {
ct++;
}
return (ct+1);
}
// Don's array approach - checks first column only.
// With added stopping condition & correct result.
// From answer https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRowByColumnArray() {
var spr = SpreadsheetApp.getActiveSpreadsheet();
var column = spr.getRange('A:A');
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct+1);
}
// Serge's getFirstEmptyRow, adapted from Omiod's, but
// using getCell instead of offset. Checks first column only.
// Modified to give correct result.
// From answer https://stackoverflow.com/a/18319032/1677912
function getFirstEmptyRowByCell() {
var spr = SpreadsheetApp.getActiveSpreadsheet();
var ran = spr.getRange('A:A');
var arr = [];
for (var i=1; i<=ran.getLastRow(); i++){
if(!ran.getCell(i,1).getValue()){
break;
}
}
return i;
}
// Serges's adaptation of Don's array answer. Checks first column only.
// Modified to give correct result.
// From answer https://stackoverflow.com/a/18319032/1677912
function getFirstEmptyRowUsingArray() {
var sh = SpreadsheetApp.getActiveSpreadsheet();
var ss = sh.getActiveSheet();
var data = ss.getDataRange().getValues();
for(var n=0; n<data.length ; n++){
if(data[n][0]==''){n++;break}
}
return n+1;
}
/**
* Mogsdad's "whole row" checker.
*/
function getFirstEmptyRowWholeRow() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var row = 0;
for (var row=0; row<values.length; row++) {
if (!values[row].join("")) break;
}
return (row+1);
}
function fillSheet(){
var sh = SpreadsheetApp.getActiveSpreadsheet();
var ss = sh.getActiveSheet();
for(var r=1;r<1000;++r){
ss.appendRow(['filling values',r,'not important']);
}
}
// Function to test the value returned by each contender.
// Use fillSheet() first, then blank out random rows and
// compare results in debugger.
function compareResults() {
var a = getFirstEmptyRowByOffset(),
b = getFirstEmptyRowByColumnArray(),
c = getFirstEmptyRowByCell(),
d = getFirstEmptyRowUsingArray(),
e = getFirstEmptyRowWholeRow(),
f = getFirstEmptyRowWholeRow2();
debugger;
}
这篇关于在Google表格的列中查找第一个空行的更快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
