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?


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

  • You must harness the awesome power of iteration! – CodeCamper Dec 13 '19 at 3:22

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(textjoin("\",false,query(substitute(Cust_Orders!B6:Z5000," ","_"),"Select Col24,Col6,Col8,Col7,Col10 where Col7>0",countif(H5:H5000,">0")+1))))

Then in cell G1 put the following:

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

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.

  • 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 '19 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 '19 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 '19 at 0:03
  • Thanks. Will experiment. Seems such a kluge though. – Sherwood Botsford Dec 18 '19 at 3:59
  • @SherwoodBotsford it's very simple and straightforward for single cell outputs, so the silly part is that we need to force query into a single helper cell. Think of it this way, circular references allow you to use a single cell as a variable to save values, you cannot have a circular reference array without dragging formulas manually because google sheets has no way to detect a blank cell contain a circular reference output, Excel does not have this limitation and is a design flaw of Google Sheets. – CodeCamper Dec 18 '19 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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