4

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?

0

2 Answers 2

2

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.

1
  • Almost 7 years later, this is still the workaround that I use as well. (For small data sets, I use sheet.insertRowAfter(1) where row 1 is the header, effectively sorting the data descending. For large data sets this is too slow though.)
    – Arjan
    Apr 27, 2020 at 17:48
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.