1

I have a table with some pretty basic data (it's a time series going across the columns, and different budget items going down). I want to create a chart that plots a particular row's data against time. When I click on a different row, I want the chart to update to show this row's data. Is this possible?

1

I don't think this is possible, since there is no onClick-trigger.
There are alternatives, though, using scripts. I've built two different ways.

Dropdown
Use a dropdown where you select the number of the column to choose from.

A button (drawing)
Click on a drawing with a script assigned to it updating the scripts source range.

See example of both here. The "Click me" drawing has the script updateChart-function assigned to it.

Since you have your chart-ranges on the x-axis I've used Mogsdad's method to Switch rows / columns programmatically. The method requires the 2D Arrays Library, read here how to install it. It also requires you to have a sheet named Scratch, you can hide it, though.

Here's the code used:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == "Chart Sheet"){
    //var sourceRange = e.source.getActiveRange();
    var sourceRow = 1;//sourceRange.getRow();
    var sourceColumn = 1;//sourceRange.getColumn();
    var sourceRange = sheet.getRange(sourceRow, sourceColumn)
    var sourceValue = sourceRange.getValue();
    var lastCol = sheet.getLastColumn();
    var newRange = sheet.getRange(sourceValue + 1, 2, 1, lastCol);


    if(sourceRow == 1 && sourceColumn == 1){ //Address to cell with dropdown, placed in corner not to interfere with getLastColumn

      var srcData = sheet.getRange(sourceValue + 1, 2, 1, lastCol).getValues();

      // Transpose the table (using 2D Array Library)
      var scratchData = ArrayLib.transpose(srcData);

      var numRows = scratchData.length;
      var numCols = scratchData[0].length; // assume all rows are same width

      // Write scratch values to scratch sheet.
      var scratchSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scratch");
      scratchSheet.getRange(1, 1, numRows, numCols).setValues( scratchData );
      SpreadsheetApp.flush();



      var chart = sheet.getCharts()[0];
        var oldRange = chart.getRanges()[0];
        chart = chart.modify()
        .removeRange(oldRange)
        .addRange(scratchSheet.getDataRange())
        .build();
        sheet.updateChart(chart);
      }
    }
}

function updateChart(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastCol = sheet.getLastColumn();
  var row = sheet.getActiveRange().getRow();
  var newRange = sheet.getRange(row, 1, 1, 3);

  var srcData = sheet.getRange(row, 2, 1, lastCol).getValues();

  // Transpose the table (using 2D Array Library)
  var scratchData = ArrayLib.transpose(srcData);

  var numRows = scratchData.length;
  var numCols = scratchData[0].length; // assume all rows are same width

  // Write scratch values to scratch sheet.
  var scratchSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Scratch");
  scratchSheet.getRange(1, 1, numRows, numCols).setValues( scratchData );
  SpreadsheetApp.flush();

  var chart = sheet.getCharts()[0];
  var oldRange = chart.getRanges()[0];
  chart = chart.modify()
  .removeRange(oldRange)
  .addRange(scratchSheet.getDataRange())
  .build();
  sheet.updateChart(chart);
  sheet.getRange(1, 1).setValue(row - 1); //This row is only necessary if both methods are used
}

I hope this helps, ask me if you have any questions!

  • Thanks for that. I can't seem to get your example to work though. I tried changing both the drop down and pressing the update button, but nothing causes the graph to change. – user61241 Feb 20 '14 at 23:55
  • Try making a copy of the spreadsheet and running it from there. – Punchlinern Feb 21 '14 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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