google-apps-scriptgoogle-sheetstriggersgoogle-sheets-custom-function

How can I not execute a function at open a sheet?

I made a function on google sheets that make a request for an external link. I call the function as this:

=functionimade(params)

But, always I open the sheet, the funtion is called and the request made. How can I solve it? I want to make the request once

Solution

  • Answer:

    Unfortunately, this isn't possible to do.

    More Information:

    When a custom function is called in a Sheet, the value of the cell the return of the function - it's the formula that calls it. The return value of the function is calculated each time the Sheet is opened because the only thing that was saved was the formula; not the data.

    Also, as per the documentation, custom functions do not support the .set*() methods of SpreadsheetApp:

    Spreadsheet Service: Read only (can use most get*() methods, but not set*()).

    This means that it isn't possible to instead set the value of the cell directly using .getRange(cell).setValue(functionimade(params)) as the scope of custom functions do not allow you to set it directly.

    Workaround:

    At the end of your function, rather than returning the value, you can set the contents with the aforementioned .setValue() method, only you will have to specify the cell in the function itself:

    function functionimade(params) {
  var myReturnValue = // some cool code here

  var cell = 'A1'; // << change this
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(cell)
                                                        .setValue(myReturnValue);
}

    References:

    Related Questions: