Google-sheets – Update Google Drive Chart based on which row selected

google sheetsgoogle-apps-scriptgoogle-sheets-charts

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?

Best Answer

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!

Related Topic