CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sherwood Botsford
Sherwood Botsford

Reputation: 1997

In google sheets can I wrap a standard function in a custom function to control when it is run?

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.

https://webapps.stackexchange.com/questions/129068/recalculate-google-sheet-on-demand

Here is my original question:

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?

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.

My research:

This answer google sheets custom function built-in function 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.

This link Using Bound Google Scripts to Generate a Query Object

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.

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.

This discussion on google product forums: https://support.google.com/docs/forum/AAAABuH1jm01F-8MzzCxbY/?hl=en&gpf=%23!topic%2Fdocs%2F1F-8MzzCxbY says you can't call built-ins from scripts. But the question is almost 4 years old.

I have asked this question there too, but generally asking on Google Product Forums is a Hail Mary.

A viable solution:

I do NOT want a script that emulates the desired built-in inside the script.

Upvotes: 2

Views: 2149

Answers (1)

Wicket
Wicket

Reputation: 38254

  • A more general method than custom formulas to control recalc.

What I'm doing on project for a client is to have the "expensive" formulas saved as variables on the script and have buttons to freeze/unfreeze the certain ranges (those that have a high impact on the recalculation time.

  • The "unfreeze" button adds the formulas to the spreadsheet
  • The "freeze" button put the formulas results over the range used by the formulas
  • There is a document property that stores the frozen/unfrozen spreadsheet state
  • A sidebar is used to show the buttons the spreadsheet status.
  • An example of a script calling a built-in function.

Scripts can get values, display values, formulas, and other stuff but they can not call built-in functions.

In the case of my client, they have one array formula (IMPORTRANGE, QUERY, FILTER, ARRAYFORMULA, etc) by sheet, all the formulas that are been included are on A1. The formulas are saved in an array of objects of the following form

var formulas = [
  {
    name:'Sheet1',
    address:'A1',
    formula:'=IMPORTRANGE(...)'
  }
]

The address property, is included for future improvements.

The key code lines of the "freeze" function are

var dataRange = sheet.getDataRange();
dataRange.copyTo(dataRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

Please note that the above lines copy-paste-as-values the whole data range.

The key code lines of the "unfreze" function are

formulas.forEach(function(item){
  var sheet = spreadsheet.getSheetByName(item.name);
  sheet.clear();
  sheet.getRange(item.address).setFormula(item.formula);
});

Please note that the above lines clear the whole sheet.

Upvotes: 1

Related Questions