Google apps script that dynamically change color of charts based on cell hex value produce bizzarre color choice

I'm creating a GSheet with some graphs, I want to choose the color of these graphs dynamically (I have a column with the hex code) so I wrote the following script.

For some strange reason, at least for my newbie level, the script change the colors in a bizzarre way. Most of the times, the column or the slices in the graph, is completely black or transparent.

Executing the script multiple time can give you different results

1st graph ok, 2nd completely transparent, 3rd 2 colors ok+transparent

1st graph still ok, 2nd still completely transparent, 3rd 2 slices black+transparent

same as first image, but I changed an hex code for the 3rd graph and clicking on the graph, you can see one value of the transparent column

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Script')
    .addItem('Update colors', 'changePieChartColors')
    .addToUi();
}


function changePieChartColors() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Definisci gli intervalli di dati e colori per ciascun grafico
  var chart1DataRange = sheet.getRange("D4:D9");
  var chart1ColorRange = sheet.getRange("E4:E9");
  
  var chart2DataRange = sheet.getRange("G4:G9");
  var chart2ColorRange = sheet.getRange("H4:H9");
  
  var chart3DataRange = sheet.getRange("J4:J9");
  var chart3ColorRange = sheet.getRange("K4:K9");
  
  // Configura il primo grafico
  var chart1 = sheet.getCharts()[0]; // Modifica il numero tra parentesi quadre [0] in base alla posizione del grafico
  var data1 = chart1DataRange.getValues();
  var colors1 = chart1ColorRange.getValues();
  var colorHex1 = [];
  
  for (var i = 0; i < data1.length; i++) {
    var value = data1[i][0];
    var color = colors1[i][0];
    colorHex1.push("#" + color);
  }
  
  chart1 = chart1.modify().setOption('colors', colorHex1).build();
  sheet.updateChart(chart1);
  
  // Configura il secondo grafico
  var chart2 = sheet.getCharts()[1]; // Modifica il numero tra parentesi quadre [1] in base alla posizione del grafico perché non funziona?
  var data2 = chart2DataRange.getValues();
  var colors2 = chart2ColorRange.getValues();
  var colorHex2 = [];
  
  for (var j = 0; j < data2.length; j++) {
    var value = data2[j][0];
    var color = colors2[j][0];
    colorHex2.push("#" + color);
  }
  
  chart2 = chart2.modify().setOption('colors', colorHex2).build();
  sheet.updateChart(chart2);
  
  // Configura il terzo grafico
  var chart3 = sheet.getCharts()[2]; // Modifica il numero tra parentesi quadre [2] in base alla posizione del grafico - mi da solo fette nere!
  var data3 = chart3DataRange.getValues();
  var colors3 = chart3ColorRange.getValues();
  var colorHex3 = [];
  
  for (var k = 0; k < data3.length; k++) {
    var value = data3[k][0];
    var color = colors3[k][0];
    colorHex3.push("#" + color);
  }
  
  chart3 = chart3.modify().setOption('colors', colorHex3).build();
  sheet.updateChart(chart3);
}

I tried to change the range of the colors (naming with red, blue... instead of hex), I also thought was something wrong in adding the # in the script instead that in the cell value, I added some sleep between the graph changes but nothing changed.

Utilities.sleep(5000);

Expectations are, obviously, that the right color will be showed. Thanks in advance if someone could point me in the right direction.

PS: with only one graph, it doesn't happen

Solution

When I saw your script, how about using setColors instead of setOption('colors', colorHex1)? I guessed that this might be the reason for your current issue. When this is reflected in your script, how about the following modification?

Modified script:

function changePieChartColors() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var [chart1, chart2, chart3] = sheet.getCharts();

  // Definisci gli intervalli di dati e colori per ciascun grafico
  var chart1DataRange = sheet.getRange("D4:D9");
  var chart1ColorRange = sheet.getRange("E4:E9");

  var chart2DataRange = sheet.getRange("G4:G9");
  var chart2ColorRange = sheet.getRange("H4:H9");

  var chart3DataRange = sheet.getRange("J4:J9");
  var chart3ColorRange = sheet.getRange("K4:K9");

  // Configura il primo grafico
  var data1 = chart1DataRange.getValues();
  var colors1 = chart1ColorRange.getValues();
  var colorHex1 = [];

  for (var i = 0; i < data1.length; i++) {
    var color = colors1[i][0];
    colorHex1.push("#" + color);
  }

  var c1 = chart1.modify().asPieChart().setColors(colorHex1).build();
  sheet.updateChart(c1);

  // Configura il secondo grafico
  var data2 = chart2DataRange.getValues();
  var colors2 = chart2ColorRange.getValues();
  var colorHex2 = [];

  for (var j = 0; j < data2.length; j++) {
    var color = colors2[j][0];
    colorHex2.push("#" + color);
  }

  var c2 = chart2.modify().asColumnChart().setColors(colorHex2).build();
  sheet.updateChart(c2);

  // Configura il terzo grafico
  var data3 = chart3DataRange.getValues();
  var colors3 = chart3ColorRange.getValues();
  var colorHex3 = [];

  for (var k = 0; k < data3.length; k++) {
    var color = colors3[k][0];
    colorHex3.push("#" + color);
  }

  var c3 = chart3.modify().asPieChart().setColors(colorHex3).build();
  sheet.updateChart(c3);
}

Testing:

When this script is run using your provided Spreadsheet, the following result is obtained.

Note:

This modified script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

Google apps script that dynamically change color of charts based on cell hex value produce bizzarre color choice

Modified script:

Testing:

Note:

References: