如何找到最后一个单元格，该单元格的值比Google表格中的其他列短?

问题描述：

我试图弄清楚如何向Google表格中特定列的最后一行添加值.并非电子表格/表格中的所有列都长度相等，因此我不能简单地寻找最后一行并添加到列中.我需要在列中找到下一个空单元格，然后在其中添加新值.查看文档，我认为涉及到 getRange()和 setValue()函数，但是我认为设置实际范围需要一些额外的编码才能确定下一个空单元格.一旦知道了，就可以像下面这样进行存储.

I'm trying to figure out how to add a value to the last row in a specific column in Google Sheets. Not all columns in the spreadsheet/table are of equal length so I can't simply look for the last row and add to a column. I need to find the next empty cell in a column and add the new value there. Looking through the docs I think involves the getRange() and setValue() functions but I think setting the actual range requires some additional coding to determine the next empty cell. Once I get that then I can do somtrhing like the following.

单击按钮会触发 addRecord()函数

function addRecord(){

  var recVals = {};

  recVals.source = document.getElementById("source").value;
  recVals.medium = document.getElementById("medium").value;
  recVals.product = document.getElementById("product").value;

  google.script.run.userClicked(recVals);
}

然后在我的应用程序脚本中运行 userClicked()函数，并将值传递给它并写入电子表格:

Then in my apps scripts the userClicked() function runs and the value(s) are passed to it and written to the spreadsheet:

function userClicked(recVals){

  var ss = SpreadsheetApp.openById(ssId)
  var ws = ss.getSheetByName("Data");

  ws.appendRow([recVals.source, recVals.medium, recVals.product, new Date()]);
}

问题是， appendRow()在最后一行添加了一行，在该行的下面，我需要设置这些值.

The problem is, that appendRow() adds the row at the end, below where I need the values to be set.

答

您可以使用范围的 getNextDataCell()方法来获取特定列底部的第一个空单元格.下面的代码在列的底部开始搜索，并向上移动空白单元格，直到找到具有值的第一个单元格.您需要第一个空单元格，因此需要将1添加到找到的行值中.

You can use the getNextDataCell() method of a range to get the first empty cell at the bottom of a particular column. The code below starts the search at the bottom of the column and moves up through empty cells until it finds the first cell with a value. You need the first empty cell, so 1 needs to be added to the found row value.

function getFirstEmptyCellInColumn(po){
  var columnLetterToGet,columnNumberToGet,direction,lastRow,lastRowInThisColWithData,
      rng,rowToBeginSearch,rowToSet,sh,ss,startOfSearch,totNmbrOfRows;
  
  /*
    po.ssId = The spreadsheet file ID
    po.sheetTabName - The name of the sheet tab to get
    po.columnToSearch - The column number in the sheet tab to find the last value
  */
  
  if (po.ssId) {//The file ID was passed in
    ss = SpreadsheetApp.openById(po.ssId);
  } else {
    ss = SpreadsheetApp.getActiveSpreadsheet();
  }
  
  sh = ss.getSheetByName(po.sheetTabName);

  lastRow = sh.getLastRow();
  //Logger.log('lastRow: ' + lastRow)
  totNmbrOfRows = sh.getMaxRows();
  
  columnNumberToGet = po.columnToSearch;//The column number in the sheet to search
  
  columnLetterToGet = String.fromCharCode(96 + po.columnToSearch);//the column letter to get
  
  switch(true) {
    case (totNmbrOfRows - lastRow) > 1:
      rowToBeginSearch = lastRow + 2;
      break;
    case totNmbrOfRows === lastRow:
      rowToBeginSearch = lastRow;
      break;
  }
  
  startOfSearch = columnLetterToGet + rowToBeginSearch.toString();//Edit and replace with column letter to get
  //Logger.log('startOfSearch: ' + startOfSearch)
  
  rng = sh.getRange(startOfSearch);
  
  direction = rng.getNextDataCell(SpreadsheetApp.Direction.UP);//This starts
  //the search at the bottom of the sheet and goes up until it finds the
  //first cell with a value in it
  
  //Logger.log('Last Cell: ' + direction.getA1Notation())
  
  lastRowInThisColWithData = direction.getRow();
  //Logger.log('lastRowInThisColWithData: ' + lastRowInThisColWithData)
  
  return lastRowInThisColWithData + 1;

}

function userClicked(recVals) {
  var o = {};
  
  o.ssId = "Put Spreadsheet ID here";
  o.sheetTabName = "Sheet Tab Name";
  o.columnToSearch = 3;
  
  var rowToSet = getFirstEmptyCellInColumn(o);
  
  var valuesToSet = [recVals.source, recVals.medium, recVals.product, new Date()];
  
  var ss = SpreadsheetApp.openById("SS ID");
  var sh = ss.getSheetByName("sheet tab name");
  
  sh.getRange(rowToSet, 1,1,valuesToSet.length).setValues([valuesToSet]);
}

如何找到最后一个单元格，该单元格的值比Google表格中的其他列短?

相关推荐