Google Apps script - return multiple text colors-CodePudding

I have a simple custom function on Google Apps script, returning two lines of text:

function RPE_MAX() {
  const values = ['one', 'two']
  const first = values[0]
  const second = values[1]
  const formatted = `${first} \n ${second}`
  return formatted
}

How can I return the first line in one font color and the second one in another font color?

CodePudding user response：

Using RichTextValue:

function RPE_MAX() {
  const red = SpreadsheetApp.newTextStyle().setForegroundColor("red").build();
  const blu = SpreadsheetApp.newTextStyle().setForegroundColor("blue").build();
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const values =[['one', 'two','three','four'],['five','six','seven','eight']]; 
  sh.getRange("A1").setValue(`${values[0].join(' ')}\n${values[1].join(' ')}`);
  SpreadsheetApp.flush();
  const txt = sh.getRange("A1").getDisplayValue();
  const v = SpreadsheetApp.newRichTextValue()
    .setText(txt)
    .setTextStyle(0,txt.indexOf('\n'),red)
    .setTextStyle(txt.indexOf('\n') 1, txt.length,blu)
    .build();
    sh.getRange('A1').setRichTextValue(v)
}

This also works if you don't want to get your text from the spreadsheet which is normally what I do.

function RPE_MAX() {
  const red = SpreadsheetApp.newTextStyle().setForegroundColor("red").build();
  const blu = SpreadsheetApp.newTextStyle().setForegroundColor("blue").build();
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const values =[['one', 'two','three','four'],['five','six','seven','eight']]; 
  const txt = `${values[0].join(' ')}\n${values[1].join(' ')}`;
  const v = SpreadsheetApp.newRichTextValue()
    .setText(txt)
    .setTextStyle(0,txt.indexOf('\n'),red)
    .setTextStyle(txt.indexOf('\n') 1, txt.length,blu)
    .build();
    sh.getRange('A1').setRichTextValue(v)
}

CodePudding user response：

You are describing a custom function. Custom functions can only return values and cannot return cell formats such as font colors. What you are asking cannot be done with a custom function.

Use a regular function like the one Cooper shows, and call it through some other means, such as a button, a custom menu item, a sidebar or a trigger.