Search code examples
google-apps-scriptgoogle-sheetscountaggregation

Unexpected behaviour when using Google Sheets Macros to create Charts with Count Aggregation

I want to create a Macro to create charts based on information from 2 columns (as in the picture below): enter image description here

The chart I created manually looks like this, where each column from the chart represents how many transactions happened at that exact hour, using the Count Aggregation feature: enter image description here

The problem I have now is that, despite the Macro clearly stating to use the Count Aggregate function on the first series, it seems to default to Sum Aggregate:

function CreateMaximumsChart() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange("G1").activate();
  var sheet = spreadsheet.getActiveSheet();

  chart = sheet
    .newChart()
    .asColumnChart()
    .addRange(spreadsheet.getRange("H1:H"))
    .addRange(spreadsheet.getRange("G1:G"))
    .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
    .setTransposeRowsAndColumns(false)
    .setNumHeaders(0)
    .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
    .setOption("applyAggregateData", 0)
    .setOption("bubble.stroke", "#000000")
    .setOption("useFirstColumnAsDomain", true)
    .setOption("isStacked", "false")
    .setOption("title", "Maximums")
    .setOption("series.0.aggregateFunction", "count")
    .setPosition(8, 5, 44, 20)
    .build();
  sheet.insertChart(chart);
}

Which results in this chart being created: enter image description here

Is there something I'm doing wrong? The macro code looks fine on my end, could it be a bug with Google Sheets?

Solution

  • If you comment out the line that adds the G1:G range, since you are not using it for the chart, it will give the same result as the one you have above.

    function CreateMaximumsChart() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange("G1").activate();
  var sheet = spreadsheet.getActiveSheet();

  chart = sheet
    .newChart()
    .asColumnChart()
    .addRange(spreadsheet.getRange("H1:H"))
//  .addRange(spreadsheet.getRange("G1:G")) // Remove this line
    .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
    .setTransposeRowsAndColumns(false)
    .setNumHeaders(0)
    .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
    .setOption("applyAggregateData", 0)
    .setOption("bubble.stroke", "#000000")
    .setOption("useFirstColumnAsDomain", true)
    .setOption("isStacked", "false")
    .setOption("title", "Maximums")
    .setOption("series.0.aggregateFunction", "count")
    .setPosition(8, 5, 44, 20)
    .build();
  sheet.insertChart(chart);
}

    The issue seems to be that you are adding a range to the chart which you don't need to generate the chart.