1

I have this formula =ARRAYFORMULA(IF(B4:B="","",(B4:B-A4:A))) in my spreadsheet. And also, I have the getLastRow() in the appscript. Apparently the ArrayFormula is affecting the getLastRow(). Is there a possible workaround? Thanks.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
John Emmanuel Dela Cruz
  • 81
  • 8
  • Does this answer your question? [ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?](https://stackoverflow.com/questions/46883862/arrayformula-is-breaking-the-getlastrow-funtion-possible-workarounds) – TheMaster Apr 05 '20 at 19:30
  • @TheMaster somehow it gives me an Idea, but I don't know how to create the proper formula with INDEX/COUNT in it. Can you provide a sample formula? – John Emmanuel Dela Cruz Apr 05 '20 at 19:42
  • Show what you've tried or just use `arrayconstrain()`with `counta()` – TheMaster Apr 05 '20 at 19:44
  • Not yet, I'm not really good at coding or formula. – John Emmanuel Dela Cruz Apr 06 '20 at 00:45
  • @TheMaster answered this question with your suggestion but instead of `counta()` used max row with data to avoid a glitch if we have spaces in the data. – CodeCamper Apr 06 '20 at 01:54

1 Answers1

1

ArrayFormula is affecting getLastRow Yes, that is a feature, it is supposed to change the LastRow because you are outputting an Array all the way to the bottom of the spreadsheet.

Simple fix:

=arrayformula(array_constrain(IF(B4:B="","",(B4:B-A4:A)),max(IF(B4:B="",0,row(B4:B)))-row()+1,1))

What you were originally doing: Outputting an Array with mostly blank spaces all the way to the bottom of the spreadsheet

What this new formula is doing: Outputting only the data you want by restricting the size of the Array to the last row containing data.

CodeCamper
  • 6,609
  • 6
  • 44
  • 94