ArrayFormula is breaking the getLastRow() funtion.

问题:

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

回答1:

Issue:

• Undesirable addition of empty strings in all the available rows by traditional usage of ARRAYFORMULA()

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

• Assuming there are no blanks in between your data,

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

回答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');

回答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;
}