How to embbed a Charts.Chart to Google Sheet (Script Apps)

user_11719716 0
Viewed 491

I'm looking for a way to embed a Charts.Chart to a Google Sheet (but not only, I also want to be able to insert it to other supports such as userform, Google Doc, etc.)

function generateChart(data) {
  
  var chartBuilder = Charts.newBarChart();
    // Miscellaneous such as titles, dimensions and data
    // ..
    ;
  
  var chart = chartBuilder.build();
  
  return chart;
}

Also, I don't want to directly use the chart values from the spreadsheet range (there will be some data manipulation..), but used if from data where is a struct of array.

data = {
  field1: [..],
  field2: ..
}

So, basically I want to avoid something like as it creates a dependency to the Sheet.

var sheet = SpreadsheetApp.getActiveSheet();
var chartBuilder = sheet.newChart()
  // ..
  ;

If I write sheet.insertChart(generateChart(data)), I get the following error. Which makes sense because I'm not dealing with same kind of Chart, but is there a workaroud?

Exception: The parameters (Charts.Chart) don't match the method signature for SpreadsheetApp.Sheet.insertChart.

I'm wondering if I shouldn't convert it as blob in order to make it compatible with Google Sheet Chart format, but I'm sure there are more straightforward ways to achieve it.

Thanks for any insights!

google-apps-script google-visualization charts
1 Answers

EmbeddedChart is not part of the Class Charts

As per the documentation for Charts says creates a basic data table, populates an area chart with the data, and adds it into a web page as an image. You can get the image URL and the Blob as well.

As a Workaround

By using Sheets

I'd use the insertImage function making use of the URL provided by Charts.

function myFunction() {
  var data = Charts.newDataTable()
    .addColumn(Charts.ColumnType.STRING, "Year")
    .addColumn(Charts.ColumnType.NUMBER, "Sales")
    .addColumn(Charts.ColumnType.NUMBER, "Expenses")
    .addRow(["2004", 1000, 400])
    .addRow(["2005", 1170, 460])
    .addRow(["2006", 660, 1120])
    .addRow(["2007", 1030, 540])
    .addRow(["2008", 800, 600])
    .addRow(["2009", 943, 678])
    .addRow(["2010", 1020, 550])
    .addRow(["2011", 910, 700])
    .addRow(["2012", 1230, 840])
    .build();

  var chart = Charts.newColumnChart()
    .setTitle('Sales & Expenses')
    .setXAxisTitle('Year')
    .setYAxisTitle('Amount (USD)')
    .setDimensions(600, 500)
    .setDataTable(data)
    .build();

  var htmlOutput = HtmlService.createHtmlOutput().setTitle('My Chart');
  var imageData = Utilities.base64Encode(chart.getAs('image/png').getBytes());
  var imageUrl = "data:image/png;base64," + encodeURI(imageData);

  // Insert the image in the A1
  SpreadsheetApp.getActiveSheet().insertImage(imageUrl, 1, 1);
}

By using Docs

I'd use the insertInlineImage

function myFunction() {
  var data = Charts.newDataTable()
    .addColumn(Charts.ColumnType.STRING, "Year")
    .addColumn(Charts.ColumnType.NUMBER, "Sales")
    .addColumn(Charts.ColumnType.NUMBER, "Expenses")
    .addRow(["2004", 1000, 400])
    .addRow(["2005", 1170, 460])
    .addRow(["2006", 660, 1120])
    .addRow(["2007", 1030, 540])
    .addRow(["2008", 800, 600])
    .addRow(["2009", 943, 678])
    .addRow(["2010", 1020, 550])
    .addRow(["2011", 910, 700])
    .addRow(["2012", 1230, 840])
    .build();

  var chart = Charts.newColumnChart()
    .setTitle('Sales & Expenses')
    .setXAxisTitle('Year')
    .setYAxisTitle('Amount (USD)')
    .setDimensions(600, 500)
    .setDataTable(data)
    .build();

  var image = chart.getAs('image/png')
  
  // Insert an image chart
  var doc = DocumentApp.getActiveDocument();

  var cursor = doc.getCursor();
  if (cursor) {
    cursor.insertInlineImage(image);
  } else {
    doc.getBody().insertImage(0, image);
  }
}

Reference

Google Apps Script > Charts

user_124303160
Related