App script - Exception: You do not have permission to call setFontColor-CodePudding

I'm trying to edit cells font based on other cells font on the same row but I can't edit their style using a custom function as I saw.

My question is how to achieve this then ?

Here is my code :

function colorCells(nameRange, valueRange) {
    var redHex = "#ff0000";
    var orangeHex = "#ff6d01";

    var activeRange = SpreadsheetApp.getActiveRange();
    var activeSheet = activeRange.getSheet();

    var range = activeSheet.getRange(valueRange);
    var rangeColor = range.getFontColors();
    console.log(rangeColor);

    var newColors = rangeColor.map(r => r.includes(redHex) ? redHex : r.includes(orangeHex) ? orangeHex : '#000000');
    console.log(newColors);

    var rangeToColor = activeSheet.getRange(nameRange)
    
    for (let i = 1; i <= rangeToColor.getNumRows(); i  ) {
      const cell = rangeToColor.getCell(i, 1);
      cell.setFontColor(newColors[i-1])
    }

    return "";
}

EDIT :

To explain my objective, I have a column of strings and for each a row of data. And I want some of the data have different font color based on conditional formatting.

So I want that if any data from a row is either red or orange the related column string change its color to the same one. Here is an example on an image.

Before applying the function/script :

After :

CodePudding user response：

What you really want is to extract the colors on each row and then apply it to the column with names (independently of whatever name is there).

To do that, the simplest way is to make a function that extracts that information:

const BLACK = '#000000'

function extractColumnColors(range) {
  return range.getFontColors()
    .map(rowColors => [
      rowColors.find(v => v !== BLACK) ?? BLACK
    ])
}

This function does the following:

Get the font color of the cells in the range (class Range not the A1 notation of it).
For each row, finds the first color that's not black and makes an array with a single element with it.
If all are black, the value in the array is black.

With all of that, the result is an array that contains arrays with a single color value, which is perfect because it allows to call setFontColors directly:

function example() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Example')
  const nameRange = sheet.getRange('A1:A')
  const valuesRange = sheet.getRange('B1:C')

  const newColors = extractColumnColors(valuesRange)
  nameRange.setFontColors(newColors)
}

References