Controlling the determination of a spreadsheet's last row by getLastRow

Question

I have a script in my Google Sheet which copies a row of data from my "Job History" sheet to the "Job Schedule" sheet when cell AV in the row in "Job History" is cleared. This works fine if there are completely empty rows on the "Job Schedule" sheet, but some of the columns on the target sheet contain formulas and other characters, and this is causing getLastRow() to consider them as rows containing data.

How can I define what the last row is? Is there a way I can have getLastRow() check just one cell in the row to determine whether it is the last row or not? Here is my script:

function onEdit(event) {
  var editedCell;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Job History" && r.getColumn() == 47 && r.getValue() == "") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Job Schedule");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns);
    var source = s.getRange(row, 1, 1, numColumns);
    var notes = source.getNotes();
    source.copyTo((target), {contentsOnly:true});
    target.setNotes(notes);
    s.deleteRow(row);
  }
}

Please see this example. The script it has was created to move rows from the "Job Schedule" sheet to the "Job History" sheet when it receives an "X" in its Column 47 cell, and to the "Job Holding" sheet when receiving an "H". This has been working with no problems, but when I try to 'reverse' this move from the History and Holding sheets back to the Schedule sheet when their Column 47 values are cleared, it does not happen because some of the cells in the 'empty' rows are formatted with formulas and some characters, so if a row could be seen as empty if the cell in column B is empty, for example, then I think it would work.

Answer 1

While you can't alter the way in which Sheet#getLastRow operates, you can use it to limit your search space - i.e. instead of needing to check every row from the last possible (the row given by getMaxRow()) until you find a non-empty cell, you can step up from the getLastRow() row.

There are two approaches you can take - one simulates pressing Ctrl + Up, and the other tests the cells.

Simulated keypress / contiguous range detection

This uses the getNextDataCell method, and then offsets once in the opposite direction (since it selects the next edge cell with data (or the edge of the sheet)). It will never return the first row in the sheet.

// Will throw if called on an expanding arrayformula column. Adds a new row if needed.
function getTargetRowInColumn_(sheet, columnIndex) {
  const maxRow = sheet.getMaxRows();
  // If the last row is the max row (e.g. an expanding arrayformula is present in a column),
  // then accessing getLastRow() + 1 will silently fail.
  var r = sheet.getRange(maxRow, columnIndex + 1);;
  if (sheet.getLastRow() === maxRow &&
     (r.getValue() !== "" || r.getFormula()))
  {
    // We want either the next row (which we will create), or there is a formula
    // that will add values to this column - user error for accessing this column.
    sheet.insertRowsAfter(maxRow, 1);
    SpreadsheetApp.flush();
    r = r.offset(1, 0);
    if (r.getValue() !== "" || r.getFormula())
      throw new RangeError("target column index '" + columnIndex + "' has expanding formula");
    
    return ++maxRow; // Same output as r.getRow(), but faster.
  }
  
  r = r.getNextDataCell(SpreadsheetApp.Direction.UP);
  do {
    r = r.offset(1, 0);
  } while (r.getValue());
  return r.getRow();
}

edit: Since simple trigger calls don't display errors, you won't see a visual notification of any issues - you'll have to check your script's logs on Stackdriver. I've added some sanity checks for out-of-bounds Range accesses, which generally result in silent failures even when running in a context that allows displaying error messages.

Value testing

This uses a batch call to Range#getValues and Range#getFormulas, and then never uses the sheet again. Here we test if the array index is an empty string (""), and if it is, require that there is no formula in the index either. Both getValues and getFormulas return an empty string for "no value/formula", and since a formula may intentionally return the value "", both need to be checked.

// Does not throw if called on an expanding arrayformula column. Does not add a new row.
function getTargetRowInColumn_(sheet, columnIndex) {
  var lastRow = sheet.getLastRow();
  const r = sheet.getRange(1, columnIndex + 1, lastRow, 1);
  const values = r.getValues();
  const formulas = r.getFormulas();
  do {
    --lastRow;
  // isEmptyString = values[lastRow][0] === "";
  // isNotAFormula = !formulas[lastRow][0];
  //          isEmptyStringValue     &&     isNotAFormula
  } while (values[lastRow][0] === "" && !formulas[lastRow][0]);
  // lastRow now points to an array index that is occupied by either a formula or value.
  // += 1 converts from array index to Range row, and then += 1 again
  // (since we want the next row, which is empty).
  lastRow += 2;
  return lastRow;
}

The value testing approach will offer you the most flexibility, since you get to define exactly what controls the changes to the lastRow variable. However, since you define exactly what controls the changes to the lastRow variable, you have to consider possible pitfalls that are associated with comparison and short-circuit operators, and the data being stored on your sheet. The contiguous range detection removes all of this from your control, which is both good and bad (depending on your specific task). Note that both examples above use a column index, not a column. "A" is 0, "B" is 1, etc. If you want to change that, by all means, do so.

---

An example usage, which will pop up a toast containing the row number of the next blank cell in the first 3 columns of the first sheet in the spreadsheet whenever any cell on any sheet is edited.

function onEdit(e) {
  const all = e.source.getSheets();
  var message;
  try {
    message = ["eh?", "bee", "see"].map(function (colName, i) {
      return [
        "Column '" + colName + "'",
        "(index " + i + "):",
        getTargetRowInColumn_(all[0], i)
      ].join(" ");
    }).join("; ");
  } catch (err) {
    message = err.message;
  }
  e.source.toast(message, "Row of the next blank cell in first sheet", 60);
  // Datestamp the next empty cell in column A of the first sheet.
  var targetColumn = 1; // Column A
  all[0].getRange(getTargetRowInColumn_(all[0], targetColumn - 1), targetColumn).setValue(new Date());
}