Unable to add annotations to charts within google sheets using app script

Question

I am trying to create line and area chart png images with google app scripts using data within a google sheet. I am able to successfully create the graphs, but unable to add an annotation field in app scripts that can be added using java script.

Below is the app script code, I would expect I'd be able to add something like .addColumn(Charts.ColumnType.STRING, "annotationText") or .addColumn({type: 'string', role: 'annotation'}) like is done within javascript. Neither work in the code below, and there seems to be no way to declare via google script that a column is assigned to the "annotation" role. Is there some undocumented way to add annotations to charts via google scripts? I could not find any documentation for assigning annotation roles to columns using google script, but it seems that this would be possible. Does anyone know how to do this via google scripts?

var data = Charts.newDataTable()
       .addColumn(Charts.ColumnType.STRING, "Month")
       .addColumn(Charts.ColumnType.NUMBER, "In Store")
       .addColumn(Charts.ColumnType.NUMBER, "Online")
       .addColumn(Charts.ColumnType.STRING, "annotationText")
       .addRow(["January", 10, 1, "cold!"])
       .addRow(["February", 12, 1,""])
       .addRow(["March", 20, 2, ""])
       .addRow(["April", 25, 3, "showers"])
       .addRow(["May", 30, 4,""])
       .build();

   var chartBlob = Charts.newAreaChart()
       .setDataTable(data)
       .setStacked()
       .setRange(0, 40)
       .setTitle("Sales per Month")
       .build()
       .getAs('image/PNG');

Mogsdad Mogsdad · Accepted Answer · 2015-07-20T01:50:53

The Charts Service exposes a limited portion of the Google Visualization API. Annotations are not supported, for example.

You can add annotations if you use client-side javascript (in a dialog, for example) with the Visualization API. Unfortunately, that would not fit your save-as-image approach.

Unable to add annotations to charts within google sheets using app script

1 Answers