Is it possible to select a whole column for chart data in Google Spreadsheets?

Question

I've got a Google Spreadsheet that's written to by a sensor system approximately every thirty seconds. Each insertion adds a row to the bottom of the spreadsheet. I've got a graph of this data.

I'd like the graph to always show all the data, updating each time a new row is added. Currently, if I give the chart a data range like A:C, that gets translated into something like A1:C32, where 32 is the current length of the spreadsheet when I save the changes.

Is there a way to get it to work as I described?

Answer 1

The range in the chart's Data - Select ranges box will expand automatically when new rows are inserted within the range, so one workaround would be to modify the code that writes the data on the spreadsheet to use sheet.insertRowBefore(sheet.getLastRow()) instead of sheet.appendRow()or similar.

Make sure you have an placeholder row after the last data row so that the inserts take place after the existing data but within the range defined for the chart.

Answer 2

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.