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

Answer 1

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.