获取特定列功能的最后一行 - 最佳解决方案 [英] Get last row of specific column function - best solution

问题描述

用lastRow可以得到工作表中的最后一行。有时你需要特定列的最后一行。 Google Apps脚本das不提供此功能。网站上已有大约5个问题和解决方案，但所有代码都包含一些特定的代码。

我认为解决方案是我认为最快的解决方案案例 - 您开始查看工作表的末尾，并在您指定的列中向后看 - 并从中做出一个干净的功能。

所以这是初学者复制粘贴的一个贡献 - 参数RowNumber是您想要看作为数字的列：0是A，1是B，2是C等。该函数返回最后一行作为数字（4）

因为我在第3天作为应用程序脚本用户，所以我的问题是：如果这不是最快和最干净的方式，或者脚本在某些情况下无法正常工作，请告诉我。对于我的用例来说它是完美的。

  function getLastColumnRow（RowNumber，sheet）{
 var data = sheet.getDataRange ）.getValues（）; 
 var numRows = data.length; 
 
 //给定行中的最后一行从下到上循环RowNumber
 while（data [numRows  -  1] [RowNumber] ==&& numRows> 0）{
 numRows--; 
} 
 return numRows 
} 
  

解决方案 div>

你的代码只有一个小错误，你应该使用 === 运算符而不是 == 。因为您将0值视为空白。如果您不知道它们之间的区别，请尝试快速搜索以了解更多信息。

虽然您将变量的列号命名为RowNumber ，为什么不columnNumber？这对我来说更有意义。

我通常在我的所有脚本中尝试做的另一件事是减少API调用。搜索后我会在其他地方需要数据。所以，如果这个函数直接接受数据而不是表单对象，那将会很好。或者任何一个。

最后，它可能并没有太大的区别，但是你可以减少一个循环的减法，减少一个。 p>

将我的意见付诸实践，下面是结果代码：

  function getLastRowOfColumn（columnNumber，optData，optSheet）{
 var data = optData！== null？ optData：optSheet.getDataRange（）。getValues（）; 
 var row = data.length-1; $; 
用于（;行> 0&& data [row] [columnNumber] ===''; --row）; 
返回行+1; 
} 
  

对两个代码的警告，我们只是通过检查单元格值。它可能有一个公式，根据定义，这意味着单元格不是空白的，即返回一个空字符串。例如

= IF（A1 =一些比较返回false;really？;）

With "lastRow" one can get the last row in a sheet. Sometimes you need the last row of a specific column. Google Apps script das not provide this function. There are around 5 questions and solutions already here on the site, but all have some specific code wrapped around it.

I took the solution what i think is the fastest in most cases - you start looking at the end of the sheet and look backwards in the column you specified - and made a clean function out of it.

So this is a contribution for beginners to copy paste this - the argument "RowNumber" is the column you want to look at as a number: 0 is A, 1 is B, 2 is C etc. The function returns the last row as a number ("4")

Because i am on day 3 as a apps script user, my question is: if this is not the fastest and cleanest way to do it, or the script does not work properly on some circumstances, please tell me. for my usecase it works perfect.

function getLastColumnRow(RowNumber, sheet) {
  var data = sheet.getDataRange().getValues();
  var numRows = data.length;

 // loop from bottom to top for last row in given row "RowNumber"
  while( data[numRows - 1][RowNumber] == "" && numRows > 0 ) {
    numRows--;
  }
  return numRows
}

解决方案

There's only one minor mistake on your code, you should use the === operator and not the ==. Because you're considering "0" values as empty. If you don't know the difference between them, try a quick search to learn more.

It's weird though that you named the variable that holds the column number as "RowNumber", why not "columnNumber"? It makes more sense to me.

Another thing that I usually try to do on all my scripts is to reduce API calls. And it's very common that after searching I'll need the data elsewhere. So, it would be good if this function accepted the data directly instead of the sheet object. Or maybe either one.

At last, it probably does not really make a big difference, but you could do one less subtraction per loop, by starting one less.

Putting my comments to practice, here's the resulting code:

function getLastRowOfColumn(columnNumber, optData, optSheet) {
  var data = optData !== null ? optData : optSheet.getDataRange().getValues();
  var row = data.length-1;
  for( ; row > 0 && data[row][columnNumber] === ''; --row ) ;
  return row+1;
}

A caveat on both our codes, we're considering blank just by checking a cell value. It might have a formula, which by definition means that the cell is not blank, that is returning an empty string. e.g.

=IF(A1="Some comparison that returns false"; "really?"; "")

这篇关于获取特定列功能的最后一行 - 最佳解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！