Automatically Color Series In Chart

Question

I am trying to make a graph that is essentially a timeline of a few different activities, all of which can be repeated. The only way I've come up with to do this so far is to have each "instance" of a task graphed as its own series, but then to manually color all the series of the same type in the same color.

Here is an example of a small table and the way I would like the graph it generates to look:

Unfortunately the real table I'm using is quite a bit larger, so it is not practical for me to manually match the color of all series under the same task [the current size of the table is 335 series, and I am still populating it. It could potentially double but I do not expect more than that].

I am looking for a way to automatically color each series based on the value in the "Task" column. I am also open to any different way of implementing this that will produce a similar visual result.

Example sheet to test in: [link]

I would strongly prefer to accomplish this in Google Sheets, but I do have access to Excel if necessary.

Answer 1

The following answer was developed piecemeal and many elements could have been combined to create a more efficient process. However, each of the functions will run standalone and this may assist the OP and others in development of their coding.

---

The chart developed by this answer looks like this:

---

Step#1: Colour Reference

In order to allocate a specific colour to each unique task in the graph data, there needs to be an index showing the task/colour assignments.

• create a new sheet (mine is called "colorRefChart"), but this is a variable so you can choose your own name
• populate the list with the unique Task name in Column A, and the relevant background colour in Column B.
• it should look something like this:

---

Step#2: Chart data

• I assumed that the chart data was on the same sheet as the chart.
• I created a new sheet (mine was called "ejbChart") but again this is a variable so you can choose your own name
• The data is in columns B&C;
  • Column B: Task/event name
  • Column C: Time
• It is not necessary to have background colours in this list. In fact, it probably makes management of the list easier.
• There should be no task names in this list that are not also in the Colour Reference list. Ideally one would include some error checking for this, but I'll leave this for the OP.
• The process to graph the data is dynamic, so the data can occupy any number of rows provided that there are no gaps in any rows of the data.

The data might look something like this

---

Step3: Code to update colours

The key function is buildTaskColors which does two things:

• assigns colours to each data value based on the background colour in the "colorRefChart" sheet.
• builds an external "Legend" to the right of the chart (the standard legend being irrelevant because it displays every data element)

---

function buildTaskColors(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var chartSheetName = "ejbChart"
  var chartSheet = ss.getSheetByName(chartSheetName)
  var colorSheetName = "colorRefChart"
  var colorSheet = ss.getSheetByName(colorSheetName)
  const charts = chartSheet.getCharts()
  // Logger.log("DEBUG: number of charts = "+charts.length)
  var chart = charts[0]

  // get the data from the chartSheet
  var firstRow=4
  var bVals = chartSheet.getRange("B"+firstRow+":B").getValues()
  var bLast = bVals.filter(String).length
  // Logger.log("DEBUG: the last row in Column B = "+(bLast+firstRow-1))
  var dataRange = chartSheet.getRange(4,2,bLast)
  //Logger.log("DEBUG: the data range = "+dataRange.getA1Notation())
  var dataValues = dataRange.getValues()
  var tasks  = dataValues.map(function(e){return e[0]})
  //Logger.log(tasks)

  // get the color info
  // get the last row in Column A
  var aVals = colorSheet.getRange("A2:A").getValues();
  var aLast = aVals.filter(String).length;
  //Logger.log("the last row in Column A = "+aLast)
  var colorRange = colorSheet.getRange(2,1,aLast,2)
  //Logger.log("the color range = "+colorRange.getA1Notation())
  var colorValues = colorRange.getValues()
  //Logger.log(colorValues)
  
  var taskNames = colorValues.map(function(e){return e[0]})
  //Logger.log(taskNames)

  // create a temporary array for the background colors
  var colorCodes = new Array()
  var bgRange = colorSheet.getRange(2,2,aLast,1)
  //Logger.log(bgRange.getA1Notation())
  var bgValues = bgRange.getBackgrounds()
  //Logger.log(bgValues)
  var colorCodes = bgValues.map(function(e){return e[0]})

  //Logger.log(colorCodes)
  
  
  // create a temporary array to hold the color values
  var colorList = new Array()
  
  // loop though the data rows on the Chart
  // for each task find the match on the ColourRef and get the color code
  for (var i=0;i<dataValues.length;i++){
    var colorNumber = taskNames.indexOf(tasks[i])
    var hexColor = colorCodes[colorNumber] 
    //Logger.log("DEBUG: i:"+i+", task = "+tasks[i]+", colorNumber = "+colorNumber+", color = "+hexColor)
    colorList.push(hexColor)
  }

  const barBuilder = chart.modify().asBarChart().setColors(colorList)
  chartSheet.updateChart(barBuilder.build())
  Logger.log("DEBUG: Built the color assignments")
  
  
  // now create the external legend beside the chart
  var legendStartCol=11
  var legendCount = taskNames.length
  var legendTaskRange = chartSheet.getRange(firstRow,legendStartCol+1,legendCount,2)
  legendTaskRange.setValues(colorValues)
  Logger.log("Updated the Legend: Tasks column")
  var legendBGRange = chartSheet.getRange(firstRow,legendStartCol,legendCount)
  legendBGRange.setBackgrounds(bgValues)
  Logger.log("Updated the Legends: Background Color Column")
}

---

Key aspects-Colour values

• gets the chart data (a 2D array)
  • then var tasks = dataValues.map(function(e){return e[0]}) creates a 1D array of all the tasks. This contains as many items as there are rows of data
