I am working on filtering a Google Sheet for data and creating time series charts off of it with each athlete as a different series on the graph. The graph is being created properly but I cannot figure out how to set the legend labels on the graph using the built in functions.
function teamTimeSeriesChart(sportTeamSheet) {
// Remove any existing charts
var charts = sportTeamSheet.getCharts();
charts.forEach(function(chart) {
sportTeamSheet.removeChart(chart);
});
// Define the metrics to create charts for
var metrics = [
{ column: 4, name: 'Stress Level' },
{ column: 5, name: 'Sleep Quality' },
{ column: 6, name: 'Sleep Duration' },
{ column: 7, name: 'Soreness Level' },
{ column: 8, name: 'Hydration Level' },
{ column: 9, name: 'Fueling Level' },
];
// Get the number of rows
var numRows = sportTeamSheet.getLastRow() - 1; // Subtract 1 to exclude header
// Get date range
var dateRange = sportTeamSheet.getRange(2, 1, numRows, 1); // Dates are in the first column
// Get athlete names
var athleteNameRange = sportTeamSheet.getRange(2, 2, numRows, 1);
var athleteNames = athleteNameRange.getValues().flat().map(function(name) {
return name.trim().toLowerCase();
});
// Create a set of unique athlete names
var uniqueAthleteNames = Array.from(new Set(athleteNames));
var generatedCharts = [];
// Iterate over each metric
metrics.forEach(function(metric) {
// Create a new chart for the current metric
var chartBuilder = sportTeamSheet.newChart();
// Add date range
chartBuilder.addRange(dateRange);
Logger.log("Date range added: " + dateRange.getA1Notation());
// Add a series for each unique athlete
uniqueAthleteNames.forEach(function(name) {
// Filter rows for the current athlete
var athleteRows = [];
for (var i = 0; i < numRows; i++) {
if (athleteNames[i] === name) {
athleteRows.push(i + 1);
}
}
if (athleteRows.length > 0) {
// Create a continuous range for the current athlete's data for the current metric
var startRow = athleteRows[0];
var endRow = athleteRows[athleteRows.length - 1];
var athleteDataRange = sportTeamSheet.getRange(startRow, metric.column, endRow - startRow + 1);
// Add the series to the chart
chartBuilder.addRange(athleteDataRange);
}
});
// Build and insert the chart
var chart = chartBuilder
.setChartType(Charts.ChartType.LINE)
.setOption('title', metric.name + ' Over Time')
.setOption('hAxis', {
title: 'Date',
format: 'MM/dd/yyyy'
})
.setOption('vAxis', { title: 'Values (on scale of 1-10)' })
.setPosition(2, 11 * generatedCharts.length + 1, 0, 0)
.build();
generatedCharts.push(chart);
});
return generatedCharts;
}
I have tried using .setOption and using labels, but it’s not working. It would be great if anyone could help.
New contributor
Maya is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.