Update Combo Chart Title Dynamically

Question

I have a combo chart, but I couldn't change the title of the combo chart dynamically, but when I change the chart to a column chart or line chart, the title successfully changes but not with the combo chart.

Here is the code I used to change the title dynamically

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

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

}

The title of the chart will change depending on the text written/chosen from var newtitle = sheet.getRange(7, 2).getValue();

Hope someone can help with the code. Thank you in advance

Answer 1

In my experience, unfortunately, it seemed that the title cannot be directly changed. When your showing script is run, an error like Exception: Service Spreadsheets failed while accessing document with id ###. occurs. So, in my case, I used a workaround. How about the following workaround?

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var newtitle = sheet.getRange(7, 2).getValue();
  var chart = sheet.getCharts()[3]; // 4th chart in the active sheet is used.
  var chartId = chart.getChartId();
  var chartObj = Sheets.Spreadsheets.get(ss.getId(), { ranges: [sheet.getSheetName()], fields: "sheets.charts" }).sheets[0].charts.find(c => c.chartId == chartId);
  var series = chartObj.spec.basicChart.series.reduce((o, { type }, i) => (o[i] = { type }, o), {});
  var updatedChart = chart.modify().setOption('title', newtitle).setOption('series', series).build();
  sheet.updateChart(updatedChart);
}

• When this script is run, the title of the combo chart of var chart = sheet.getCharts()[3] is changed.

Note:

• This is my workaround. I think that there might also be other approaches. So, this is just one workaround.

Added:

As another approach, how about modifying the title using Sheets API as follows?

function myFunction2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheet = ss.getActiveSheet();
  var newtitle = sheet.getRange(7, 2).getValue();
  var chart = sheet.getCharts()[3]; // 4th chart in the active sheet is used.
  var chartId = chart.getChartId();
  var { spec } = Sheets.Spreadsheets.get(ssId, { ranges: [sheet.getSheetName()], fields: "sheets.charts" }).sheets[0].charts.find(c => c.chartId == chartId);
  spec.title = newtitle;
  Sheets.Spreadsheets.batchUpdate({ requests: [{ updateChartSpec: { chartId, spec } }] }, ssId);
}