Google Script - 获取特定列的最后一行 [英] Google Script - get last row of specific column
问题描述
我正在寻找一种方法来查找特定列的最后一行(包含数据).假设 A 列第 100 行的最后一个数据项，B 列第 50 行的最后一个数据项.
I'm looking for a way to find the last row (with data) of specific column. Let's say column A last data item in row 100 and column B has last data item in row 50.
.getLastRow();将永远返回 100 给我.
.getLastRow(); will always return 100 to me.
我需要一种简单的方法来获得 50(这是 B 列中最后一个非空行的行号).
I need a simple way to get 50 instead (which is the row number of last non empty row in column B).
我不能使用循环.这非常重要，因为有数千行，这将花费太多时间.
I can't use loops. It's very important as there are thousands of rows, it will simply take too much time.
我在谷歌上搜索了很多，但所有答案都涉及循环.
I've googled a lot, but all answers involve looping.
推荐答案
虽然不知道这个方法好不好，但是这个方法怎么样?此方法不使用循环.请将此作为示例之一进行检查.
Although I don't know whether this is a good method, how about this method? This method doesn't use the loops. Please check this as one of samples.
- 检索您想知道最后一行数的列数据.
- 将列数据作为临时工作表导入到新电子表格中.(在这种情况下，您还可以在当前使用的电子表格中添加一个新工作表，并将其用作临时工作表.)
- 使用
getLastRow()检索最后一行的编号. - 删除临时电子表格.
- Retrieve the column data that you want to know the number of last row.
- Import the column data to a new spreadsheet as a temporary sheet. (In this case, you can also add a new sheet to the spreadsheet you currently use and it can be used as a temporary.)
- Retrieve the number of last row using
getLastRow(). - Remove the temporary spreadsheet.
示例脚本:
var col = ##; // Here, please input a column number.
var ss = SpreadsheetApp.getActiveSheet();
var coldata = ss.getRange(1, col, ss.getLastRow(), 1).getValues();
var tempss = SpreadsheetApp.create("temporary_sheet");
tempss.getActiveSheet().getRange(1,1,coldata.length, coldata[0].length).setValues(coldata);
var last_row = tempss.getLastRow(); // The number of last row
Drive.Files.remove(tempss.getId()); // In this case, the file is removed using Drive API.
注意:
在上述情况下，即使列有空单元格，也可以检索最后一行的编号.如果该列没有空单元格，您可以通过以下脚本检索特定列的最后一行的编号.这不会创建临时工作表.
In above case, the number of last row can be retrieved, even if the column has null cells. If the column has no null cells, you can retrieve the number of last row for a specific column by following script. This doesn't create a temporary sheet.
var last_row = ss.getRange(1, col, ss.getLastRow(), 1).getValues().filter(String).length;
2021 年 5 月 19 日更新:
在这种情况下，我想使用以下 2 种模式.
Updated at May 19, 2021:
In this case, I would like to approach with the following 2 patterns.
通过从工作表的TOP中搜索来检索特定列的第一个空单元格
Retrieving 1st empty cell of specific column by searching from TOP of sheet
通过从工作表的BOTTOM中搜索来检索特定列的第一个非空单元格
Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet
为了实现上述，我认为可以使用以下2种模式.
In order to achieve above, I think that the following 2 patterns, can be used.
检索列的值并使用循环搜索结果.
Retrieve the values of the column and search the result using the loop.
使用 Google Apps Script 的内置方法直接检索结果.
Retrieve directly the result using the built-in methods of Google Apps Script.
我测量了它们的过程成本.结果发现以下2个脚本是所有方法中最低的.
I measured the process cost of them. As the result, it was found that the following 2 scripts are lowest of all methods.
Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
const range = this.getRange(offsetRow, columnNumber, 2);
const values = range.getDisplayValues();
if (values[0][0] && values[1][0]) {
return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
} else if (values[0][0] && !values[1][0]) {
return offsetRow + 1;
}
return offsetRow;
};
// Please run this function.
function main() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const res = sheet.get1stEmptyRowFromTop(3);
console.log(res); // Retrieve the 1st empty row of column "C" by searching from TOP of sheet.
}
- 请提供工作表对象和列号.
- 在这个脚本中，当使用第二个参数时，您可以设置偏移行.例如，当第一行和第二行是标题行时，您可以将此脚本用作
const res = sheet.get1stEmptyRowFromTop(3, 2);.
在这个问题中，我认为这种模式可能是合适的.
In this question, I thought that this pattern might be suitable.
Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
return search ? search.getRow() : offsetRow;
};
// Please run this function.
function main() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const res = sheet.get1stNonEmptyRowFromBottom(3);
console.log(res); // Retrieve the 1st non empty row of column "C" by searching from BOTTOM of sheet.
}
- 请提供工作表对象和列号.
- 在这个脚本中，当使用第二个参数时，您可以设置偏移行.例如，当第一行和第二行是标题行时，您可以将此脚本用作
const res = sheet.get1stNonEmptyRowFromBottom(3, 2);. 当通过从工作表的TOP搜索检索特定列的第一个空单元格"的脚本时用于列C"，则获得第 6 行.
When the script of "Retrieving 1st empty cell of specific column by searching from TOP of sheet" is used for the column "C", the row 6 is obtained.
- 基准:在 Google 电子表格中使用特定列检索第一个空单元格和第一个非空单元格的过程成本Google Apps 脚本
- 在此报告中，您可以看到每种方法的基准测试的详细数据.
这篇关于Google Script - 获取特定列的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
当使用上述脚本时，得到如下结果.
When above script is used, the following result is obtained.
当通过从工作表的底部搜索检索特定列的第一个非空单元格"的脚本时用于列C"，则获得第 9 行.
When the script of "Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet" is used for the column "C", the row 9 is obtained.
