Looking for an example how to create Bubble Chart inside the Spreadsheet programmatically on Apps Script

Question

I am looking for an example how to create an embedded Bubble Chart inside the Spreadsheet programmatically on Apps Script or to edit (apply options) an existing embedded Bubble Chart (ideally to see both examples of code)

Google Chart Gallery provides an example for a standalone web app.

Spreadsheet: Class EmbeddedScatterChartBuilder shows examples how to create embedded charts except the Bubble Chart!

Charts.ChartType doesn't have BUBBLE value!

Or maybe possible to use EmbeddedScatterChartBuilder to create a Bubble Chart? If so could somebody provides such an example of code please.

What I want: I want to control Size and Colour of Bubbles and also I want to Series to show Ids (but it doesn't accept range with strings - only numbers!)

  colorAxis: {
    Values: [0,1,8,15], 
    colors: ['white', 'green','yellow','red']
  },
  sizeAxis: {
    minValue: 0,
    maxSize: 30
  },
  hAxis: { 
    title :'Deadline',
    ticks: [{v:0, f:'now'},{v:1, f:'soon'},{v:2, f:'approaching'},{v:3, f:'Distant'}],
    min: 0,
    max: 3
  },

And why Google doesn't provide EmbeddedBubbleChartBuilder? I found that Old version of the Chart Editor provides the opportunity to apply colours manually for series:

This is the best result which I reached manually customise a Bubble Chart in the Old Chart Editor. This is what I would like to create programmatically (or with formulas in a sheet:

But when I scale down the Chart I see such a picture:

TheAddonDepot TheAddonDepot · Accepted Answer · 2017-11-10T14:40:29

UPDATE

As of April 11, 2018, Google Sheets now officially supports Bubble Charts.

See change log.

I think your best bet is to look for analogs between the Apps Script APIs and the example code used to embed charts that can be found in the chart gallery.

The EmbeddedScatterChartBuilder class has a method called setOptions that is used to set advanced options. These options probably correspond to options variable listed in the sample code you linked to in the Chart Gallery. So looking at the samples you linked to there are options such as bubble and colorAxis that you should be able to pass to the setOptions method.

Here's a full list of advanced configuration options taken from the same page you linked to in your question:

https://developers.google.com/chart/interactive/docs/gallery/bubblechart#configuration-options

Try them out and see what works.

Looking for an example how to create Bubble Chart inside the Spreadsheet programmatically on Apps Script

1 Answers