0

I have a system that makes line graphs and scatterplots in google sheets. As of March 7th, 2018 the graphs started to include the 1 row of my data as part of the graph rather than headers of the column.

If i make the graph manually then it will make my row one the headers of the graph. Also if i edit the current graph and tick the Use row x as headers then my header will be back to how they use to be.

My question is if there is a way i can program this in google script or a work around i can use.

Some thing I tried was following this guide: Google Apps Script Chart Use row 1 as headers

but as someone else commented it no longer works, and in my test it didnt.

Thank you!

Edit: This is how i build the chart:

var currChart = currSheet.newChart();
currChart.setPosition (row, col, 5,5)
var builtChart = currChart.asScatterChart()
                        .addRange(currRange)
                        .setTitle(title)
                        .setYAxisTitle(axisLabel)
                        .setOption('useFirstColumnAsDomain','true')
                        .setOption('hAxis.viewWindow.min', min)
                        .setOption('hAxis.viewWindow.max', max)
                        .setOption('chartArea.left', 80)
                        .setOption('chartArea.top', 60)
                        .setOption('chartArea.width', 360)
                        .setOption('chartArea.height', 240)
                        .setOption('chartArea.backgroundColor',
                                  { stroke: "#828282", strokeWidth: 2, fill: "#fcfcfc" })
                        .setOption('series', 
                                  {0:{color: '#54B4C6', pointSize: 3, pointShape: 'circle'},
                                   1:{color: '#2F53D9', pointSize: 10, pointShape: 'diamond'},
                                   2:{color: '#F1BE00', pointSize: 4, pointShape: 'circle'},
                                   3:{color: '#D90D0C', pointSize: 11, pointShape: 'diamond'}})
                        .build();

currSheet.insertChart(builtChart);
Michael Isaza
  • 229
  • 1
  • 15
  • Nearly an exact duplicate of [this question](https://stackoverflow.com/questions/49176237/google-script-charts-excludes-header-by-default) – Brian Mar 08 '18 at 16:06
  • @Brian Your answer pertains to column charts and transposing them works. However, scatter plots cannot be transposed. – Jack Brown Mar 08 '18 at 16:46
  • Both questions are about setting the legend values from a specific row, which isn't doable right now with in the `EmbeddedChartBuilder` class, regardless of type. The solution for the column chart is a hacky workaround (and might not even solve the problem). – Brian Mar 08 '18 at 16:57
  • Im not sure i can do that cause im processing about 5,000 points of data per sheet page. I think i would hit the time limit – Michael Isaza Mar 08 '18 at 17:07
  • 1
    @Brian I am not sure of legends in column charts. However, legends can be defined in the scatter plot as mentioned in the answer below. – Jack Brown Mar 08 '18 at 18:18
  • 1
    @JackBrown Thanks for the clarification. It isn't in the column chart options. It's always a mystery why options vary between classes, especially with such a subtle difference like this. – Brian Mar 08 '18 at 18:35
  • 1
    @Brian I tried the below code on a column chart and it seems to set the legend! But the [documentation](https://developers.google.com/chart/interactive/docs/gallery/columnchart#configuration-options) doesn't mention it though! – Jack Brown Mar 08 '18 at 18:43

1 Answers1

1

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

So in your above code lets assume you have extracted your first row in a array like so: 

 var values = currRange.getValues()
 var headerRow = []
 for (var i =0; i<values[0].length ; i++)
   headerRow[i] = values[0][i]

You can set the values of your series using setOptions like so:

.setOption('series', 
            {0:{color: '#54B4C6', pointSize: 3, pointShape: 'circle',labelInLegend:headerRow[1]},
             1:{color: '#2F53D9', pointSize: 10, pointShape: 'diamond',labelInLegend:headerRow[2]},
             2:{color: '#F1BE00', pointSize: 4, pointShape: 'circle',labelInLegend:headerRow[3]},
             3:{color: '#D90D0C', pointSize: 11, pointShape: 'diamond',labelInLegend:headerRow[4]}})

In short, just include the following key:value pair lableInLegend:'Header Value'in your series object.
Note: Skip the first value of the header, as that pertains to the vertical axis.
Your final code will like so:

var values = currRange.getValues()
 var headerRow = []
 for (var i =0; i<values[0].length ; i++)
   headerRow[i] = values[0][i]
 var currChart = currSheet.newChart();
  currChart.setPosition (row, col, 5,5)
 var builtChart = currChart.asScatterChart()
                    .addRange(currRange)
                    .setTitle(title)
                    .setYAxisTitle(axisLabel)
                    .setOption('useFirstColumnAsDomain','true')
                    .setOption('hAxis.viewWindow.min', min)
                    .setOption('hAxis.viewWindow.max', max)
                    .setOption('chartArea.left', 80)
                    .setOption('chartArea.top', 60)
                    .setOption('chartArea.width', 360)
                    .setOption('chartArea.height', 240)
                    .setOption('chartArea.backgroundColor',
                              { stroke: "#828282", strokeWidth: 2, fill: "#fcfcfc" })
                        .setOption('series', 
                                   {0:{color: '#54B4C6', pointSize: 3, pointShape: 'circle',labelInLegend:headerRow[1]},
                                   1:{color: '#2F53D9', pointSize: 10, pointShape: 'diamond',labelInLegend:headerRow[2]},
                                   2:{color: '#F1BE00', pointSize: 4, pointShape: 'circle',labelInLegend:headerRow[3]},
                                   3:{color: '#D90D0C', pointSize: 11, pointShape: 'diamond',labelInLegend:headerRow[4]}})
                        .build();
currSheet.insertChart(builtChart);
Jack Brown
  • 5,802
  • 2
  • 12
  • 27