Can I use custom js function for FILTER cell function in Google Sheets

0
votes

I'm trying to use custom js function from the script as condition for FILTER cell function in Google Sheets.

Example: =FILTER(A1:A25; colorsame(A1:A25; $A$10))

colorsame returns true/false (one value or array, based on range size).

The custom function works well alone, like =colorsame(A1:A25; $A$10) fill the column. But in =FILTER() above I always have this error:

FILTER has mismatched range sizes. Expected row count: 25. column count: 1. Actual row count: 1, column count: 1.

I've tried googling this problem, but only got solutions for FILTER based on built-in google sheets functions. Also I try to intergrate this function with Filter and Conditional Formatting "custom formula" field, but without success.

Custom functions code:

color returns the background color(s) of provided cell or range.

function color(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get 'input' address by metascanner
  var formula = ss.getActiveRange().getFormula();
  var rangeA1Notation = formula.match(/\((.+)\)/).pop();

  var cell = ss.getRange(rangeA1Notation);
  var bg = cell.getBackgrounds();
  return bg;
}

colorsame returns the truth table, which shows is the background color of the input range is the same as background color of color cell.

function colorsame(input, color) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get 'input' address by metascanner
  var formula = ss.getActiveRange().getFormula();
  var matches = formula.match(/\((.+);(.+)\)/);
  var rangeA1Notation = matches[1];
  var colorA1Notation = matches[2].trim();

  var color = ss.getRange(colorA1Notation).getBackground();
  var bgs = ss.getRange(rangeA1Notation).getBackgrounds();
  var truthTable = bgs.map(function(bg) { return bg == color });
  return truthTable;
}
2
javascriptgoogle-sheets

2 Answers

0
votes

It could be useful to see the code of your colorsame function, but meanwhile, have you tried with 

=ArrayFormula(FILTER(A1:A25; colorsame(A1:A25; $A$10)))
0
votes

You can return array of boolean values from custom function, which will indicate what cells will be shown after filtering.

My problem was the try to parse cell content with regexp to retrieve cell address. It works for formulas like =colorsame(A1:A25; $A$10), but not for using inside FILTER.

This version works, exclusively with FILTER:

function colorsame(input, color) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // get 'input' address by metascanner
  var formula = ss.getActiveRange().getFormula();
  var matches = formula.match(/colorsame\((.+?);(.+?)\)/);

  var rangeA1Notation = matches[1].trim();
  var colorA1Notation = matches[2].trim();

  var color = ss.getRange(colorA1Notation).getBackground();
  var isSame = function(col) { return col === color; };

  var bgs = ss.getRange(rangeA1Notation).getBackgrounds();
  var truthTable = bgs.map(function(bg) { return bg.map(isSame); });

  return truthTable;
}