Google Script - Use row x as headers

Question

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);

Jack Brown · Accepted Answer

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



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

Google Script - Use row x as headers

Answers (1)

Related Questions