You can update the chart's range via a Google Apps Script. It is hard to provide you with an accurate script without seeing your spreadsheet, but this could be used as a starting point:
function onModification(event) {
var sheet = event.source.getActiveSheet();
if (event.changeType == "INSERT_ROW") { // <-- This is true when a new row is inserted
updateGraph(sheet);
}
}
function updateGraph(sheet) {
var charts = sheet.getCharts();
if (charts.length < 1) {
return;
}
var chart = charts[0]; // We operate only on the first graph in this example
var ranges = chart.getRanges();
var range = ranges[0]; // ... and we expect it to have only one range
var dataRange = sheet.getDataRange();
var numRows = dataRange.getNumRows(); // Find the actual number of rows in the sheet
var rangeA1Notation = "A2:A" + numRows;
chart = chart.modify().removeRange(range).setOption("title", "Chart over " + rangeA1Notation).addRange(sheet.getRange(rangeA1Notation)).build();
sheet.updateChart(chart)
}
The first function
is an event listener that runs updateGraph
whenever a new row is inserted (from How can I record the date/time when a new row is added?).
The updateGraph
function is rudimentary, as it expects only one sheet with only one graph. You might have to adjust this for your own spreadsheet.
Feel free to copy the example spreadsheet I've set up for further experimentation.