0

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:

Example Graph

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.

6
  • 1
  • Hi. You said the real table I'm using is quite a bit larger. Would you edit your question to explain this - How many unique tasks; how many data rows, etc.
    – Tedinoz
    Jul 11 at 2:34
  • @Tedinoz Thank you for the link! I will see if I can get it working off of this, and I have added the information you requested just in case.
    – Emmabee
    Jul 12 at 2:19
  • How goes it? I started testing yesterday and while I'm confident about the way forward, it's a stop-start process. Lots of trouble shooting.
    – Tedinoz
    Jul 13 at 23:56
  • I've overcome the colour issue. The remaining problem (such as it is) is that the legend is meaningless. One strategy would be to manually image that will serve as a legend; insert that image into the spreadsheet, and then drag it across the graph where the legend would normally appear. What do you think?
    – Tedinoz
    Jul 14 at 2:43

1 Answer 1

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:

chart


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:

ColourRef


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

chart data


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:

legend


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
 }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.