Google Sheets Custom functions - simplifying calling, with convenience functions

Question

I would like to have a custom function handle most of the complexity of this type of formula, not have to type it in each cell.

=sparkline({B3-$B$2,C3-B3},{"charttype","bar"; "color1", "white";"color2","blue";"max",$D$2})

From reading the docs, it appears that you cannot invoke a built-in function from within a custom function. sigh!

My next thought would be: can I have a custom function that returns the parameters required by "SPARKLINE", so the formula I enter in the call would look like

=sparkline({B3-$B$2,C3-B3}, MYGETPARAMS())

//  * @customfunction
function MYGETPARAMS() {
  return {"charttype":"bar", "max":20, "color1": "white", "color2": "green"};
}

However the params don't appear to be in js dict format. Any ideas?

Hmm. I see that setFormula() offers a way out.. https://stackoverflow.com/questions/26805309/how-to-evaluate-a-spreadsheet-formula-within-a-custom-function — RoyM, May 30 '19 at 14:17

score 1 · Accepted Answer · answered May 30 '19 at 16:25

You don't have to type it in each cell. You can set up these settings elsewhere in your sheet(say,Y2:Z4). Then, You can use it like,

=sparkline({B3-$B$2,C3-B3},Y2:Z4)

Where Y1:Z4 looks like:

    CHART SETTINGS
    charttype    bar
    color1    white
    color2    blue
    max    =$D$2

Google Sheets Custom functions - simplifying calling, with convenience functions

1 Answers1