Setting the cell's background color with a custom function (script)

Question

I want to set a background color for a cell based on a color code stored in another cell.

I wrote a following google script to implement this:

function colorCode2Background(code) {
  if (typeof code != 'string') {
  return null;
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  // Returns the active cell
  var cell = sheet.getActiveCell();
  return cell.setBackground(code);
}

The problem is that when I set the cell's formula to =colorCode2background(%CELL_NUMBER%) where cell's value is a color code (i.e. #ff00ff), I get an error: "You have no permission to call function setBackground (line 12)", although I granted all the permissions to my script.

JPV JPV · Accepted Answer · 2016-09-19T15:36:44

Every custom function must return a value to display. That is not the case in your function.

Setting the cell's background color with a custom function (script)

1 Answers