Google表格功能可主动检查单元格 [英] Google Sheets Function to Actively Check Cell

问题描述

我有一个正在使用的函数，该函数检查颜色的单元格并在另一列中返回是/否".但是，在应用功能之后，随后又更改了单元格的颜色，该功能不再适用.

I've a function I'm using that checks the cell of the color and returns Yes/No in another column. However, after applying the function, and then changing the color of the cell afterwards the function isn't applied anymore.

例如，该函数被应用并返回是"，那么如果我将单元格的颜色更改为白色，它就不会返回否".是否可以具有主动检查单元格的功能，或者我只需要在每次单元格颜色更改时重新应用该功能?

For example the function is applied and returns "Yes", then if I change the color of the cell to white it doesn't return to "No". Is it possible to have the function to actively check the cell, or I just need to reapply the function each time the color of the cell changes?

function GetCellColorCode(input) {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var cell = ss.getRange(input); 
  var result = cell.getBackground(); 

  var green = '#d9ead3';
  var white = '#ffffff';

  if (result == green) {
    return 'Yes';
  } else {    
    return 'No';
  }  

}

推荐答案

• 您要在更改背景颜色时刷新单元格中 = GetCellColorCode(###)的自定义公式.
• You want to refresh the custom formulas of =GetCellColorCode(###) in the cells when the background color is changed.

为了实现您的目标，我建议使用TextFinder和OnChange事件触发器刷新公式.

In order to achieve your goal, I would like to propose to refresh the formulas using TextFinder and the OnChange event trigger.

请复制并粘贴以下脚本.

Please copy and paste the following script.

function onChange(e) {
  if (e.changeType === "FORMAT") {
    const formula = "=GetCellColorCode";  // Function name of your custom function.

    const sheet = e.source.getActiveSheet();
    const tempFormula = "=sampleFormula";
    sheet.createTextFinder("^\\" + formula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(tempFormula);
    sheet.createTextFinder("^\\" + tempFormula).matchFormulaText(true).useRegularExpression(true).replaceAllWith(formula);
  }
}

• 在此示例脚本中，使用TextFinder将 = GetCellColorCode 替换为其他名称，然后将其修改为 = GetCellColorCode .这样，可以刷新自定义公式.
• 当您在脚本编辑器中直接运行 onChange 函数时，会发生错误，因为此函数使用OnChange事件触发器的事件对象.请注意这一点.
• In this sample script, =GetCellColorCode is replaced to other name using TextFinder, and then, it is modified to =GetCellColorCode. By this, the custom formula are refreshed.
• When you directly run the function of onChange at the script editor, an error occurs, because this function uses the event object of OnChange event trigger. Please be careful this.

请为函数安装OnChange事件触发器onChange .

1. 从脚本编辑器中，选择编辑">当前项目的触发器" .
2. 点击添加触发器.
3. 在选择要运行的功能下，选择要触发的功能的名称.
   • 在这种情况下，请选择 onChange .
1. From the script editor, choose Edit > Current project's triggers.
2. Click Add Trigger.
3. Under Choose which function to run, select the name of function you want to trigger.
   • In this case, please select onChange.

3.试运行.

完成上述流程后，请更改单元格的背景颜色.这样，将触发OnChange事件触发器，并运行函数 onChange .然后，刷新 GetCellColorCode 的自定义公式.

3. Test run.

After above flow was done, please change the background color of the cell. By this, the OnChange event trigger is fired and the function onChange is run. Then, the custom formula of GetCellColorCode is refreshed.

在此脚本中，即使复制并粘贴了单元格，也不会运行OnChange vente触发器.请注意这一点.在这种情况下，请修改背景颜色.

In this script, even when the cell is copied and pasted, the OnChange vente trigger is not run. Please be careful this. In this case, please modify the background color.

• 在此答案中，假设您的自定义公式名称为 GetCellColorCode .因此，当您更改函数名称时，也请修改上面的脚本.请注意这一点.
• In this answer, it supposes that your custom formula name is GetCellColorCode. So when you changed the function name, also please modify above script. Please be careful this.
• 可安装触发器
• 事件对象
• Class TextFinder

这篇关于Google表格功能可主动检查单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！