Copy of a more extreme example of 'co-linearity`:
seems to have pointed to an acceptable solution/workaround. That is, add data point markers (Point size, in Customise in Chart Editor) - in the image to one [red] data series (also with the line made thinner than the default and the other [blue] series line increased in width).
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!
Best Answer
If the chart is fairly simple, you can use a
sparkline()
formula to recreate it in a cell that remains stationary in the frozen section of the sheet.Use View > Freeze > [n] rows to insert a frozen section. To give the sparkline more space, use Format > Merge cells.
See the sparkline() help page.