14

I am using Google Apps Script and EmbeddedChartBuilder to embed line charts within my Google Spreadsheet. When you create these charts by hand, you have the (non-default) option to "Use column A as labels" (where "A" is the first column in the data range). I cannot find a way to do the same from a script. From the Google Visualization Line Chart documentation, it appears that the default is to treat the first column as having the "domain" role; but EmbeddedChartBuilder seems to override this and give all columns the "data" role. Since I don't have an explicit DataTable, I have no way to set the column roles myself.

Have I missed a way to do this? Or do I have to switch approaches from EmbeddedChartBuilder to using the spreadsheet as a data source?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Andrew
  • 5,611
  • 3
  • 27
  • 29

2 Answers2

17

Found it! Set the option useFirstColumnAsDomain to true with EmbeddedChartBuilder.setOption.

This option appears to be undocumented. I found it by going to "Publish chart" (click on the chart, then select from the drop-down in the top right) and inspecting the JavaScript data structure in the given code. To be exact, I created a chart with "Use column A as labels" unchecked, grabbed the publish data structure, then checked "use column A as labels", grabbed the new publish data structure, and compared the two. To compare, I suggest normalizing the JSON and running diff. This technique can be used to reverse-engineer any settings in the chart editor.

Community
  • 1
  • 1
Andrew
  • 5,611
  • 3
  • 27
  • 29
  • Is this still working for anyone? I've been trying for an hour or so without success – Jonathan del Strother Jul 11 '14 at 15:11
  • 3
    Perfect, thank you! Yes it works. for embedded charts, this is the exact syntax: `.setOption('useFirstColumnAsDomain', true)` within the **chartBuilder** block. – Sujay Phadke Jun 11 '15 at 01:29
  • Some searching led me to believe this is actually documented: https://developers.google.com/apps-script/chart-configuration-options but I couldn't find a link to this page in docs, it's only available via search. – TheMaster Jun 27 '22 at 15:39
  • @TheMaster That page is linked from the documentation for `EmbeddedChartBuilder.setOption` [here](https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart-builder#setoptionoption,-value) – Aaron Dunigan AtLee Sep 04 '22 at 20:08
4

I just experienced this same problem. I took a similar approach as Andrew, but found a different solution (presumably because Google has added functionality to their graphs in Spreadsheets).

I used http://jsbeautifier.org/ to format the code after publishing, and I found this part to be responsible for adding the data labels. Note that you can even change the color of the stem that connects the bar to the data label:

"series": {
                    "0": {
                        "errorBars": {
                            "errorType": "none"
                        },
                        "dataLabel": "value",
                        "annotations": {
                            "stemColor": "none"
                        }
                    },
                    "color": "black",
                    "targetAxisIndex": 0,
                    "annotations": {
                        "textStyle": {
                            "color": "red",
                            "fontSize": 12
                        }
                    }
                },
Dave Ghidiu
  • 41
  • 1
  • @DaveGhidui, could I contact you for how to get that script to work for my use case? I'm no good at JS and I've been unsuccessful with combining your script with what Google's documentation cited by OP. – Bobby Fritze May 04 '16 at 17:20