The function will be called from many other functions. For series colors when reporting on states (a fixed number) I use the state colors which I load from a larger array which I then trim. When reporting on schools (a relatively fixed number) I use the school colors. When reporting on other entities I use the relevant subset. This code works fine.
My requirement is that when the last row is to be highlighted as an average or highlighted and displayed against the right axis I choose a color which contrasts well against the black background and attempt to increase the line width. The technique I use is to have variables in the .setOption series for series number, series width and series axis. This does not appear to work. What am I doing wrong?
function lineChart1(rowa, cola, rowl, coll, rowo, chtTitle, sheetName, position, suffix, second, groups) {
/**
* @param: rowa is the anchor row for the input range
* @param: cola is the anchor column for the input range
* @param: rowl is the length of the input range in rows
* @param: coll is the length of the input range in columns
* @param: chtTitle is the title to be inserted in the chart
* @param: sheetName is the name of the target sheet for output
* @param: position Selects whether the chart is placed on the first,
* second or third column (column defined in script properties)
* @param: suffix true/false indicates if last row should be highlighted
* @param: second true/false specifies if the last series should be mapped
* to the second axis and highlighted */
/** Define current spreadsheet and name of sheet to contain chart */
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName(sheetName);
/** Get default chart attributes from script properties */
const ps = PropertiesService.getScriptProperties();
var chartHeight = Number(ps.getProperty('chartHeight'));
var chartWidth = Number(ps.getProperty('chartWidth'));
var column1Chart = Number(ps.getProperty('column1Chart'));
var column2Chart = Number(ps.getProperty('column2Chart'));
var column3Chart = Number(ps.getProperty('column3Chart'));
/** Check if the anchor cell contains the chart ID of a previous chart
* If so delete using that id, otherwise */
var chtSheet = ss.getSheetByName(sheetName);
var rng = chtSheet.getRange(rowo, outputColumn);
var oldID = rng.getValue();
if (oldID !== '' && oldID !== null) {
deleteChartById(oldID, sheetName);
}
/** Setup default colours for series */
/** Get corporate colours is a group; otherwise get colours
* with the first 5 colours matching state colours */
var seriesColoursArray = [];
if (groups) { var seriesColoursArray = getSelectedGroupColours(); }
else { var seriesColoursArray = getSeriesColours(); }
/** Conditionally capture the colours for the left axis */
var seriesColours = [];
/** Determine how many series are for left axis */
/** Subtract one for the header, one more if suffix or second specified */
if (second !== true && suffix !== true) { var numPrimary = rowl - 1; }
else { numPrimary = rowl - 2 }
for (var r = 0; r < numPrimary; r++) {
if (groups) { var colour = seriesColoursArray[r]; }
else { var colour = seriesColoursArray[r][0]; }
seriesColours.push(colour);
}
/** Add another colour if there is a suffix or right axis
* and set the seriesWidth for the suffix or secondary series */
if (suffix || second) {
seriesColours.push('#ff00ff');
var seriesWidth = 4;
}
else {
var seriesWidth = 2;
}
/** Set variable for axis for last series */
if (second) { var axisNum = 1 } else { var axisNum = 0 };
/** Set series number variable for last series */
var seriesNum = seriesColours.length - 1;
/** Set the chart position */
switch (position) {
case null:
outputColumn = column1Chart
break;
case 1:
outputColumn = column1Chart
break;
case 2:
outputColumn = column2Chart
break;
case 3:
outputColumn = column3Chart
break;
default:
outputColumn = column1Chart
}
/** Set the chart element colour defaults */
var chartElementColours = [];
var chartElementColours = getChartColours();
var chartAreaColour = chartElementColours[0][1];
var chartPlotColour = chartElementColours[0][1];
var chartTextColour = chartElementColours[2][1];
var chartGridColour = chartElementColours[4][1];
/** --------------------------------------------------------------
* Select the input data to build the chart
* --------------------------------------------------------------*/
var chtRange = sh.getRange(rowa, cola, rowl, coll);
var lineChartBuilder = sh.newChart().asLineChart();
var chart = lineChartBuilder
.addRange(chtRange)
.setPosition(rowo, outputColumn, 0, 0)
.setTransposeRowsAndColumns(true)
.setTitle(chtTitle)
.setColors(seriesColours)
.setOption('titleTextStyle', { color: '#ffff00', fontName: 'Arial', bold: false, fontSize: 16 })
// .setOption('applyAggregateData', 0)
.setNumHeaders(1) /** First row contains headers */
.setOption('chartArea', { backgroundColor: chartPlotColour })
.setOption('legend', { position: 'bottom', textStyle: { color: chartTextColour, fontSize: 12 } })
.setOption('XAxis', { position: 'bottom', textStyle: { color: chartTextColour, fontSize: 12 } })
.setOption('hAxis', { format: "#", textStyle: { color: chartTextColour, fontSize: 12 }, gridlines: { count: 0 }, minorGridlines: { count: 0 }, majorGridlines: { count: 0 } })
.setBackgroundColor(chartAreaColour)
.setOption('applyAggregateData', 0)
.setOption("useFirstColumnAsDomain", true) /** Get the the labels from the first column */
.setCurveStyle(Charts.CurveStyle.SMOOTH)
.setOption('series', { seriesNum: { textStyle: { color: chartTextColour, fontName: 'Arial', fontSize: 12, visibleInLegend: true }, lineWidth: seriesWidth, targetAxisIndex: axisNum } })
.setOption('vAxes', {
0: { textStyle: { color: chartTextColour, fontName: 'Arial', fontSize: 12 }, gridlines: { count: -1, color: chartGridColour, lineDashStyle: [4, 4] } },
1: { textStyle: { color: 'white', fontName: 'Arial', fontSize: 12 } }
})
.setOption('vAxis', { textStyle: { color: chartTextColour, fontSize: 12 } })
.setOption('width', chartWidth)
.setOption('height', chartHeight)
.build();
sh.insertChart(chart);
var newID = chart.getChartId();
/** Store the chart ID in case we want to subsequently delete or modify it */
var rng = chtSheet.getRange(rowo, outputColumn);
rng.setValue(newID);
};
function deleteChartById(id, sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var chtSheet = ss.getSheetByName(sheetName);
const chart = chtSheet.getCharts().find(chart => chart.getChartId() === +id);
if (!chart) {
throw new Error('Chart with ID ' + id + ' not found.');
} else {
chtSheet.removeChart(chart);
}
}
I was expecting that when either suffix or second were true the line width would be 4 and if second was true the series would be mapped against the right axis
if I follow correctly, you're trying to use the value of variable seriesNum as the property name of the series option
with the current code, it is simply using the label 'seriesNum' as the property name
in JavaScript, in order to use the value of a variable as a property name of an object,
you have to use brackets when setting the property name
for example, using an object variable named seriesOption...
var seriesOption = {};
the following...
seriesOption.A = 1;
is the same as...
var name = 'A';
seriesOption[name] = 1;
as such, recommend building your series option first, as follows...
/** Set series number variable for last series */
var seriesNum = seriesColours.length - 1;
/** Build series option for last series */
var seriesOption = {};
seriesOption[seriesNum] = {
textStyle: {
color: chartTextColour,
fontName: 'Arial',
fontSize: 12,
visibleInLegend: true
},
lineWidth: seriesWidth,
targetAxisIndex: axisNum
}
and then set the option in the chart, using...
/** Set series option for last series */
.setOption('series', seriesOption)