How to find the last cell with a value at the end of a column that is shorter than other columns in Google Sheets?

Question

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.

The button click triggers the addRecord() function

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

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

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

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