Add array as "range" of EmbeddedChart in Google sheets

2
votes

Brief: I want to change the data in an Google sheets EmbeddedChart by using an array generated within the embedded Google script without pouring the data into cells first.

Long: I have an bound script that updates a preexisting spreadsheet. I modify the data in js and want to change an embedded chart to use the generated array of data without putting the data on the table. I technically do not care that it is an EmbeddedChart I just want to make a summary page with a graph without polluting the document with an extra sheet. Would prefer not to make an HTML page out of the sheet.

I have tried various options.

.setDataTable() is not a method of EmbeddedChartBuilder, unlike normal (HTML) Google Charts. I am assuming I am missing something simple, like some class that converts an array to a pseudo-Range instances akin to a DataTable or Excel's names, without touching the sheet.

Worst case scenario: I am not aware of a method to add a div with id attrib in Google sheets, is there one?

Ways tested:

  • sheet.getCharts()[0].modify().addRange([2,3,4,5]); //Cannot convert Array to Range.
  • sheet.getCharts()[0].modify().addRange("{2,3,4,5}"); //Cannot find method addRange(string).
  • (Desparate...) sheet.getCharts()[0].modify().setDataTable(); //Cannot find function setDataTable in object EmbeddedChartBuilder.
  • (Desparate...) sheet.updateChart(chartFromCharts); //Cannot find method updateChart(Chart).

  • Magically convert array to a range object without saving on a sheet

    //Demo script. function populate() { var a=[1, 2, 3, 4, 5]; var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var chart=sheet.getCharts()[0].modify(); chart.setOption('title', "Changed in script..."); cchart=chart.asColumnChart(); dt=Charts.newDataTable().addColumn(Charts.ColumnType.STRING, "Categorical abscissa").addColumn(Charts.ColumnType.NUMBER, "Numerical ordinate").addRow(["A",5]).addRow(["B",6]).build();

    //Try 1. Desparate just in case it accepts arrays. //chart.addRange(a); //Cannot convert Array to Range. //cchart.addRange(a); //Cannot convert Array to Range. //Try 2. Desparate just in case it accepts DataTable //chart.addRange(dt); //Cannot find method addRange(DataTable). //Try 3. Desparate hack... //new google.visualization.ColumnChart(getCharts()[0].getId()).draw(dt, options); //ReferenceError: "google" is not defined //Could I have accessed it anyway? //$(getCharts()[0].getId()).html("Hacked") //ReferenceError: "$" is not defined. //again... //document.getElementByID(getCharts()[0].getId()) //ReferenceError: "document" is not defined. //Try 5. //sheet.updateChart(Charts.newColumnChart().build()); //Cannot find method updateChart(Chart). //Try 4. Missing method in documentation?! //chart.addDataTable(a); //Cannot find function addDataTable in object EmbeddedChartBuilder.

    //cchart.addDataTable(a); //Cannot find function addDataTable in object EmbeddedColumnChartBuilder.

    sheet.updateChart(chart.build()); }

1
javascriptgoogle-apps-scriptgoogle-sheets
what have you tried so far? Could you clarify a little bit more on what you want to do?... check this document on how to ask questions in Stack Overflow stackoverflow.com/help/how-to-ask – Gerardo
My aim is simply to make a chart in google sheets without having to make a new sheet with the data required and without making an HTML page or similar outside of the sheet —as that would defeat its purpose. Thanks. – Matteo Ferla

1 Answers

3
votes

Instead of using Embedded Chart use normal Charts object and use .getAs(MimeType.PNG) to add the chart to the spreadsheet as an image.

Edit: I've tried this solution and because of google's bug when you get the spreadsheet as PDF the images inserted by script do not show. Google please fix this, it's been 3 years since it was reported and they're still "working on it"..