Search code examples
google-sheetslambdacountsequencespreadsheet

Why is it faster in Google Sheets to split this formula into two steps?

Edit: I initially diagnosed this problem totally wrong, so the question is entirely rewritten to reflect new understanding.

The problem can be reproduced using a Google spreadsheet with one sheet that contains one header row and a significant number of additional rows (let’s say 5,000).

I wanted column A to increment by 1, starting with A2, as long as the adjacent cell in B was not blank. I used this formula in A1:

={"SKU"; arrayformula(if($B2:$B="","",text(row($A2:$A),"000000")))}

This formula worked but caused extremely significant lag.

In one of my attempts to resolve the issue, I added a helper column before column A and split my formula into two formulas to see which function was causing the lag:

Cell A1: ={"SKU (helper)"; arrayformula(if($C2:$C="","",row($A2:$A)))}

Cell B1: ={"SKU"; arrayformula(if($C2:$C="","",text($A2:$A,"000000")))}

To my surprise, the answer was neither. The lag was completely eliminated. What is the reason? And is it possible to eliminate the lag without the need for a helper column?

Solution

  • use:

    ={"SKU"; SEQUENCE(ROWS(A:A)-5344; 1; 5344)}

    update:

    ={"SKU"; INDEX(TEXT(SEQUENCE(COUNTA(B2:B)), "000000"))}

    enter image description here

    if you have empty cells in between use:

    =LAMBDA(x, {"SKU"; INDEX(IF(x="",,
 TEXT(COUNTIFS(x, "<>", ROW(x), "<="&ROW(x)), "000000")))})
 (B2:INDEX(B:B, MAX((B:B<>"")*ROW(B:B))))

    enter image description here