google-apps-scriptgoogle-sheetschartsgoogle-visualization

Chart in Google Sheets created by Apps Script won't display headers as Series Names

I'm trying to create a simple chart using test data to generate a chart with Apps Script. I'm using ChatGPT as well to troubleshoot, but no joy thus far. In the resulting chart, the series have no names, and thus the chart columns and chart legend are blank.

How do I get the data columns and legend to display the header row text as titles?

Here's the data (2 rows, exported as CSV so I can show it here):

A,B,C,D,E,F,G,H,I,J,K,L,M
227,3,3,3.666666667,3.333333333,3,4,3.666666667,3.333333333,3.5,2.5,4,3

Here's the test code which creates the chart:

function createTestChart() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("TestSheet");
  
  // Define the range for the chart (including headers)
  var chartRange = sheet.getRange("A1:M2");

  // Create a column chart
  var chartBuilder = sheet.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(chartRange)
    .setPosition(5, 5, 0, 0)
    .setOption('title', 'Test Chart from A1:M2');

  var chart = chartBuilder.build();
  sheet.insertChart(chart);
}

Resulting Chart

Solution

  • Workaround: Switch the range's rows and colums

    With how your chart builder is configured. It automatically creates different series for each column and sets Column A as the label. This isn't ideal as each series has a different data type (series A1:A2 for example has A for the first value and a 227 for the second), resulting in the chart builder ignoring the letters. I suggest changing chartBuilder to this:

      var chartBuilder = sheet.newChart()
    .setChartType(Charts.ChartType.COLUMN)
    .addRange(chartRange)
    .setPosition(5, 5, 0, 0)
    .setTransposeRowsAndColumns(true) //switches the range's rows and columns
    .setOption('title', 'Test Chart from A1:M2')

    The resulting chart would look like this: image

    If it is necessary to display the values of each bar, you can also add the following:

    .setOption('series', {
  0: { dataLabel: 'value' }
})

    However, I suggest limiting the decimal places of each value in order to properly dispaly it as labels in the chart. The result would look something like this: image

    References: