google-apps-script google-sheets google-visualization

Display vertically an embedded combo chart

I am trying to display a bar chart combined to a line using an EmbeddedComboChartBuilder. For this I'm using the following code and data:

function createChartSerie() {
    
    var ss = SpreadsheetApp.openById('XXX...');
    var sheet = ss.getSheetByName("sheet1");
    
    var chartTableXRange = sheet.getRange("A1:A24");
    var chartTableYRange = sheet.getRange("B1:C24");
      
    var chart = sheet.newChart()
      .setOption('useFirstColumnAsDomain', true)
      .setOption('hAxis', {
          title: 'X',
          format: '#,##'
        })
        .setOption('vAxes', {0: {
          title: 'Y', format: '#,##'
        }})
        .setChartType(Charts.ChartType.COMBO)
        .addRange(chartTableXRange) // x axis
        .addRange(chartTableYRange) //first y data
        .setPosition(5, 5, 0, 0)
        .setOption('orientation', 'vertical')
        .setOption('series', {
        0: {
          type: 'line', 
          color: 'red'
        },
        1: {
          type: 'bars', 
          color: 'blue'
        }
      })
        .build();
    
    sheet.insertChart(chart);
    }

The data range used to display the combo chart:

A   B   C
1   0.0001  0
1.5 0.0009  0.0006
2   0.0044  0.0037
2.5 0.0175  0.0133
3   0.054   0.0236
3.5 0.1296  0.0533
4   0.242   0.0073
4.5 0.3522  0.2468
5   0.399   0.0843
5.5 0.3522  0.3352
6   0.242   0.2201
6.5 0.1296  0.0607
7   0.054   0.0256
7.5 0.0175  0.0006
8   0.0044  0.003
8.5 0.0009  0.0005
9   0.0001  0.0001

It seems that the option .setOption('orientation', 'vertical') has no effect as you can see in the following picture:

This option is working well when using with google visualization like you can see in this JSFiddle.

Solution

Answer

Unfortunately Sheets API does not support this option (see the embedded charts resource).

There seems to be a feature request in Google’s Issue Tracker. You can click the white start (☆) so Google knows that you want this to be done.

Workarounds

There are 2 workarounds: use Google Charts itself on a web (works with WebApps and modals), and generate the chart with Google Charts, convert the SVG on the page to PNG and embed that to the spreadsheet. Generating a PNG requires making a modal, so this set instructions will work for both workarounds.

Step 1: Make a page that uses Google Charts to make a chart

Get some mocking data and make your chart, design it to your taste, and make sure everything works fine.

Step 2: Show it using a modal

Add a new HTML file named Chart (you can change the name but you’ll need to change it in the code) and paste the code you made.

Once you have a mock chart, we need to show it using a modal. We first need to add a menu to the spreadsheet:

function onOpen() {
 SpreadsheetApp.getUi()
   .createMenu('Chart generator')
   .addItem('Create and insert chart', 'showChart')
   .addToUi()
}

Then we add the function that shows the modal:

function showChart() {
 const html = HtmlService.createHtmlOutputFromFile('Chart')
   .setWidth(900)
   .setHeight(600)
 
 SpreadsheetApp.getUi().showModalDialog(html, 'Chart')
}

You can also set up a WebApp with the same template:

function doGet(e) {
 return HtmlService.createHtmlOutputFromFile('Chart')
  .setWidth(900)
  .setHeight(600)
}

Step 3: Get the data from the spreadsheet

Now it’s time to remove the mock data and get the actual data. To do this we’ll use the google.script.run function to asynchronously load the data. This example uses asynchronous logic so it can load the charts library and the data in parallel.

google.charts.load('current', {'packages':['corechart']})
const readyPromise = toAsync(resolve => google.charts.setOnLoadCallback(resolve))
const dataPromise = toAsync((resolve, reject) => {
 google.script.run
   .withSuccessHandler(resolve)
   .withFailureHandler(reject)
   .getData()
})

toAsync is a helper function that makes sure to reject if there is an error that’s not handled by a failure callback:

function toAsync(callback) {
 return new Promise((resolve, reject) => {
   try {
     callback(resolve, reject)
   } catch(e) {
     reject(e)
   }
 })
}

Then we need to add the function in Google Apps Script that actually reads the data from the spreadsheet:

function getData() {
 return SpreadsheetApp.getActiveSpreadsheet()
   .getSheetByName('Sheet1')
   .getRange('A1:C18')
   .getValues()
}

Then simply use both in the main function:

async function drawVisualization() {
 const el = document.getElementById('chart_div')
 
 await readyPromise
 const data = google.visualization.arrayToDataTable(await dataPromise)
 const options = { … }
 new google.visualization.ComboChart(el).draw(data, options)
}

Make sure that the call handles the promise rejection otherwise you may silence errors unknowingly:

document.addEventListener('DOMContentLoaded', () => drawVisualization().catch(console.error))

(Optional) Step 4: Embedding chart as PNG

Google Charts generate SVGs. Unfortunately we cannot embed a SVG so we need to convert it to a PNG. The easiest way to do it is using an external library like canvg which allows us to draw an SVG into a canvas. Then we can export the canvas as PNG.

Add a canvas to your body and the library to your head.

Then you can draw on the canvas using (append to drawVisualization):

const canvas = document.querySelector('canvas')
const ctx = canvas.getContext('2d')
v = canvg.Canvg.fromString(ctx, el.getElementsByTagName('svg')[0].outerHTML)
v.start()
await v.ready()

This will draw it and wait until it’s done.

Now we need a way of sending the generated image to GAS so it can embed it. Thankfully we can get a base64 of the PNG and send it to there:

const base64 = canvas.toDataURL('image/png').split(';base64,')[1]
 
await toAsync((resolve, reject) => {
 google.script.run
   .withSuccessHandler(resolve)
   .withFailureHandler(reject)
   .insertChart(base64)
})

And in Google Apps Script, we define insertChart:

function insertChart(base64png) {
 const blob = Utilities.newBlob(
   Utilities.base64Decode(base64png,  Utilities.Charset.US_ASCII),
   'image/png',
   'chart.png'
 )
 
 SpreadsheetApp.getActiveSpreadsheet()
   .getSheetByName('Sheet1')
   .insertImage(blob, 5, 5, 0, 0)
}

(Optional) Step 5: Close modal and hide charts while being generated

Now that it’s being inserted, I like to automatically close the modal. To do so just append this to drawVisualization:

google.script.host.close()

I also like to not show the chart while being processed. A bit of CSS should be enough:

canvas, #chart_div {
 visibility: hidden;
}
body {
 overflow: hidden;
}

You cannot use display: none because then it will generate charts of the wrong size. Overflow is set to avoid the scroll bars into nothing.

You can also change the screen title and even a loading animation to the modal.