Google Sheets : Color dots in scatter plot based on data range cell background color

Question

I have a Google Sheets file that stores paint thickness values for different cars in different zones (rear, front, left, right). Everything is then summarized in a scatter plot that shows Paint thickness (Y- Axis) by vehicle age (X-axis).

Each Zone is its own series, and what that gives is that or each car, it creates a vertical line of dots aligned on the age value of the car (the serial number is only a label). Each dot is showing the paint thickness in the corresponding zone with its position on the Y axis.

I'm changing the cells background color based on the damage reported in each zone. I would like to have the same colors on each corresponding dots in my scatter chart, without coloring them one by one.

(I can't include any link/screenshot because this is confidential data)

Example :

SerialNumber	AGE	ZONE1	ZONE2	ZONE3	ZONE4
42	15	178	210	246	143

Serial number 42 is 15 months old. Its left side has a paint thickness of 246 and damage has been reported. I color the cell containing 246 in red, and that changes the color of the corresponding dot in my scatter chart.

The script would have to associate each dot in the scatter plot to its corresponding cell, get the cell's background color and set the color of the point according to it.

I can't color entire series because each color needs to be specific to one zone, in one car

This file used to be an Excel file, and I found a VBA macro that could do that, but my company is switching to Gsuite and I'm not familiar enough with it to translate the macro.

Here is the VBA code :

Sub CellColorsToChart()
'Update by Extendoffice
    Dim xChart As Chart
    Dim I As Long, J As Long
    Dim xRowsOrCols As Long, xSCount As Long
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    Set xChart = ActiveSheet.ChartObjects("Chart 1").Chart
    If xChart Is Nothing Then Exit Sub
    xSCount = xChart.SeriesCollection.Count
    For I = 1 To xSCount
        J = 1
        With xChart.SeriesCollection(I)
            Set xRg = ActiveSheet.Range(Split(Split(.Formula, ",")(2), "!")(1))
            If xSCount > 4 Then
                xRowsOrCols = xRg.Columns.Count
            Else
                xRowsOrCols = xRg.Rows.Count
            End If
            For Each xCell In xRg
                .Points(J).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
                .Points(J).Format.Line.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
                J = J + 1
            Next
        End With
    Next
End Sub

Answer 1

A quite clumsy but anyway, here is my try:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var colors = sheet.getRange('C2:F2').getBackgrounds().flat();
  
  var chart = sheet.getCharts()[0];
  chart = chart.modify()
    .setOption('series.0.items.0.color', colors[0])
    .setOption('series.0.items.1.color', colors[1])
    .setOption('series.0.items.2.color', colors[2])
    .setOption('series.0.items.3.color', colors[3])
    .build();
  sheet.updateChart(chart);
}

Update

I'm still not sure if I understand it right. May be something like this?

function myFunction() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var chart = sheet.getCharts()[0];
  var colors = [];
  
  colors = sheet.getRange('C2:C5').getBackgrounds().flat();
  chart = recolor(chart, colors, 0);

  colors = sheet.getRange('D2:D5').getBackgrounds().flat();
  chart = recolor(chart, colors, 1);

  colors = sheet.getRange('E2:E5').getBackgrounds().flat();
  chart = recolor(chart, colors, 2);
    
  colors = sheet.getRange('F2:F5').getBackgrounds().flat();
  chart = recolor(chart, colors, 3);
    
  sheet.updateChart(chart);
  
  function recolor(chart, colors, series) {
    return chart.modify()
      .setOption('series.' + series + '.items.0.color', colors[0])
      .setOption('series.' + series + '.items.1.color', colors[1])
      .setOption('series.' + series + '.items.2.color', colors[2])
      .setOption('series.' + series + '.items.3.color', colors[3])
      .build();
  }

}

Update 2

Updated version of the code. Now it able to color any number of cars (rows):

function myFunction() {

  var sheet  = SpreadsheetApp.getActiveSheet();
  var last   = sheet.getLastRow();
  var chart  = sheet.getCharts()[0];
  var colors = [];

  // recolor zone 1
  colors = sheet.getRange('C2:C' + last).getBackgrounds().flat();
  chart = recolor(chart, colors, 0);

  // recolor zone 2
  colors = sheet.getRange('D2:D' + last).getBackgrounds().flat();
  chart = recolor(chart, colors, 1);

  // recolor zone 3
  colors = sheet.getRange('E2:E' + last).getBackgrounds().flat();
  chart = recolor(chart, colors, 2);
    
  // recolor zone 4
  colors = sheet.getRange('F2:F' + last).getBackgrounds().flat();
  chart = recolor(chart, colors, 3);

  // update the chart    
  sheet.updateChart(chart);
  
  function recolor(chart, colors, series) {
    colors.forEach((color,i) => {
      chart = chart.modify()
        .setOption('series.' + series + '.items.' + i + '.color', color)
        .build();
    });
    return chart;
  }
}

Probably it can be extended to be able to take any number of zones as well.

---

Here is a final version that can handle any number of zones:

function myFunction() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var chart = sheet.getCharts()[0];
  var last_cell = chart.getRanges()[1].getA1Notation().split(':')[1]; // the bottom right cell of the chart
  var data  = sheet.getRange('C2:' + last_cell).getBackgrounds();

  for (var i in data) chart = recolor(chart, data.map(x => x[i]), i);
  
  sheet.updateChart(chart);
  
  function recolor(chart, colors, series) {
    colors.forEach((color,i) => {
      chart = chart.modify()
        .setOption('series.' + series + '.items.' + i + '.color', color)
        .build();
    });
    return chart;
  }

}