Today I noticed an interesting phenomenon. I have one cell in my Google sheet that prepares data for display in my app using the following formula:
=arrayformula(concatenate(trim(unique(indirect(concatenate(“Collate!$AP2:$AP”,value(C2+1))))) & char(10)))
This produces something that looks like this:
㋇㏠ lllllll 10
㋆㏾ llllllllllllllllllllllllllllllllllllllllllllllllllllll 86★★
㋆㏽ llllllllllllll 22★
㋆㏼ llllllllllllllllllllllllllllllllllllllllllllll 73★
㋆㏻ llllllllllllllllllllllllllllllllllllllllllll 69★
㋆㏺ lllllllllllllllllllll 33★
㋆㏹ llllllllllllllllllllllllllllllllllll 56★
㋆㏸ llllllllllllllllllllllllllllllllllllllllllllllllllllll 86★★
㋆㏷ lllllllllllllllllllllllllllllllllllllllllllllllllllllllll 91★★
㋆㏶ llllllllllllllllllllllllllllllllllllllllllllll 73★★
㋆㏵ llllllllllllllllllllllllllll 44★
㋆㏴ lllllllllllllllllllllllll 39★
㋆㏳ lllllllllllllllllllllllllllll 45★
㋆㏲ llllllllllllll 21★
What I noticed was that the data was being updated in one part of my app more quickly than another part. Ironically, the part of the app that was slow was the table that includes the cell where the formula shown above does its work. The part of the app that was faster (by a considerable amount, perhaps 10 minutes or more) was a cell in a table that merely imports the calculation with the following kind of formula:
=Overall!J2
I found that using a LOOKUP() expression on the original table to go the copied cell solved the delay problem. In other words, instead of using the cell that was right there in my table, going to look for the copied data in a different table is actually FASTER!
I’m not sure if this is a known phenomenon or not – or if I’ve understood what’s happening correctly, but I thought I’d write it up for the benefit of anyone who might be interested. If I’ve come across something that hasn’t been pointed out yet, I might rewrite this as a “TIP” for people using server caching.
LinkedIn
Twitter
