“Aggregate Data Series” to average instead of sum in Google Apps Script?

Question

Nearly duplicate question

I am currently trying to create a couple charts through Google App Script. I would like to have one of the series in one of the charts to be aggregated to an average, but I cannot figure out how. The default is sum and I have yet to find a way to change it. I have been trying everything I can find to solve this issue. It does not seem to be documented at all in the chart options, and I have found minimal documentation elsewhere.

What I've tried:

Setting it for each series

Reverse engineering the code myself

.setOption('applyAggregateData',0) (from link above)

.setOption("series", {"0":{"applyAggregateData":"average"}}) (combination of answers I found)

.setOption('aggregateFunction',0) (combination of answers I found)

None of this has worked for me, and the best I'm able to get is aggregation by sum. I am not sure what I am missing here, as it seems to have worked for others in the past.

Chart code:

  var chart2 = exportSheet.newChart()
  .setChartType(Charts.ChartType.COLUMN)
  .setPosition(2, 2, 0, 0)
  .addRange(searchSheet.getRange('B11:B'))
  .addRange(searchSheet.getRange('G11:G'))
  .setOption("series", {"0":{"aggregateFunction":"average"}}) //Does not work
  .setOption("title", 'Avg. Ton Per Mile')
  .setOption('hAxis.title', 'Dates')
  .setOption('vAxis.title', 'Salt Usage (T)')
  .build();

NightEye · Answer 1 · 2021-07-21T16:58:17.200

I also can't seem to make it work but I have thought of a workaround for this. See if it works for you. This can be used until you figure out how to aggregate by average.

Code:

function myFunction() {
  // Used only 1 sheet for testing purposes
  var exportSheet = SpreadsheetApp.getActiveSheet();

  // Set a query formula somewhere that takes the average of your data
  // Our data should now be in Y11:Z that contains the average
  exportSheet.getRange('Y11')
             .setFormula('=query(query({B11:B,G11:G},"select Col1, avg(Col2) where Col1 is not null group by Col1"), "offset 1", 0)');

  var chart2 = exportSheet.newChart()
  .setChartType(Charts.ChartType.COLUMN)
  .setPosition(2, 8, 0, 0)
  // now get the output of the query
  .addRange(exportSheet.getRange('Y11:Y'))
  .addRange(exportSheet.getRange('Z11:Z'))
  .setOption("title", 'Avg. Ton Per Mile')
  .setOption('hAxis.title', 'Dates')
  .setOption('vAxis.title', 'Salt Usage (T)')
  .build();

  exportSheet.insertChart(chart2);
}

We pre-process the data via query's average and then pass the range of the output to the chart instead.

Sample Data:

Output:

Hidden query output:

Note:

Choose a range that can be hidden from plain sight for your query formula if you don't want it to be seen. (Furthermore, you can also actually hide the column to where it will be placed)
Editing B11:B and G11:G ranges will still update the chart with aggregation.
Appending to the range where query is set (in this sample, Y11:Y and Z11:Z) will not update the average, but just append to the range normally without aggregation.