Utilities.formatstring With Positive/negative Numbers

I want to display my numeric string data with a rich format, applying a different color when the number is positive or negative. My current code is: var url = 'https://docs.google.

Solution 1:

In your script, formattedData is the string value. When you want to change the font color, in this case, the value is required to be set to the cell. So in this case, I would like to propose the method for changing the font color in the cell.

If theSheet in your script is used, the sample script is as follows.

Sample script:

var calcData = -0.1234;
theSheet
  .getRange("A1")
  .setValue(calcData)
  .setNumberFormat('+0.00%;-0.00%;0.00')
  .setFontColor(calcData > 0 ? "green" : "red");

• In this sample script, -12.34% is put to the cell "A1" and the font color is red.

• When var calcData = 0.1234; is used, +12.34% is put to the cell "A1" and the font color is green.

• When you want to use Utilities.formatString, the sample script is as follows.

  var calcData = -0.1234;
    var formattedData = Utilities.formatString("%+1.2f%", calcData * 100);
    theSheet
      .getRange("A1")
      .setValue(formattedData)
      .setFontColor(calcData > 0 ? "green" : "red");
  

Note:

• This is a simple sample script. So please modify it for your actual situation.

References:

• setNumberFormat(numberFormat)
• setFontColor(color)
• formatString(template, args)