title: In google sheets can I wrap a standard function in a custom function to control when it is run?
body: <p>I originally asked this on WebApps, thinking that I was just missing a google trick.  There I received the answer that it would require a custom function.</p>
  
  <p><a href="https://webapps.stackexchange.com/questions/129068/recalculate-google-sheet-on-demand">https://webapps.stackexchange.com/questions/129068/recalculate-google-sheet-on-demand</a></p>
  
  <p>Here is my original question:</p>
  
  <blockquote>
    <p>I have the following formula in a sheet:</p>
  
  <pre><code>=if(E1="HOLD",,query(Cust_Orders!B6:Z5000,"Select Y,G,I,H,K where H&gt;0  "))
  </code></pre>
    
    <p>With E1 being a drop down with values HOLD and FETCH</p>
    
    <p>The recalculation when I go to FETCH takes about 13 seconds.</p>
    
    <p>But when it is in HOLD, the query doesn't exceute, and everything goes
    blank. Not what I want.</p>
    
    <p>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.</p>
    
    <p>Google spreadsheet recalc settings all are 'on change and <em>foo</em>'</p>
    
    <p>Is there a way to do this?</p>
  </blockquote>
  
  <p>Custom functions only update when one of their parameters changes. So if the function only depends on the HOLD/FETCH cell, but executes the formula is the query cell, I think I win.</p>
  
  <p>My research:</p>
  
  <p>This answer <a href="https://stackoverflow.com/questions/43827450/google-sheets-custom-function-built-in-function">google sheets custom function built-in function</a> is specific in telling the OP how to do what he wants in  the script language. I suspect that rewriting the query in appscript would not be a net win.</p>
  
  <p>This link <a href="https://stackoverflow.com/questions/51327982/using-bound-google-scripts-to-generate-a-query-object">Using Bound Google Scripts to Generate a Query Object</a> </p>
  
  <p>was proposed as a solution, but this in effect is rewriting my built-in function within the script.  I want to use scripts as rarely as possible, and as generically as possible, as it makes long term maintenance and modification easier.</p>
  
  <p>The query function above is an example.  I am looking for a more general solution that allows me to use an arbitrary formula using the same script.</p>
  
  <p>This discussion on google product forums: <a href="https://support.google.com/docs/forum/AAAABuH1jm01F-8MzzCxbY/?hl=en&amp;gpf=%23!topic%2Fdocs%2F1F-8MzzCxbY" rel="nofollow noreferrer">https://support.google.com/docs/forum/AAAABuH1jm01F-8MzzCxbY/?hl=en&amp;gpf=%23!topic%2Fdocs%2F1F-8MzzCxbY</a> says you can't call built-ins from scripts.  But the question is almost 4 years old.</p>
  
  <p>I have asked this question there too, but generally asking on Google Product Forums is a Hail Mary.  </p>
  
  <p>A viable solution:</p>
  
  <ul>
  <li>An example of a script calling a built-in function.</li>
  <li>A link to an add-on that allows recalculation of a range to be toggled on/off</li>
  <li>A more general method than custom formulas to control recalc.</li>
  </ul>
  
  <p>I do NOT want a script that emulates the desired built-in inside the script.</p>