arraysgoogle-sheetslambdagoogle-sheets-formulareduce

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


Solution

  • 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