Titles in Google Sheets Charts

boost_426 0
Viewed 1239

When I first create a chart in Google Sheets, the Title is in a movable box that I can select and drag around inside the chart box.

If I then change that title programmatically, e.g.

        chart = chart.modify()
        .setOption('title',ChartTitleNameReplacement)
        .build();
        selectedSheet.updateChart(chart);

the Title jumps up to the top of the chart box and cannot be dragged anywhere.

What option setting do I need in the Google Apps Script code to keep that box movable? Alternatively, is there any way of specifying a position in the chart box for the Title?

google-sheets-charts google-apps-script google-sheets
1 Answers

I have experienced the same issue with your situation. I think that this might be a bug. In this case, I achieved to modify the title without locking the title position using Sheets API. The sample script is as follows.

Sample script:

Before you run the script, please enable Sheets API at Advanced Google services.

const updatedTitle = "### updated title ###";

const ss = SpreadsheetApp.getActiveSpreadsheet()
const ssId = ss.getId();
const chart = Sheets.Spreadsheets.get(ssId).sheets[0].charts[0];
delete chart.position;
chart.spec.title = updatedTitle;
Sheets.Spreadsheets.batchUpdate({requests: [{updateChartSpec: chart}]}, ssId);
  • In this case, the title of the 1st chart in the 1st sheet in the active Google Spreadsheet is modified.

Note:

  • When only the title is modified, an error like the internal error occurs. At UpdateChartSpecRequest, there is no fields. I think that this might be the reason of the issue. So I added the existing properties to the request body by modifying the title. Please be careful this.
  • In the current stage, when the title is updated, it seems the title position is reset.
  • This is a simple sample script for modifying the chart title without locking the title position. So please modify this for your actual situation.
  • I couldn't find this issue at the issue tracker. So how about reporting it?

References:

user_71086530
Related