使用Google表格脚本在列中查找空单元格 [英] Finding an empty cell in a column using google sheet script

问题描述

我正在尝试在Google表格的特定列中找到一个空单元格.我熟悉getLastRow()，但它返回整个工作表的最后一行.我想获取特定列中的第一个空单元格.因此，我使用了for循环和if.但是我不知道为什么它不起作用.问题是for循环不返回任何内容. 我在工作表测试(第2行)中获得了H行(位置8)的10行.前5行已包含内容.稍后将使用其他代码将数据添加到此单元格.因此，我希望能够在此列中找到第一个空单元格，以便能够放置新数据.

I am trying to find an empty cell in a specific column in google sheets. I am familiar with getLastRow(), but it returns the last row in whole sheet. I want to get the first empty cell in a specific column. So, I used a for loop and if. But I don't know why it is not working. The problem is that for loop does not return anything. I am getting 10 rows of the column H (position 8) in the sheet test (line2). first 5 rows already have content. Data will be added to this cell later using another code. so, I want to be able to find the first empty cell in this column to be able to put the new data.

var sheettest = SpreadsheetApp.getActive().getSheetByName("test");
var columntest = sheettest.getRange(4, 8, 10).getValues();  
    for(var i=0; columntest[i]=="" ; i++){
      if (columntest[i] ==""){ 
        var notationofemptycell = sheettest.getRange(i,8).getA1Notation();
        return notationofemptycell 
      }

  }

如我所说，我想在该列中找到第一个空单元格.我定义了for循环，只要该单元格不为空即可.那么如果该单元格为空，则应返回该单元格的A1表示法. 似乎for循环一直进行到找到空单元为止，因为在调试中我没有得到var"notationofemptycell"的定义.

As I said, I want to find the first empty cell in that column. I defined the for loop to go on as long as the cell is not empty. then if the cell is empty, it should return the A1 notation of that cell. It seems the for loop does go on until it find the empty cell, because I get no definition for the var "notationofemptycell" in debugging.

推荐答案

您需要遍历columntest的长度.试试这个:

You need to loop through the length of columntest. Try this:

function myFunction() {
  var sheettest = SpreadsheetApp.getActive().getSheetByName("test");
  var lr=sheettest.getLastRow()
  var columntest = sheettest.getRange(4, 8, lr).getValues();  
    for(var i=0; i<columntest.length ; i++){
      if (columntest[i] ==""){ 
        var notationofemptycell = sheettest.getActiveCell().getA1Notation();
        Logger.log( notationofemptycell)
        break;//stop when first blank cell on column H os found.
      }
  }
}

这篇关于使用Google表格脚本在列中查找空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！