3

Using the following script:

function chartCreation() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Sheet1");
  var chart = sheet.newChart().asColumnChart()
    .setTitle('Test Chart')
    .addRange(sheet.getRange(1, 1, sheet.getLastRow(), 4))
    .setPosition(1,1,0,0)
  sheet.insertChart(chart.build()); 
}

Produces a chart like this one, in which the legend is empty (i.e. the data series are unlabeled). Chart Example

I've read that it could be due to the format of my header row, which should be text (which mine is, but it doesn't seem to be used). If I try to build it within Google Sheets, though the chart builder dialog, it works just fine and includes the data series labels.

How do I set the "use row 1 as headers" option with Google Apps Script?

This question is similar, but has no answer pertaining to non-text format.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Patrick Dufresne
  • 57
  • 4

1 Answers1

4

Note: This post is based on this SO Post and works even for column charts. Even though column charts configuration options dont document it.

Legend values can be set using a labelInLegend in the series configuration options as mentioned here.(Navigate to the option series)

Your code will look something like this:

function chartCreation() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Sheet1");
var headerRow = sheet.getRange(1,1,1,4).getValues()
var chart = sheet.newChart().asColumnChart()
.setTitle('Test Chart')
.addRange(sheet.getRange(1, 1, sheet.getLastRow(), 4))
.setPosition(1,1,0,0)
.setOption('series', 
     {0:{labelInLegend:headerRow[0][1]},
      1:{labelInLegend:headerRow[0][2]},
      2:{labelInLegend:headerRow[0][3]}})
 sheet.insertChart(chart.build()); 
}
Jack Brown
  • 5,802
  • 2
  • 12
  • 27
  • I'm getting the error: TypeError: Cannot read property "1" from undefined. Looks like it has to do with the headerRow. My headers are A1:D1 so it looks fine to me, any idea why this is happening? – Patrick Dufresne Mar 13 '18 at 12:12
  • Had a syntax wrong on this line 'var headerRow = sheet.getRange(1,1,1,4).getValues'. It's fixed now, try the updated code. Thanks – Jack Brown Mar 13 '18 at 15:06