CODEWITHSUNDEEP
arraysgoogle-sheetslambdagoogle-sheets-formulareduce
AlanSTACK
AlanSTACK

Reputation: 6065

Is it possible to store large data arrays in a single cell for future reference in Google Sheets?

The current formula returns 2 columns and multiple rows. I want to store the result in a single "data cell" for future reference and post-processing.

Is it possible to make sure that the original formula doesn't expand into multiple cells? Is it possible to force it to remain in just one cell as a "data cell" for future reference by queries without refetching the data again over the network?

=GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31))

My goal is to avoid 3 different network calls for something like:

=AVERAGE(GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31)))
=MIN(GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31)))
=MAX(GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31)))

enter image description here

Upvotes: 1

Views: 82

Answers (1)

player0
player0

Reputation: 1

you can compact it like:

=TEXTJOIN(, 1, INDEX(LET(a, 
 GOOGLEFINANCE("MSFT", "price", DATE(2024,1,1), DATE(2024,1,31)), a&{"×", "¤"})))

enter image description here

and then unpack it as:

=INDEX(SPLIT(FLATTEN(SPLIT(D6, "¤")), "×"))

enter image description here

but no idea why this would benefit anything

UPDATE

=LAMBDA(a, REDUCE({"avg","min","max"}, a, LAMBDA(b, c, {b; 
 IFERROR(QUERY(QUERY(GOOGLEFINANCE(c, "price", DATE(2024,1,1), DATE(2024,1,31)), 
 "select avg(Col2),min(Col2),max(Col2)"), "offset 1", ), {"","",""})})))
 (A2:A4)

enter image description here

Upvotes: 1

Related Questions