Modify Range in Line Chart with GoogleScript

Question

I want to be able to have a user select a month range (i.e start month and end month) and then have my charts update based on the selection. In this example it is Jan - Sep of 2021. I have written another script that performs a query to select a subset of data based on the range selected by the user. That works, but I need to add/modify the ranges in an existing chart when I run an "update" script. I have a whole page of charts and I want to be able to programmatically and update each with the updated ranges.

I finally got a script working that modifies the Title and subtitle, but it doesn't update the data series. Sometimes it does one, sometimes 2 and sometimes none. I don't get it. The statements look almost identical to what I recorded when I did it manually and recorded a macro, but it doesn't work. This is my first googlescript attempt so the methods, classes, syntax etc. have been a nightmare to navigate. Any help would be much appreciated.

Row 1 should be the horizontal axis Row 2 is data Row 3 is data Row 4 is data

function modify(sheet) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[1]; // "access data on the second sheet"
ss.setActiveSheet(sheet);
var lastColumn = sheet.getRange('W6').getValue(); //for this example 'W6" contains 'J" because the example goes from column A to Column J

var charts = sheet.getCharts();
var chart = charts[0]; // access the first graph
var ranges = chart.getRanges();
var range = ranges[0]; // access the first range

var rangeA1Notation = "A109:" + lastColumn + "109"; //row 109 column A contains the label "Month Year"
var rangeA1Notation2 = "A110:" + lastColumn + "110";//row 110 column A contains the label "Revenue", Cols B through J have dollar values
var rangeA1Notation3 = "A111:" + lastColumn + "111";//row 110 column A contains the label "Coll Goal", Cols B through J have dollar values
var rangeA1Notation4 = "A112:" + lastColumn + "112";//row 110 column A contains the label "Collected", Cols B through J have dollar values

chart = chart.modify()
.removeRange(range)
.setOption("title", "Collections by Month " + rangeA1Notation)
.setOption("subtitle", "Jan 2021 - Sep 2021" + rangeA1Notation2)
//.addRange(range).setTransposeRowsAndColumns(true).setOption("useFirstColumnAsDomain", true) 
//This did not work
.addRange(sheet.getRange(rangeA1Notation))
.addRange(sheet.getRange(rangeA1Notation2))
.addRange(sheet.getRange(rangeA1Notation3))
.addRange(sheet.getRange(rangeA1Notation4))
.build();

sheet.updateChart(chart)
}

Sample Data shown below, the sheet/tab names are on the image:

Answer 1

I finally got it to work. I abandoned the chart.modify approach and used sheet.removeChart(chart), chart = sheet.newChart() and sheet.insertChart(chart) and it works. I would still like to know if the chart.modify could work or why it didn't.

function buildLineChart() {
var spreadsheet = SpreadsheetApp.getActive();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1]; // "access data on the second sheet"

var sheet = spreadsheet.getActiveSheet();
var lastColumn = sheet.getRange('W6').getValue(); //sets the start and end value 
for columns

var rangeA1Notation = "A109:" + lastColumn + "109"; //row 109 column A contains 
the label "Month Year"
var rangeA1Notation2 = "A110:" + lastColumn + "110";//row 110 column A contains 
the label "Revenue" and data
var rangeA1Notation3 = "A111:" + lastColumn + "111";//row 111 column A contains 
the label "Coll Goal" and data
var rangeA1Notation4 = "A112:" + lastColumn + "112";//row 112 column A contains 
the label "Collected" and data


//removes and updates first chart
var charts = sheet.getCharts();
var chart = charts[0];
sheet.removeChart(chart);
chart = sheet.newChart()
.asLineChart()
.addRange(spreadsheet.getRange(rangeA1Notation))
.addRange(spreadsheet.getRange(rangeA1Notation2))
.addRange(spreadsheet.getRange(rangeA1Notation3))
.addRange(spreadsheet.getRange(rangeA1Notation4))
.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_ROWS)
.setTransposeRowsAndColumns(true)
.setNumHeaders(-1)
.setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
.setOption('bubble.stroke', '#000000')
.setOption('useFirstColumnAsDomain', false)
.setOption('subtitle', 'Jan 2021 - Sep 2021')
.setOption('title', 'Collections by Month')
.setOption('annotations.domain.textStyle.color', '#808080')
.setOption('textStyle.color', '#000000')
.setOption('legend.textStyle.color', '#1a1a1a')
.setOption('subtitleTextStyle.color', '#999999')
.setOption('titleTextStyle.color', '#757575')
.setOption('annotations.total.textStyle.color', '#808080')
.setOption('hAxis.textStyle.color', '#000000')
.setOption('hAxis.titleTextStyle.color', '#000000')
.setOption('vAxes.0.textStyle.color', '#000000')
.setOption('height', 248)
.setOption('width', 896)
.setPosition(6, 1, 10, 10)
.build();
sheet.insertChart(chart);
}

Answer 2

You might want to check this issue on Google's Issue Tracker.

It seems that the reason you cannot modify the chart using the modify method is due to the fact that this is an ongoing issue on Google's side. I suggest you star the issue as any updates will be posted there.