Problem changing the color of a cell partially with Apps Script

helios paniagua 0
Viewed 50

I need to put 2 rows of data in a single cell, and color code them like so: [endgoal]1

So I decided to make a custom function with apps script, so far i've only been able to make the text be on top of each other, but can't for the life of me make it change color, so I was hoping to get some help here.

The function I made is the following (it's extremely simple but i'm a noob)

function pileUp(val1,val2) {
  return val1+"\n"+val2
}

I've been searching here and found some people partially coloring cells, but none I tried worked for me, would appreciate any help.

P.D: The length of both bottom and top rows varies cell by cell, so anything that uses a range with a fixed amount of characters to color won't work

P.D.2: I do realize I didn't post my approach to actually changing the color, here it is:

function pileUp(val1,val2) {   
  var color="#b82f2f";   
  var format =SpreadsheetApp.newTextStyle()
    .setForegroundColor(color)
    .build();
  var final=SpreadsheetApp
    .newRichTextValue()
    .setText('val1"\n"+val2')
    .setTextStyle(0, 3, format);     
return final }

I intended to see if this worked, then change the 3 in the penultimate line for a variable that was equal to the length of var1, but it didn't work.

custom-function richtext google-sheets google-apps-script
1 Answers

I believe your goal is as follows.

  • You have the values like sample1\nsample2 in the cells. In this case, 2 lines are existing in a cell.
  • You want to change the text style of the 2nd line to the font color of #b82f2f.
  • You want to achieve this using Google Apps Script. In this case, you can directly run the script without using the custom function.

In this case, how about the following sample scripts?

Sample script 1:

In this case, the script can be run from the custom menu. When you reopened Spreadsheet, the custom menu is created by OnOpen trigger.

When you use this script, please select the cell and run the script from the custom menu. By this, the script works.

function onOpen() {
  SpreadsheetApp.getUi().createMenu("sample").addItem("Run script", "myFunction").addToUi();
}

function myFunction() {
  var color = "#b82f2f"; // This is from your script.

  const range = SpreadsheetApp.getActiveRange();
  const format = SpreadsheetApp.newTextStyle().setForegroundColor(color).build();
  const v = range.getRichTextValues().map(r =>
    r.map(c => {
      const delimiter = "\n";
      const text = c.getText();
      const idx = text.indexOf(delimiter);
      if (idx != -1) {
        const temp = text.split(delimiter);
        return c.copy().setTextStyle(idx, idx + delimiter.length + temp[1].length, format).build();
      }
      return c;
    })
  );
  range.setRichTextValues(v);
}

Sample script 2:

In this case, by giving the variables of rangeA1Notation and sheetName, you can directly run the script with the script editor.

function myFunction() {
  const rangeA1Notation = "A1:A3"; // Please set the cell range as A1Notation.
  const sheetName = "Sheet1"; // Please set the sheet name.
  const color = "#b82f2f"; // This is from your script.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange(rangeA1Notation);
  const format = SpreadsheetApp.newTextStyle().setForegroundColor(color).build();
  const v = range.getRichTextValues().map(r =>
    r.map(c => {
      const delimiter = "\n";
      const text = c.getText();
      const idx = text.indexOf(delimiter);
      if (idx != -1) {
        const temp = text.split(delimiter);
        return c.copy().setTextStyle(idx, idx + delimiter.length + temp[1].length, format).build();
      }
      return c;
    })
  );
  range.setRichTextValues(v);
}

References:

Tanaike0
Related