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

by

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

  • The usage of COUNTA assumes there are no blank cells in between. If there are any, you may need to manually add a offset. If there are two blank cells, add 2 to COUNTA

    A2:INDEX(A2:A,COUNTA(A2:A)+2)

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

It might be hard to fix those array formulas with INDEX/COUNTA manually, so I made a script. This is just a proof of concept and alpha quality. So, test it in a copy of your spreadsheet rather than on the original. Having said that, I’m sure it’ll handle most common cases without trouble.

/**
 * @see https://stackoverflow.com/a/46884012
 */
function fixArrayFormulas_so46884012() {
  const ss = SpreadsheetApp.getActive()/*.getSheetByName('Sheet1')*/,
    map = new Map([
      [
        // Normalize first part of range
        /* A:F */ String.raw`([a-z]+):([a-z]+)`,
        /* A1:F*/ String.raw`$11:$2`,
      ],
      [
        // Convert any previous index/counta to normal ranges
        /* A1:INDEX(F:F,COUNTA(F:F)) */ String.raw`([a-z]+\d+):INDEX\(([a-z]+)\d*:\w+,COUNTA\(\w+:\w+\)\)`,
        /*A1:F*/ String.raw`$1:$2`,
      ],
      [
        // Convert open ended ranges to  index/counta ranges
        /*A1:F*/ String.raw`([a-z]+\d+:)([a-z]+)`,
        /* A1:INDEX(F:F,COUNTA(F:F)) */ `$1INDEX($2:$2,COUNTA($2:$2))`,
      ],
    ]);
  map.forEach((v, k) =>
    ss
      .createTextFinder(k)
      .matchFormulaText(true)
      .useRegularExpression(true)
      .replaceAllWith(v)
  );
}

More Related Contents:

  1. ultimate short custom number formatting – K, M, B, T, etc., Q, D, Googol
  2. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  3. Selecting the last value of a column
  4. .setFormula() and received error missing ) argument list
  5. How to create INDIRECT array string of multiple sheet references in Google Sheets?
  6. Refresh data retrieved by a custom function in Google Sheet
  7. How do you create a “reverse pivot” in Google Sheets?
  8. Create Google Calendar Events from Spreadsheet but prevent duplicates
  9. Automatic timestamp when a cell is filled out
  10. Google Apps Script: How to set “Use column A as labels” in chart embedded in spreadsheet?
  11. Apps Script: how to get hyperlink from a cell where there is no formula
  12. Retrieving Google Sheets cell border style programmatically
  13. Determining the last row in a single column
  14. Cannot find method getRange(number,number)?
  15. Google Apps Script, copy one spreadsheet to another spreadsheet with formatting
  16. Adding Buttons To Google Sheets and Set value to Cells on clicking
  17. How do I communicate a page(modal dialog) with its sibling (sidebar)?
  18. How to replace text in Google Spreadsheet using App Scripts?
  19. Custom function throws a “You do not have the permission required to setValue” error
  20. Margins parameters for Google spreadsheet export as PDF
  21. Google Apps Script: Dynamically creating spreadsheet menu items
  22. Script to convert .XLSX to Google Sheet and move converted file
  23. Hidden field in a Google Form
  24. Apply multiple font colors to the text in a single Google Sheets cell
  25. Google Sheet use Importxml error could not fetch url [duplicate]
  26. On Edit doesnt work if cell auto updates – Need a workaround Google Sheets
  27. Use script in all spreadsheets
  28. How to pause App Scripts until spreadsheet finishes calculation
  29. Custom Functions and Recalculation
  30. Can the google spreadsheet ‘query’ function be used in google apps script?

Leave a Comment