5

I have the following formula in a sheet:

=if(E1="HOLD",,query(Cust_Orders!B6:Z5000,"Select Y,G,I,H,K where H>0  "))

With E1 being a drop down with values HOLD and FETCH

The recalculation when I go to FETCH takes about 13 seconds.

But when it is in HOLD, the query doesn't exceute, and everything goes blank. Not what I want.

I'm trying to avoid recalculating this query every time I make a change in the Cust_Orders range, but keep the old values, as two different pivot tables that are viewed by other people are dependent on it.

Google spreadsheet recalc settings all are 'on change and foo'

Is there a way to do this?

Improve this question

3 Answers 3

Reset to default
3

There is no built-in way to have a "recalculation on demand" for built-in functions. You could save a copy of the formula somewhere the apply it to the cell when you want it to be calculated, then to "freeze" it, copy and paste the values only. You could use Google Apps Script to automate this.

By the other hand, custom functions are recalculated on open and when one of its arguments changes.

NOTE: On the follow-up question on Stack Overflow, In google sheets can I wrap a standard function in a custom function to control when it is run? I shared the core code lines to implement the suggestion made here. After that I published Spreadsheet Freezer, a Google Sheets Add-on that initially is unlisted as it's no extensively tested yet. The link points a site that describes the add-on and includes a Feedback form to ask for the link.

Related questions

Improve this answer
1
  • You must harness the awesome power of iteration!
    – CodeCamper
    Dec 13, 2019 at 3:22
1

There is a simple built-in way to have "recalculation on demand" for built-in functions. Just turn on Iterative calculation (I recommend turning Max number of iterations to 1).

Below is an example using your code: I am assuming your report is occupying cell F1 so feel free to change that cell to whatever cell the report is in.

=if(E1="HOLD",F1,arrayformula(query(transpose(substitute(query(substitute(Cust_Orders!B6:Z5000," ","_"),"Select Col24,Col6,Col8,Col7,Col10 where Col7>0",countif(H5:H5000,">0")+1)," ","\")),"select *",99^99)))

Then in cell G1 put the following:

=arrayformula(transpose(split(transpose(split(F1," ")),"\")))

The only reason we need a helper cell in F1 is because your formula is outputting to multiple cells otherwise you could have done this in one cell.

Now whenever you type HOLD in E1 it will freeze the data and if you remove HOLD it will start updating in real time.

Improve this answer
9
  • I don't understand what the textjoin wrapped around my query does, nor the purpose of the double transpose in G1.
    – Sherwood Botsford
    Dec 16, 2019 at 22:22
  • @SherwoodBotsford Google Sheets incorrectly handles circular references in formulas that pour over to adjacent cells so we need to force the Query output into a single cell which is what we are doing in F1 with the textjoin, in G1 we are "unjoining" with all of our split and transposes. To understand it just break it into steps and add each function one at a time to see what is happening. For example if you try to use the circular reference with just Query you will see only the top left cell remains and the rest become blank without the textjoin. Were you able to get it working?
    – CodeCamper
    Dec 16, 2019 at 23:59
  • @SherwoodBotsford query header parameter = notice we are saying every row is a header to trick the query to putting it all in one row then we use the textjoin to collapse the one row into one cell. We force output into a single cell so we can save it with a circular reference double transpose/split = breaking it back into rows and columns
    – CodeCamper
    Dec 17, 2019 at 0:03
  • 1
    TextJoin quickly runs into the 50,000 character limit for a cell. I found that replacing all my query's with individual vlookups made the spreadsheet much more responsive, I think because fewer cells are marked for recalculation.
    – Sherwood Botsford
    Jan 26, 2020 at 12:24
  • 1
    @SherwoodBotsford I removed the textjoin from the solution, please try it out again, instead of using a textjoin I just use another query (there is no 50k limit on the headers) the answer should now work for 50k+ char reports.
    – CodeCamper
    Feb 2, 2020 at 22:35
0

Not really an answer but a pair of workarounds that sped things up:

Split the sheet

My spreadsheet had basically two main dataflows: An order system, and an inventory status system.

By splitting the sheet into two files, the inventory status file was able to use an IMPORTRANGE from the orders sheet. This command only updates every 30 minutes, but more to the point, I didn't care how long it took, as it didn't interfere with my order entry.

Replace Queries with VLookups.

Queries make sense when you are pulling in a wadge of stuff. I was using a lot of queries to return one cell of data. I suspect that the overhead for setting up a query is fairly large.

A small amount of re-arranging of data to be looked up, coupled with a new named range, and sorting the data on the lookup key sped the sheet up a lot.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.