自定义功能不会随着输入的更改而刷新 [英] Custom function won't refresh as inputs are changed

问题描述

我有一个自定义函数，可以找到另一个单元格的值并显示它。更改源单元格后，该功能不会反映。

I have a custom function that finds the value of another cell and displays it. When the source cell is changed, the function does not reflect.

> https://docs.google.com/spreadsheets/d/1wfFe__g0VdXGAAaPthuhmWQo3A2nQtSVUhfGBt6aIQ0/edit?usp=sharing

刷新Google表格

function findRate() {
  var accountName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,1).getValue(); //determine the account name to use in the horizontal search
  var rateTab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Rates'); //hold the name of the rate tab for further dissection
  var rateNumColumns =rateTab.getLastColumn(); //count the number of columns on the rate tab so we can later create an array
  var rateNumRows = rateTab.getLastRow(); //count the number of rows on the rate tab so we can create an array
  var rateSheet = rateTab.getRange(1,1,rateNumRows,rateNumColumns).getValues(); //create an array based on the number of rows & columns on the rate tab
  var currentRow = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getRow(); //gets the current row so we can get the name of the rate to search
  var rateToSearch = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(currentRow,1).getValue(); //gets the name of the rate to search on the rates tab
  for(rr=0;rr<rateSheet.length;++rr){
    if (rateSheet[rr][0]==rateToSearch){break} ;// if we find the name of the 
      }
  for(cc=0;cc<rateNumColumns;++cc){
    if (rateSheet[0][cc]==accountName){break};
      }
  var rate = rateSheet[rr][cc] ; //the value of the rate as specified by rate name and account name
  return rate;
}

如果我在汇率标签中更改了汇率，则需要使用自定义功能识别新汇率并更新其值

If I change a rate in the rate tab, I need the custom function to recognize the new rate and update its value

推荐答案

• 您要重新计算<$的自定义函数c $ c> = findRate（），当工作表名称为 Rates 的单元格被编辑时。


• You want to recalculate the custom function of =findRate(), when the cells of the sheet name of Rates are edited.

如果我的理解是正确的，那么如何添加以下示例脚本？请考虑这只是几个答案之一。

If my understanding is correct, how about adding the following sample script? Please think of this as just one of several answers.

为了重新计算自定义函数，在此答案中， = findRate（）的公式被运行OnEdit事件触发器的脚本覆盖（在这种情况下，这是简单的触发器。）。由此，执行重新计算。但是，当该公式直接由同一公式替换时，将不执行重新计算。因此，我使用了以下流程。

In order to recalculate the custom function, in this answer, the formula of =findRate() is overwritten by the script running with the OnEdit event trigger (in this case, it's the simple trigger.). By this, the recalculate is executed. But, when the formula is directly replaced by the same formula, the recalculate is not executed. So I used the following flow.



1. 检索所有具有 = findRate（）从预计收入表中。


2. 清除范围公式。


3. 将公式放入范围


1. Retrieve all ranges of cells which have the formula of =findRate() from the sheet of "Projected Revenue".
2. Clear the formulas of the ranges.
3. Put the formulas to the ranges.

通过此流程，当编辑费率工作表的单元格时， onEdit（）重新计算> = findRate（）。

By this flow, when the cell of the sheet of "Rates" is edited, the custom function of =findRate() is recalculated by automatically running onEdit().

请复制以下脚本并将其粘贴到脚本编辑器中。 然后，请编辑工作表名称为费率的单元格。这样， onEdit（）由OnEdit事件触发器自动运行。

Please copy and paste the following script to the script editor. Then, please edit the cells of sheet name of Rates. By this, onEdit() is automatically run by the OnEdit event trigger.

function onEdit(e) {
  var range = e.range;
  if (range.getSheet().getSheetName() == "Rates" && range.rowStart > 1 && range.columnStart > 1) {
    var sheetName = "Projected Revenue"; // If you want to change the sheet name, please modify this.
    var formula = "=findRate()";// If you want to change the function name, please modify this.

    var sheet = e.source.getSheetByName(sheetName);
    var ranges = sheet.createTextFinder(formula).matchFormulaText(true).findAll().map(function(e) {return e.getA1Notation()});
    sheet.getRangeList(ranges).clearContent();
    SpreadsheetApp.flush();
    sheet.getRangeList(ranges).setFormula(formula);
  }
}





注意：






• onEdit（e）由OnEdit事件触发器运行。因此，当您直接运行 onEdit（e）时，会发生错误。请注意这一点。


• 在此示例脚本中，作为示例，即使编辑了费率表的行1和列 A，自定义功能也不会重新计算。如果要修改此设置并限制要编辑的范围，请修改上面的脚本。



Note:

• onEdit(e) is run by the OnEdit event trigger. So when you directly run onEdit(e), an error occurs. Please be careful this.
• In this sample script, as a sample, even when the row 1 and column "A" of the sheet of "Rates" are edited, the custom function is not recalculated. If you want to modify this and give the limitation of range you want to edit, please modify the above script.


• 简单触发器


• Class TextFinder


• 类别范围列表


• flush（）


• Simple Triggers
• Class TextFinder
• Class RangeList
• flush()

如果我误解了您的问题，但这不是您想要的结果，

If I misunderstood your question and this was not the result you want, I apologize.

来自 TheMaster的评论已反映到脚本中。当可以使用 sheet.createTextFinder（formula）.matchFormulaText（true）.replaceAllWith（formula）时，我还认为流程成本将大大降低。但是在我的环境中，即使使用 flush（），似乎也需要清除一次公式以刷新自定义函数。因此，我已经提出了上述流程。

The proposal from TheMaster's comment was reflected to the script. When sheet.createTextFinder(formula).matchFormulaText(true).replaceAllWith(formula) can be used, also I think that the process cost will be much reduced. But in my environment, it seemed that the formulas are required to be cleared once to refresh the custom function, even if flush() is used. So I have proposed above flow.

但是，现在我可以注意到使用TextFinder的 replaceAllWith（）的解决方法。所以我想添加它。此解决方法的流程如下。

But, now I could notice a workaround using replaceAllWith() of TextFinder. So I would like to add it. The flow of this workaround is as follows.



1. 替换所有 = findRate（）使用 replaceAllWith（）到预计收入表格中的值。.
   
   
   
   
   • 在这种情况下，作为测试用例，公式将替换为 sample 。
   
   
1. Replace all values of =findRate() to a value in the sheet of Projected Revenue using replaceAllWith()..
   • In this case, as a test case, the formulas are replaced to sample.

通过此流程，我可以确认 = findRate（）被重新计算。而且，在这种情况下，似乎不需要 flush（）。

By this flow, I could confirm that =findRate() is recalculated. And also, it seems that flush() is not required for this situation.

请复制以下脚本并将其粘贴到脚本编辑器中。 然后，请编辑工作表名称为费率的单元格。这样， onEdit（）由OnEdit事件触发器自动运行。

Please copy and paste the following script to the script editor. Then, please edit the cells of sheet name of Rates. By this, onEdit() is automatically run by the OnEdit event trigger.

function onEdit(e) {
  var range = e.range;
  if (range.getSheet().getSheetName() == "Rates" && range.rowStart > 1 && range.columnStart > 1) {
    var sheetName = "Projected Revenue";
    var formula = "=findRate()";
    var tempValue = "sample";

    var sheet = e.source.getSheetByName(sheetName);
    sheet.createTextFinder(formula).matchFormulaText(true).replaceAllWith(tempValue);
    sheet.createTextFinder(tempValue).matchFormulaText(true).replaceAllWith(formula);
  }
}

这篇关于自定义功能不会随着输入的更改而刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！