Google脚本 - 使用行x作为标题 [英] Google Script - Use row x as headers
问题描述
我有一个系统可以在Google表格中制作折线图和散点图。截至2018年3月7日,图表开始将我的数据的一行作为图表的一部分,而不是列的标题。
如果我手动创建图形,那么它会使我的行成为图形的标题。此外,如果我编辑当前图形并勾选使用行x作为标题,那么我的标题将回到他们如何使用。
我的问题是如果有方式我可以编程这在谷歌脚本或工作,我可以使用。
我尝试的一些内容是遵循本指南:
Google Apps脚本图表使用第1行作为标题
但随着别人评论它不再作品,并在我的测试它没有。
谢谢!
编辑:这是我建立图表的方式:
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);
您可以使用<$ c $如系列配置选项中的c> labelInLegend / gallery / scatterchart#configuration-optionsrel =nofollow noreferrer> here 。(导航到选项系列)
假设你已经在数组中提取了第一行,如下所示:
var values = currRange.getValues()
var headerRow = []
for(var i = 0; i< values [0] .length; i ++)
headerRow [i] = values [0] [i]
$ c
您可以使用setOptions来设置您的系列值,如下所示:
.setOption('series',
{0:{color:'#54B4C6',pointSize:3,pointShape:'circle',labelInLegend:headerRow [1]},
1:{color:'#2F53D9',pointSize:10,pointShape:'diamond',labelInLe gend:headerRow [2]},
2:{color:'#F1BE00',pointSize:4,pointShape:'circle',labelInLegend:headerRow [3]},
3:{color:' #D90D0C',pointSize:11,pointShape:'diamond',labelInLegend:headerRow [4]}})
<总之,在你的系列对象中只包括以下键:值
对 lableInLegend:'标题值'
>。
注意:跳过页眉的第一个值,因为这涉及到垂直轴。
您的最终代码将如下所示:
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,poin tShape:'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);
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);
解决方案 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);
这篇关于Google脚本 - 使用行x作为标题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!