Build google chart by using google script and query function

Question

From a spreadsheet and using google script,

I'm trying to select a range of data from a specific sheet with conditions (where) by using 'query' function. But I don't want to regenerate another sheet with the new range of selected data.

Next after I would like build/update a chart in another sheet from the same spreadsheet.

I didn't find a way to do it.

Thank you

That's a good question. I just figure out there is two way to make charts: Charts Service and Class EmbeddedChart. I think it's not possible to use Charts Service to build it in a sheet, So i suppose I should use EmbededChart. But with that one, only way to give data to build the chart is to provide Range. So I do not have choice to extract data from my data sheet in another sheet. I dont like it because data are redondante and make the spreadsheet heavier. With Charts Service it's possible to load filtered data, But not possible to insert this one in spreadsheet, only in a dashboard — MarcoBros_
Since query doesn't give you a range, you can make a filter function to get the ranges of the wanted data on your already existing sheet and feed that to the EmbeddedChart class instead. — Salix

Jacques-Guzel Heron Jacques-Guzel Heron · Accepted Answer · 2019-10-04T13:32:30

I wrote an example code to accomplish your request. This code uses the Embedded Chart class to create a chart from a table made with a formula using QUERY. This code will first set the formula, after that it will detect the range of the results table, and after that it will use that range to create a chart on another sheet in the same spreadsheet.

I used an example table of two columns from which I searched the ones which has a value greater than 499 on the second column. This is the code:

function buildChart() {
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var graphSheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  var searchTable = dataSheet.getRange('D1').setFormula(
    '=QUERY(A1:B100,"SELECT A, B WHERE B > 499",1)');
  var searchTableRows = dataSheet.getRange('D1').getDataRegion(SpreadsheetApp
    .Dimension.ROWS);
  var searchTableRange = searchTableRows.getDataRegion(SpreadsheetApp.Dimension
    .COLUMNS);
  var chartBuilder = dataSheet.newChart();

  chartBuilder.addRange(searchTableRange).setChartType(Charts.ChartType.LINE)
    .setOption('title', "MarcoBros_'s Chart").setPosition(1, 1, 0, 0);
  graphSheet.insertChart(chartBuilder.build());
}

Since this is just an example, you will have to modify it a bit to adapt it to your project. Please, let me know if you need some help with that or you have any doubt about the code.

Build google chart by using google script and query function

1 Answers