Get range that is the cell the function is currently running on

Question

So I'm trying to make a google spreadsheet that fills in each cell with a different colour, determined by RGB values I'll figure out later. End result will be that the sheet will be a color palette I can use elsewhere.

Problem is that I can't figure out how to make the function below (colorHex) apply the setBackgroundColor function on to the cell that it's currently running in.

I basically want to do =colorCell(254,108,72) into a cell, and that cell's background color changes into that color. So I'm sure this is a fairly simple question, but everytime I look up how to reference cells, I've yet to see an example that uses the cell the function is running in.

Help?

function colorCell(r,g,b) {
  var colorCode = colorHex(decimalToHex(r,1), decimalToHex(g,1), decimalToHex(b,1));
  // setBackgroundColor(colorCode); Where the magic will happen, hopefully
}

function decimalToHex(d, padding) {
    var hex = Number(d).toString(16);
    var hex = hex.toUpperCase();
    padding = typeof (padding) === "undefined" || padding === null ? padding = 2 : padding;

    while (hex.length < padding) {
        hex = "0" + hex;
    }

    return hex;
}

function colorHex(r,g,b) {
  var hexcode = "#";
  var hexcode = hexcode.concat(r,g,b);
  return hexcode;
}

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2016-11-01T15:27:06

Custom functions for spreadsheets must be side effect free, i.e. only return a value.
What you can do is enter the RGB or Hex code in the cell and have an onEdit script do the coloring.

function onEdit(e) {
  color = e.value.split(",");
  e.range.setBackgroundRGB(color[0], color[1], color[2]);
}

You can also run over all the cells in the sheet and try to color them based on content.

function onEdit(){
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var data = range.getValues();

  data.forEach(function(row, rowI) {
    row.forEach(function(cell, colI) {
      try {
        var color = cell.split(",");
        range.getCell(rowI+1, colI+1).setBackgroundRGB(color[0], color[1], color[2]);
      } catch(ex) {}
    });
  });
}

Get range that is the cell the function is currently running on

1 Answers