How To Find The Last Cell With A Value At The End Of A Column That Is Shorter Than Other Columns In Google Sheets?

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 lo

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