刷新自定义函数

Question

我正试图在谷歌单张中找到彩色单元格的最小值。在从这个论坛得到一些帮助后，我能够得到我需要的东西。但基于细胞颜色变化的最小值的变化，结果似乎并不是动态的。有人能帮忙吗？

/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function minValColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();

var rangeA1Notation = formula.match(/\((.*)\,/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();

var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();

var colourarray=[]

for(var i=0;i<bg.length;i++){
     for(var j=0;j<bg[0].length;j++){
        if( bg[i][j] == color ){
           colourarray.push(values[i][j])                   
        }
     }
}

   var smallest = colourarray.sort((a, b) => a - b);
  // Logger.log("DEBUG: the min is "+smallest[0])
   return smallest[0];
}

Answer 1

颜色更改不会自动刷新结果。为此，您可以添加一个虚拟参数，例如复选框，并选中/取消选中以刷新。例如，通过写入来调用此参数

=minValColoredCells(___________,___________,$A$1)

其中A1包含复选框

你得稍微改变一下剧本

var rangeA1Notation = formula.match(/\((.*)\)/).pop().split(',')[0];

和

var colorCellA1Notation = formula.match(/\((.*)\)/).pop().split(',')[1];

​

​

完整的脚本将是

function minValColoredCells(countRange, colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  var formula = activeRange.getFormula();
  var rangeA1Notation = formula.match(/\((.*)\)/).pop().split(',')[0];
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();

  var colorCellA1Notation = formula.match(/\((.*)\)/).pop().split(',')[1];
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();

  var colourarray = []

  for (var i = 0; i < bg.length; i++) {
    for (var j = 0; j < bg[0].length; j++) {
      if (bg[i][j] == color) {
        colourarray.push(values[i][j])
      }
    }
  }

  var smallest = colourarray.sort((a, b) => a - b);
  return smallest[0];
}

Answer 2

在我看来，这是一个典型的XY问题示例。这里真正的解决方案是使用某种代码来管理颜色的细节，而不是手动更改颜色。然后，您将不会被困在使用AppScript读取颜色，您只需使用公式读取文本，以找到最小的基于MINIFS()的公式。

以下是解释解决方案的电子表格。