How to Embed google visualization chart into spreadsheet

Question

I've been looking at google visualization for the past few days and have been successful in getting it to work on single web pages or with spreadsheets using the built in insert -> chart creator. But when I edit the javascript myself I want this data to still be visible on a google spreadsheet where it came from. I've looked into apps script and I can only get it to work by publishing it as an application which then sends me to a completely different web page.

Is there any way that I can just embed my custom javascript such as

function drawVisualization() {
    // Create and populate the data table.
    var data = google.visualization.arrayToDataTable([
      ['Region', 'count'],
      ['West', 8000],
      ['South', 7272],
      ['Northeast', 5333],
      ['Midwest', 4444],
      ['Southwest', 5714]
    ]);

    // Create and draw the visualization.
    new google.visualization.PieChart(document.getElementById('visualization')).
        draw(data, {title:"Sales Per Region"});
  }

into a google doc or spreadsheet or is that not supported?

Rudolf Manusachi Rudolf Manusachi · Accepted Answer · 2013-07-02T22:25:41

You can use the Charts Services and display through UiApp

example GS Code for your PieChart

function getChart(dataTable){
  var chartBuilder = Charts.newPieChart()
       .setTitle('Sales Per Region')
       .setDimensions(600, 500)
       .set3D()
       .setDataTable(dataTable);

  return chartBuilder.build(); 
}

function showChart(){
  var dataTable = Charts.newDataTable()
       .addColumn(Charts.ColumnType.STRING, "Region")
       .addColumn(Charts.ColumnType.NUMBER, "Count")
       .addRow(['West', 8000])
       .addRow(['South', 7272])
       .addRow(['Northeast', 5333])
       .addRow(['Midwest', 4444])
       .addRow(['Southwest', 5714])
       .build();

  var chart = getChart(dataTable)

  //insert into the spreadsheet
  SpreadsheetApp.getActiveSheet().insertChart(chart)
}

for show in doc You need to replace the last line to

  DocumentApp.getUi().showSidebar(UiApp.createApplication().add(chart))

But anyway, these charts will in dialog boxes (or sidebar), not in the content of the document. In the document, you can insert them through transform into image, so

    var chart = getChart(dataTable)
    var imageChart = chart.getAs('image/jpeg');

And to add to the content

  DocumentApp.getActiveDocument().getBody().appendImage(imageChart);

How to Embed google visualization chart into spreadsheet

1 Answers