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

Question

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

Answer 1

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:

• Custom functions in Google Sheets | Apps Script
• Class Range | Apps Script - Method .setValue(value)

---

Related Questions:

• Run script only on click on button instead with open the sheets