How to import custom Combo Chart into Google sheets?

Question

I'm trying to follow this example for creating a custom combo chart in google sheets but can't seem to get the file to import: https://developers.google.com/chart/interactive/docs/gallery/combochart

I copied the HTML into a file called Index.html and in my script editor I have the following code to retrieve the HTML

/**
 * @customfunction
 */
function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index');
}

When I call doGet() from the sheet it says there is an error loading the data. Am I missing a step here?

Thank you!

developers.google.com/apps-script/guides/sheets/functions?hl=en — Cooper

Mateo Randwolf Mateo Randwolf · Accepted Answer · 2021-01-26T09:48:18

That is because the code you are using is for inserting a chart in a web app. From the information you have provided, your intention seems to be to insert a Combo Chart directly in the Spreadsheet with Apps Script. For inserting a combo chart in your Spreadsheet, please refer to this documentation.

To achieve so, you will simply need to get your data from your Spreadsheet's data range and then create and build a chart to finally insert it in the Spreadsheet using the right methods.

The example script below inserts a minimal Combo chart, use it in your bounded script to the Spreadsheet and run it to insert the chart. If you would like to customize it as much as in the example you have provided please go through this piece of documentation which details all the possible customization you can achieve.

function myFunction() {
  // Get sheet
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');

  // Create chart (here is where you should add your chart type, labels, axis, series types and so on)
  var chart = sheet.newChart().setChartType(Charts.ChartType.COMBO).addRange(sheet.getRange('A1:F5')).setPosition(5, 5, 0, 0).build();

  // insert chart in the Spreadsheet
  sheet.insertChart(chart);
}

Also note that you will need to use setPosition to set where in your sheet you want to insert the chart and setChartType to determine which chart type you will be using.

How to import custom Combo Chart into Google sheets?

1 Answers