Google Apps Script getBackgrounds for colour custom function

Question

How do you write a custom function to get a cell's colour, or an array for a range's colour? There's an example given by Google for how to optimise your functions when they use ranges, but how do you get data about the range rather than do maths on it?

Example given:

function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Basic idea:

function COLOUR(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell.getBackground())) :
      input.getBackground();
}

or maybe like this?

function COLOUR(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => getBackground(cell))) :
      getBackground(input);
}

This code doesn't actually work, the first gives a type error and says you can't read getBackground of null, and the second one says getBackground is not defined, but hopefully you get what I'm trying to do. Help?

Answer 1

Issue:

getBackground() is a method applied to a range object. In your code, cell is a value.

Solution:

• To get the background colors of a range, you need to get the range object of the particular cell references and then apply getBackgrounds to get the hex color codes of your input.

You just need to pass the input as a string though e.g. =COLOUR("A1:B2").

function COLOUR(input) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const bcolors = sh.getRange(input).getBackgrounds(); 
  return bcolors;
}

Example output:

If you want to get the color names, then you need to map the hex color codes (in bcolors) to actual color names and return the names instead.