每次打开电子表格时，是否停止自定义函数重新计算? [英] Stop a custom function from recalculating every time the spreadsheet is open?

问题描述

我有一个自定义函数，该函数使用访存将某些信息保存到电子表格中.问题是，它打算在电子表格中多次使用，从各个页面获取.随着使用量的增加，每次打开电子表格时不必要地重新获取信息都将成为越来越多的问题.

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?

推荐答案

也许有一种方法可以判断这是手动编辑还是自动重新计算，或者只是完全禁用该行为?

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

不，不.

一种可能的解决方法是在电子表格的某个单元格中具有一个开关":例如，如果单元格A1具有"Update"，则自定义函数将正常重新计算；否则他们什么都不做.该单元格(A1)可以通过下拉菜单进行数据验证，以便在状态之间轻松切换.自定义函数将被修改为包括以下内容:

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

这篇关于每次打开电子表格时，是否停止自定义函数重新计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！