Help for programming a custom function for spreadsheet

Docs
, ,
1

Hello.

I’m trying to program a custom function for a spreadsheet. I want to create a function that takes a cell range and a reference cell as input.
This function should calculate and return the number of cells in the range with the same background color as the reference cell.

Here’s the code I wrote:

(function()
{
	/**
	 * Function that returns the number of color cell in selection
	 * @customfunction
     * 
	 * @param {range} rangeCell The Cell range (required).
     * @param {range} cellref The color cell ref (required).
     * @returns {number} nbcolor The number of color cell on selection.
	*/
	function colorcounter(rangecell, cellref) {
        // save ref color
        const colorref = cellref.GetFillColor();
        // number of color detected
        let nbcolor = 0;
        let nbrow = 0;
        let nbcol = 0;

        // transform rangecell to Range type
        //let range = Api.GetRange(rangecell);

        // get Row and Col size
        nbrow = rangecell.GetRow();
        nbcol = rangecell.GetCol();

        // explor tab
        for (let r = 0; r < nbrow; r++) {
            for (let c = 0; c < nbcol; c++) {
                // save cell color
                let colorcell = rangecell(r, c).GetFillColor();
                
                if (colorcell === colorref) {
                    nbcolor++;
                }
            }
        }

        return nbcolor;
    }

	Api.AddCustomFunction(colorcounter);
})();

Unfortunately, it doesn’t work; it’s not recognized in the list of functions.
I’m not very good with JavaScript. Thank you in advance for your help.

2

Hello @dragonlost

Custom functions are not suited for such goal, they can calculate, concatenate, compare data (values) in the cells and much more, but they cannot address ranges like A1:B5 to read their positions on the sheet. As can argument, they can only have numbers, strings, booleans, any of them and several of those at the same time. There is no such argument as range available.

For your goal, you will need to use a macro instead. For instance, a macro like that:

let rangeCell = "A1"; // Target cell, from which color ref is taken
let cellref = "C2:E5"; // Array of cells to look up for the color
let finalCell = "B1"; // Destination cell into which the result will be inserted
let nbcolor = 0;

let worksheet = Api.GetActiveSheet();
let targetCell = worksheet.GetRange(rangeCell);
let targetColor = targetCell.GetFillColor();
let destCells = worksheet.GetRange(cellref);
let cellColorCode;

destCells.ForEach(function (range) {
    let cellColor = range.GetFillColor();

    if (cellColor!== "No Fill"){
        cellColorCode = cellColor.GetRGB();
    } else {
        cellColorCode = null;
    }

    if (cellColorCode && cellColorCode === targetColor.GetRGB()) {
        nbcolor++;
    }
});
worksheet.GetRange(finalCell).SetValue("Total: " + nbcolor);

For instance, here is the sheet I used for that macro and the result:

image
image402×166 5.45 KB