1

I have a custom function that uses fetch to save some information to a spreadsheet. The problem is, it's meant to be used many times on the spreadsheet, fetching from various pages. As the amount of uses increases, needlessly re-fetching information every time the spreadsheet opens will become more and more of a problem.

Is there any way to avoid this default behavior? I still want it to recalculate every time the cell is edited, even if the parameters are exactly the same. Is there perhaps a way to tell if it's a manual edit vs. automatic recalculation, or simply disable the behavior entirely?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Azk
  • 141
  • 7
  • Can you just remove the custom function and perform your calculation and data collection via the onEdit trigger hooked up to a script function and return the result to the edited cell. – Cooper Sep 13 '17 at 02:43
  • @Cooper Yes, I've used that method in the past when fetching data, but I was hoping to find a method that would allow me to make it usable as a custom function. – Azk Sep 13 '17 at 03:48

1 Answers1

1

Is there perhaps a way to tell if it's a manual edit vs. automatic recalculation, or simply disable the behavior entirely?

No and no.

One of possible workarounds is to have a "switch" in some cell of the spreadsheet: say, if cell A1 has "Update" then custom functions recalculate normally; otherwise they do nothing. That cell (A1) can have data validation with dropdown, for easy switching between states. The custom functions would be modifying to include the following:

function customFunction(...) {
  var currentStatus = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
  if (currentStatus != "Update") {
    return; 
  }
  // the rest of function