2

I was having trouble getting my script to highlight a range and after debugging for a bit, realized that there seems to be something wrong with the call of type

range.setBackground("name of color");

So, I went to GAS reference website and used the example from their page: https://developers.google.com/apps-script/reference/spreadsheet/range#setFontColor(String)

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange("B2");
cell.setFontColor("red");

This code results in !ERROR. If I force a return before setFontColor() call, all is fine. (and by fine I mean it returns from the function without an error, but of course, without having set the color).

What am I doing wrong?

azro
  • 53,056
  • 7
  • 34
  • 70
a1s2d3f4
  • 589
  • 4
  • 7
  • 18

2 Answers2

3

I can't reproduce the error. The following code work as expected:

/* CODE FOR DEMONSTRATION PURPOSES */
function setFontAndBackgroundColorCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange('B2');
  cell.setFontColor('yellow');
  cell.setBackground('red');
  cell.setValue('TEST');
}
/* CODE FOR DEMONSTRATION PURPOSES */

enter image description here

wchiquito
  • 16,177
  • 2
  • 34
  • 45
3

You havent put enough info, but i assume you are calling your function from a cell formula as a custom function. See the docs, used like that you cant change a cell other than the current cell.

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36
  • You are right, I forgot to mention that I am trying to create a custom function, which I thought I could call from a cell formula as a custom function and it would change the color of a specified range. so, I want a row to change color based on the value of a specific cell. I guess I have to figure out how to have some kind of "self-refreshing" script run on top of my doc which would check cell contents every once in a while. – a1s2d3f4 Jun 05 '14 at 14:46
  • Exactly. Use a time trigger to refresh every minute, plus i suggest you add a menu to 'format now'. Make sure to check spreadsheets native custom formatting. Its much better in the new sheets and can reference ranges – Zig Mandel Jun 05 '14 at 14:51
  • see here regading sheet's native custom formatting: http://stackoverflow.com/questions/20545961/google-spreadsheet-conditional-formatting-based-on-another-cell-value – Zig Mandel Jun 05 '14 at 14:54
  • Ok, got it now. Used this thread: http://stackoverflow.com/questions/3703676/google-spreadsheet-script-to-change-row-color-when-a-cell-changes-text Then learned about onEdit(e) – a1s2d3f4 Jun 05 '14 at 14:54
  • Yes, onEdit can do it too, and more generally onChange. There are a few issues to deal with, make sure to test it on users that are not the spreasheet owner. Also, some actions dont trigger onChange, for example "undo", so have a time trigger too to deal with those. – Zig Mandel Jun 05 '14 at 15:01
  • onChange has the advantage of catching more events (onEdit wont catch deleted rows for example), and it has FULL oauth mode (see https://developers.google.com/apps-script/understanding_events) – Zig Mandel Jun 05 '14 at 15:03