Using fontcolor() in Google Sheets Returns HTML in Cell

Question

In my sheet, I want to automatically take negative numbers, change the color to red, and remove the '-' character. The number in a cell can fluctuate between positive and negative, based on data in other cells eg...

A1 = "some number"
A2 = "some number"
A3 = A1 + A2 (number should be red if value is a negative, and '-' 
     should be stripped off. Otherwise number should be black.

I've written an apps script that should do this

function negativesToRed(num) {
      if (num < 0) {
        var newNum = (num * -1).toString();
        var redNum = newNum.fontcolor("red");
      }
      return redNum;
    }

but when I use it, it returns...

<font color="red">605</font>

...in the cell.

NOTE: A3 needs to turn red when data inputted in A1 or A2 changes it to a negative. onEdit() will only work if user manually changes A3 to a negative number. AND if the value in A3 is used elsewhere, it needs to be treated as a negative number (removing '-' is just for display).

Is there anyway to get this to work?

Answer 1

You can only return value from the custom script function. In order to change the cell color, you can try adding a trigger, such as onEdit, and then apply setFontColor to a cell.

Answer 2

Yes, there is a way to do this in AppsScript for display purposes. Instead of changing the value of the cell, you can just change the way the number format is displayed. Set positive numbers to display as black, and negative numbers to display as red without the negative (-) sign showing. For example, to change the display of just column 10:

var ss = SpreadsheetApp.openById('sheetIDhere').getSheetByName('sheetNameHere');
ss.getRange(2, 10,ss.getLastRow()-1, 1).setNumberFormat('[Black]#,##0;[Red]#,##0;');             

You can also update the number format of the cell/column/sheet by selecting the columns you want to change the view on in the Sheet itself, click on Format -> Number -> More Formats -> Custom Format -> and entering [Black]#,##0;[Red]#,##0; into the custom area.

Answer 3

By using onEdit you can manipulate the cell, and not only the value.

Something like this, unless you want to restrict it more.

/** @OnlyCurrentDoc */
function onEdit(e) {
  var range = e.range;
  if (e.value < 0) {
        range.setValue(e.value * -1);
        range.setFontColor('red');
  }
}

Answer 4

onEdit() has limitations and might not trigger if the cell values are changed externally, e.g. through script execution.

For this case, consider to install a time-driven trigger instead, which runs e.g. every minute, if desired.