更快地找到第一个空行 [英] Faster way to find the first empty row

问题描述

我已经创建了一个脚本，每隔几个小时向Google Apps电子表格添加一行新内容。

这是我找到的第一个函数空行：

<预类= 郎-JS prettyprint-越权> 函数getFirstEmptyRow（）{
变种SPR = SpreadsheetApp.getActiveSpreadsheet（ ）;
var cell = spr.getRange（'a1'）;
var ct = 0;
while（cell.offset（ct，0）.getValue（）！=）{
ct ++;
}
return（ct）;
}


它可以正常工作，但是当达到大约100行时，甚至十秒钟。
我担心当达到数千行时，它会太慢，可能会超时或更糟。
有没有更好的方法？

解决方案

Google Apps脚本博客在优化电子表格操作，讨论批量读取和写入操作，这可能会加快速度。我在一个有100行的电子表格上试了你的代码，花了大约7秒钟。通过使用 Range.getValues（）

 函数getFirstEmptyRow（）{
 var spr = SpreadsheetApp .getActiveSpreadsheet（）; 
 var column = spr.getRange（'A：A'）; 
 var values = column.getValues（）; //在一次调用中获取所有数据
 var ct = 0; 
 while（values [ct] [0]！=）{
 ct ++; 
} 
 return（ct）; 
 
 
 
 $ b $ p 
 $ b 
如果电子表格变得足够大，您可能需要以大块100行或1000行，而不是抓取整列。
 
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);
}
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?
解决方案
The Google Apps Script blog had a post on optimizing spreadsheet operations that talked about batching reads and writes that could really speed things up. I tried your code on a spreadsheet with 100 rows, and it took about seven seconds. By using Range.getValues(), the batch version takes one second.
function getFirstEmptyRow() {
  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][0] != "" ) {
    ct++;
  }
  return (ct);
}
If the spreadsheet gets big enough, you might need to grab the data in chunks of 100 or 1000 rows instead of grabbing the entire column.

                        
这篇关于更快地找到第一个空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！