Google Sheets Script Parameter for e.g. Button

Question

Is it possible to pass parameters on a signed script?

I have a spreadsheet and made a couple of buttons signing functions to each.

Sample function:

function foo(){
    return "bar";
}

Calling this function on a cell

=foo()

returns me bar, signing this function to a button

foo

returns me nothing but of course it works. I mean the script cant return a string to an image (whats actually my button). Anyways...

Sample function with parameter:

function foo(bar){
    return bar;
}

calling the script in a cell

=foo('hello world')

returns me hello world as expacted

Signing this to my button

foo('hello world')

causes an error because the button cant find the script. He's searching for a function name foo('hello world') but (...) has non to do with the function.

So how can I pass params when signing script?

At the moment I have 26 functions (all the same, only 1 param changes) to solve this. But with passing a parameter I could do all this with 2 functions instead of 26.

score 5 · Answer 1 · answered Mar 17 '16 at 14:14

You can't pass parameters when using a custom function via assigning it to a drawable image. It only works when the custom function is called from a cell. Possible workarounds is to store your parameters on certain sheets and cells then retrieve them via getRange() and getDisplayedValue().

Other possible workarounds are posted in a Google product help forum which can be found here

score 2 · Answer 2 · answered Sep 02 '20 at 22:17

I found the best option to create separate helper methods for each individual button, which then pass the correct parameter to the generic method.

In the below example you would bind to the buttons the city specific method, for example for "Helsinki" button "updateAvailabilityInfoHelsinki".

function updateAvailabilityInfoHelsinki() {
  updateAvailabilityInfo("Helsinki");
}

function updateAvailabilityInfoPorvoo() {
  updateAvailabilityInfo("Porvoo");
}

function updateAvailabilityInfo(citySheet) {
    // Do something with citySheet parameter
}

nice to see that this solves your problem, or at least is an solution for you. for me this is not a solution since than every button has its own function again. This reduces the code and was also some i did, but in the end i am used to an other behavior of functions and what i expact — Dwza, Mar 08 '21 at 16:31

Google Sheets Script Parameter for e.g. Button

2 Answers2