Refresh custom function-CodePudding

I am trying to find the min value of colored cells within a google sheet. After receiving some help from this forum, I am able to get what I need. But the results don't seem to dynamic based on the change in min value of change in color of the cell. Can someone please help out?

/**
* @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];
}

CodePudding user response：

The color change does not automatically refresh the result. For this, you can add a dummy parameter, like a checkbox for example, and check/uncheck to refresh. Call this parameter by writing for instance

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

where A1 contains the checkbox

You have to change a little the script

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

and

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

the complete script will be

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];
}

CodePudding user response：

This is a classic example of an XY problem in my opinion. The real solution here is to manage the details of the colors using a code of some sort rather than manually changing the colors. Then you're not going to be stuck using AppScript to read colors, you can just use a formula to read text to find the minimum with a MINIFS() based formula.

Here is a spreadsheet explaining the solution.