Is there a way to take the `Title Text` from the Google Sheets cell?

Question

Is there a way to take the Title Text from the Google Sheets cell?

So whenever I change the cell string then it should appear in the Chart automatically.

For example: I have May - 2021 in cell A1. Can I reference this in Title Text, like using =A1 or =Sheet1!A1? But it does not work.

I got this code, but how do I reference the cell into the code?

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Charts').addItem('Update', 'myFunction').addToUi();
}

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var chart = sheet.getCharts()[0];
  chart = chart.modify()
  .setOption('Summary!Q123', sheet.getActiveCell().getValue() || 'Summary!Q123')
  .build();
  sheet.updateChart(chart);
}

user_15897861 · Answer

Although it is not possible to link a cell value as title, there is a trick that may solve your problem by creating a merge cell, camouflage, as the title. Then it will be changed, based on the text value you enter :)

Script:

You may use the following script to update the title by referring to A3 every time you execute the code. Basically, you are almost correct:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var newtitle = sheet.getRange(3, 1).getValue();

  var charts = sheet.getCharts()[0];
  var chart = charts.modify()
    .setOption('title', newtitle)
    .build()
  sheet.updateChart(chart)

  var scnCharts = sheet.getCharts()[1];
  var chart2 = scnCharts.modify()
    .setOption('title', newtitle)
    .build()
  sheet.updateChart(chart2)
}

Is there a way to take the `Title Text` from the Google Sheets cell?

1 Answers