How do I update my embedded stacked column chart and keep the chart settings?

Question

I'm using two spreadsheet ranges in an embedded stacked column chart. I need to edit the chart ranges when my spreadsheet is updated. The technique mentioned in this article mostly works, but my chart settings that "combine ranges vertically" and "use row 1 as header" are removed (causing the chart to error).

I've looked through documentation for setOptions and haven't found anything useful or obvious. How can I update my chart and keep the settings?

My code:

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dashboard = ss.getSheetByName("dashboard");
  var data = ss.getSheetByName("data");
  var charts = dashboard.getCharts();
  var builder = charts[0].modify().asColumnChart();
  var ranges = builder.getRanges();

  //remove ranges that might have changed
  for(var i = 0; i < ranges.length; i++)
      builder.removeRange(ranges[i]);

  //add the range used for headers
  builder.addRange(
      data.getRange(1,1,1,data.getLastColumn()));

  //add the range used for data
  builder.addRange(
      data.getRange(data.getLastRow()-10, 1, 10, data.getLastColumn()));

  dashboard.updateChart(builder.build());  

Answer 1

I had the same problem and I found the answer on this post:

Google Apps Script Chart Use row 1 as headers

To make it short, if the format of the first row is plain text, then it will will be used as headers for your chart.

In order to do that in your example add the following line of code:

data.getRange(1,1,1,data.getLastColumn()).setNumberFormat('@STRING@');