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 Answer 1

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!

2
  • 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, 2014 at 23:55
  • Try making a copy of the spreadsheet and running it from there. Feb 21, 2014 at 8:02

Your Answer

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

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