• gets the color references (a 2D array)
  • then var taskNames = colorValues.map(function(e){return e[0]}) creates a 1D array of the task names. This is a short list containing only the unique task names.
• gets the background colour HEX codes (var bgValues = bgRange.getBackgrounds()); this is a 2D array
  • then var colorCodes = bgValues.map(function(e){return e[0]}) creates a 1D array of codes
• loop through the task names from the chart data using indexOf ( var colorNumber = taskNames.indexOf(tasks[i])) to find a match with the task name from the color reference sheet
• when a match is found, the relevant HEX value is obtains (var hexColor = colorCodes[colorNumber])
• and the hex value is pushed onto a temporary array that will be used later to update the chart (colorList.push(hexColor))
• a HEX value is collected for each every task value and this is updated to the chart .setColors(colorList)

Key aspects-External legend using data collected for the assignment of colours to chart values

• the unique task names from colour Reference are saved to the sheet (legendTaskRange.setValues(colorValues))
• the background colours are set (legendBGRange.setBackgrounds(bgValues))

The resulting Legend should look something like this:

---

Other Code

In order to develop the answer, I found it useful to develop code that would:

• build the chart: buildchart()

and make a number of modifications, including:

• making it a stacked bar chart: makeStacked()
• switching Rows and Columns: switchRowsandColumns()
• hiding the legend: setLegendNone()
• setting the background colour: setChartBGColor()
• setting the chart dimensions: setChartDimensions()
• setting the X and Y axis text colours: setAxisTextCol()

These are offered E&OE

---

function buildchart() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetName = "ejbChart"
  var chartSheet = ss.getSheetByName(sheetName)

  // get the data range
  // could use 'getLastRow' but I want to make sure that focus is solely on Task Column
  var bStartRow = 3
  var bstartColumn = 2
  var bRange = chartSheet.getRange("B"+bStartRow+":B")
  var bVals = bRange.getValues()
  // Logger.log("DEBUG: the range is = "+bRange.getA1Notation())
  var bLast = bVals.filter(String).length;
  // Logger.log("DEBUG: Number of rows of data = "+bLast);

  var chartRangeNotation = chartSheet.getRange(bStartRow,bstartColumn,bLast,2).getA1Notation()
  // Logger.log("DEBUG: range: "+ chartRangeNotation)
  
  // build the chart
  const chart = chartSheet.newChart()
    .setChartType(Charts.ChartType.BAR)
    .addRange(chartSheet.getRange(chartRangeNotation))
    .setPosition(4, 2, 19, 2)
    .build()

  chartSheet.insertChart(chart)

}

---

function makeStacked(){

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var chartSheetName = "ejbChart"
  var chartSheet = ss.getSheetByName(chartSheetName)
  const charts = chartSheet.getCharts()
  // Logger.log("DEBUG: number of charts = "+charts.length)
  var chart = charts[0]
  const barBuilder = chart.modify().asBarChart().setStacked()

  chartSheet.updateChart(barBuilder.build())
  Logger.log("DEBUG: Chart is stacked")
  return
 }

function switchRowsandColumns(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
  var chartSheetName = "ejbChart"
  var chartSheet = ss.getSheetByName(chartSheetName)
  const charts = chartSheet.getCharts()
  // Logger.log("DEBUG: number of charts = "+charts.length)
  var chart = charts[0]
  const barBuilder = chart.modify().asBarChart().setTransposeRowsAndColumns(true)

  chartSheet.updateChart(barBuilder.build())
  Logger.log("DEBUG: Switched Rows and Columns")
return
}


function setLegendNone(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var chartSheetName = "ejbChart"
  var chartSheet = ss.getSheetByName(chartSheetName)
  const charts = chartSheet.getCharts()
  // Logger.log("DEBUG: number of charts = "+charts.length)
  var chart = charts[0]
  const barBuilder = chart.modify().asBarChart().setLegendPosition(Charts.Position.NONE);
  chartSheet.updateChart(barBuilder.build())
  Logger.log("DEBUG: Set legend to NONE")
  return
 }


function setChartBGColor(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var chartSheetName = "ejbChart"
  var chartSheet = ss.getSheetByName(chartSheetName)
  const charts = chartSheet.getCharts()
  var chart = charts[0]

  var bgColor = "#666666"
  const barBuilder = chart.modify().asBarChart().setBackgroundColor(bgColor)
  chartSheet.updateChart(barBuilder.build())
  Logger.log("Set chart background color")
  return
 }


function setChartDimensions(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var chartSheetName = "ejbChart"
  var chartSheet = ss.getSheetByName(chartSheetName)
  const charts = chartSheet.getCharts()
  var chart = charts[0]
  const barBuilder = chart.modify().asBarChart()
      .setOption('height',250)
      .setOption('chartArea', {left:'10%', top:'5%', width:"85%", height:"80%"})
  chartSheet.updateChart(barBuilder.build())
  Logger.log("DEBUG: Chart area is set")
  return
 }

function setAxisTextCol(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var chartSheetName = "ejbChart"
  var chartSheet = ss.getSheetByName(chartSheetName)
  const charts = chartSheet.getCharts()
  var chart = charts[0]
  const barBuilder = chart.modify().asBarChart()
      .setOption('hAxis.textStyle', { color: '#ffffff' })
      .setOption('vAxis.textStyle', { color: '#ffffff' })
  chartSheet.updateChart(barBuilder.build())
  Logger.log("DEBUG: Axis Color is set")
  return
 }

---