ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?

Question

In my spreadsheet, I have a running script, which is using the getLastRow() function as an essential part of its logic.

Ever since I applied the array formula in one of my columns, the getLastRow() function doesn't work properly. It seems that the array formula is "applying" all the way to the bottom of the sheet even when there are no other values in the other columns and thus, getLastRow() is returning the last row where there is an array formula, instead of the actual non-empty row.

Writing a slow function which checks which cells are empty is not an option for me, since the script will run out of time with such thing running (it has tens of thousands of rows).

Does anyone have any suggestions for a workaround?

Here is the ARRAYFORMULA:

=ArrayFormula(IF(A2:A="",,WEEKNUM(A2:A, 2)))

Answer 1

Issue:

• Undesirable addition of empty strings in all the available rows by traditional usage of ARRAYFORMULA(IF(A:A="",...))

Solution:

• Using ARRAYFORMULA properly with INDEX/COUNTA(to determine the last row that's needed) ensures formula is only filled upto the needed row instead of a camouflage

• INDEX/COUNTA: INDEX returns a value as well as a cell reference. A2:INDEX(A2:A,COUNTA(A2:A)) => If COUNTA(...) returns 10 => A2:INDEX(A2:A,10) => A2:A11 is the final reference feeded to weeknum

• Assuming there are no blanks in between your data,

  =ARRAYFORMULA(WEEKNUM(A2:INDEX(A2:A,COUNTA(A2:A)),2))
  

• Another alternative is to use ARRAY_CONSTRAIN/COUNTA:

  =ARRAY_CONSTRAIN(ARRAYFORMULA(WEEKNUM(A2:A, 2)),COUNTA(A2:A))
  

Unless Google does inbuilt optimizations, INDEX/COUNTA is preferred over ARRAY_CONSTRAIN

Answer 2

Another solution is to temporarily remove the ArrayFormulas with

sheet.getRange("location of array formula").setValue('');

Then calculate lastRow

var lastRow = sheet.getLastRow();

Then replace the arrayformula

sheet.getRange("location of array formula").setFormula('the formula');

Answer 3

Here is a function you can use to determine the "true" lastRow and lastColumn values of a Sheet values. It will handle both messy ArrayFormula() and merged cells.

function getSheetBoundaries2(sheet) {
  var dim = { lastColumn: 1, lastRow: 1 };
  sheet.getDataRange().getMergedRanges()
    .forEach(function (e) {
      var lastColumn = e.getLastColumn();
      var lastRow = e.getLastRow();
      if (lastColumn > dim.lastColumn) dim.lastColumn = lastColumn;
      if (lastRow > dim.lastRow) dim.lastRow = lastRow;
    });
  var rowCount = sheet.getMaxRows();
  var columnCount = sheet.getMaxColumns();
  var dataRange = sheet.getRange(1, 1, rowCount, columnCount).getValues();
  for (var rowIndex = rowCount; rowIndex > 0; rowIndex -= 1) {
    var row = dataRange[rowIndex - 1];
    if (row.join('').length > 0) {
      for (var columnIndex = columnCount; columnIndex > dim.lastColumn; columnIndex -= 1) {
        if (("" + row[columnIndex - 1]).length > 0) dim.lastColumn = columnIndex;
      }
      if (dim.lastRow < rowIndex) dim.lastRow = rowIndex;
    }
  }
  return dim;
}