Search code examples
google-apps-scriptgoogle-sheetsgoogle-visualizationembedded-resource

Questions around Google Chart API in Google Sheets


I've got some troubles working with charts in my Google Sheet. Some functions are not recognized by GAS when executing them on a chart. The most problematic one is the setDimensions() referenced in every official docs :/ Example:

sheet.newChart()
     .setChartType(Charts.ChartType.LINE)
     .asLineChart()                           
     .addRange(range1)                   
     .addRange(range2)
     .setDimensions(600, 400)
     .build();

Same thing for some options like defining the gradient of axis. I understood there is something like this:

.setOption("vAxis.ticks", [10000,20000,40000,60000,70000,80000])
.setOption("vAxis", {0: {ticks: "[10000,20000,40000,60000,70000,80000]"}})
.setOption("vAxis", {ticks: "[10000,20000,40000,60000,70000,80000]"})

But I tried quite a lot of ways to code it and nothing works :/ I was wondering also if I can skip one 'tick' out of two on the horizontal axis to lighten the view. Nonetheless some options work just well..

Another thing is about animations (for the chart e.g). I didn't understand well if it's possible or not in Google sheet? I can see some code examples with google.visualization but GAS doesn't recognize it in Google Sheet. Like here : https://developers.google.com/chart/interactive/docs/animation Is this possible or not? I finally think it's only doable by inserting html code into the sheet. Is that correct? I was a bit disappointed since I've found in GAS API doc:

.setOption('animation.duration', 1000);', 1000)

Thank you for your lights!


Solution

  • Thanks TheMaster for your reponse. Indeed options available for embedded charts are really poorly documented today.

    Solutions to my problems in an embedded chart:

    For the dimensions:

    .setOption('width', 1200)
    .setOption('height', 650)
    

    To avoid printing all axis values:

    var hAxisOptions = {
        gridlines: {
          count: 10
        }
      };
    .setOption('hAxis', hAxisOptions)
    

    About animations, yes I think it's not possible except if I embed HTML myself someway