google-sheetslambdatimegoogle-sheets-formulabenchmarking

How to check formula speed or compare speed between formulas


Is there a way to check which method is faster in Google Sheets?

For example: I have a big table to import. 20 columns and 5000 rows. As this is only a part of big spreadsheet my concern is if this may slow down my work.

I need only 6 columns from this table and they are scattered (let's say Col1 , Col3, Col5,Col6, Col20) I hesitate between:

I think about general tool that woould help with this kind of problems.


Solution

  • you can benchmark it like this:

    =LAMBDA(x;x)(NOW())-LAMBDA(x;x)(ROWS(  ▬your_formula▬  )*0+NOW())
    

    1 IMPORTRANGE formula that takes whole table nested with query that extracts columns I need.

    =LAMBDA(x;x)(NOW())-LAMBDA(x;x)(ROWS(  QUERY(IMPORTRANGE(...)...)  )*0+NOW())
    

    5 IMPORTRANGE formulas that will import one or 2 column each

    =LAMBDA(x;x)(NOW())-LAMBDA(x;x)(ROWS(  HSTACK(IMPORTRANGE(...); 
                                                  IMPORTRANGE(...); 
                                                  IMPORTRANGE(...); 
                                                  IMPORTRANGE(...); 
                                                  IMPORTRANGE(...))  )*0+NOW())
    

    and format output as time or custom number with pattern mm:ss.000

    also, instead of QUERY you can nowadays use CHOOSECOLS