Set title of currently selected chart from named cell

Question

There's a sample over here that will show how to set the first's chart title to the active cell:

function onOpen(){
  SpreadsheetApp.getUi().createMenu('Charts').addItem('Update title of the first chart from active cell', 'myFunction').addToUi();
}
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var chart = sheet.getCharts()[0];
  chart = chart.modify()
  .setOption('title', sheet.getActiveCell().getValue() || 'Empty')
  .build();
  sheet.updateChart(chart);
}

The script adds a menu and will update the first's chart title to the current active cell.

It would be interesting to do the contrary, for instance you may have multiple sheets and build charts from each sheet range. The script should allow to set the current selected chart's title to a give named cell.

Assuming the scenario above, and while a chart is selected, pseudocode:

1. Get the sheet name from currently selected chart
2. Use reference to cell to get text that will be used for the title, as a general rule each sheet shall have a cell named ChartTitle, therefore using the sheet name from step1 we should be allowed to build a correct reference
3. Set the currently selected chart title to the returned text

Problems:

• Is there a function that will return the currently selected chart?
• If so, is it possible to retrieve the range of the chart in a way the sheet name may be extracted?

Answer 1

So basically you just need to know the name of the sheet where your chart is.

function updateCHARTtitleOnEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SHEET_NAME");
  var chart = sheet.getCharts()[0];// in my case I had only 1 chart on this sheet


  chart = chart.modify().setOption('title', "DYNAMIC CHART TITLE" || 'Empty').build();
  sheet.updateChart(chart);
}

Then, you need to activate a OnEdit trigger for this function and you are done!

Answer 2

The code is mine.

Unfortunately, we can't determine if the chart is currently active.

Still. Again.

Answer 3

Thank you Joni — your code snippet was useful and I've tweaked it slightly:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Chart");
  var title = sheet.getActiveCell().getValue();
  var chart = sheet.getCharts()[0];// in my case I had only 1 chart on this sheet
  chart = chart.modify().setOption('title', title || 'Empty').build();
  sheet.updateChart(chart);
}