如何找到列末尾的最后一个单元格的值比 Google 表格中的其他列短？

我试图弄清楚如何将值添加到 Google 表格中特定列的最后一行。并非电子表格/表格中的所有列都具有相同的长度，因此我不能简单地查找最后一行并添加到列中。我需要找到列中的下一个空单元格并在其中添加新值。浏览文档我认为涉及getRange() and setValue()函数，但我认为设置实际范围需要一些额外的编码来确定下一个空单元格。一旦我明白了，我就可以做如下的事情。

单击按钮会触发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()函数运行并将值传递给它并写入电子表格：

function userClicked(recVals){

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

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

问题是，appendRow()在末尾添加行，位于我需要设置值的位置下方。

您可以使用getNextDataCell()范围的方法，用于获取特定列底部的第一个空单元格。下面的代码从列底部开始搜索，然后向上移动到空单元格，直到找到第一个包含值的单元格。您需要第一个空单元格，因此需要将 1 添加到找到的行值中。

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]);
}