How to update the data range of an existing graph using app script? [Google Sheets]

Question

I have created the script below, which runs from an onedit function for when cell J1 is edited. The graph exists in the sheet titled 'Daily Data'. The data it will be using is from a sheet titled 'Long Term Data'.

I used the following link as guidance: https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart.

Thank you for any help.

function onEdit(e) {

  //This IF statement ensures it will only run when cell J1 is edited:
  if (
    e.source.getSheetName() == "Daily Data" &&
    e.range.columnStart == 10 &&
    e.range.columnEnd == 10 &&
    e.range.rowStart >= 1 &&
    e.range.rowEnd <= 1 
  ) { 

    var spreadsheet = SpreadsheetApp.getActive();
    var daily_data = spreadsheet.getSheetByName("Daily Data");
    var LTD_data = spreadsheet.getSheetByName("Long Term Data");

       //ABOVE HAS BEEN TESTED AND RUNS SUCCESFULLY. THE BELOW DOES NOT...

    var chart = daily_data.getCharts()[0];
    var range = LTD_data.getRange("B2:J3")
    chart = chart.modify()
        .addRange(range)
        .build();
    spreadsheet.updateChart(chart);
  }
}
;

Sabayon

Answer 1

• You want to update chart in the sheet of Daily Data using Google Apps Script.

If my understanding is correct, how about the following modification?

Modification point:

• updateChart() is the method of Class Sheet. But in your script, updateChart() is used for Class Spreadsheet. By this, the script doesn't work.

When above point is reflected to your script, it becomes as follows.

Pattern 1:

In this pattern, the range is added to the existing ranges.

Modified script:

From:

spreadsheet.updateChart(chart);

To:

daily_data.updateChart(chart);

Pattern 2:

In this pattern, the existing ranges are removed and new range is added.

Modified script:

From:

var chart = daily_data.getCharts()[0];
var range = LTD_data.getRange("B2:J3")
chart = chart.modify()
    .addRange(range)
    .build();
spreadsheet.updateChart(chart);

To:

var chart = daily_data.getCharts()[0];
var range = LTD_data.getRange("B2:J3")
var ranges = chart.getRanges();
chart = chart.modify();
ranges.forEach(function(range) {chart.removeRange(range)});
var modifiedChart = chart.addRange(range).build();
daily_data.updateChart(modifiedChart);

Note:

• In above case, the simple trigger of OnEdit event trigger can be used.

References:

• updateChart(chart)
• removeRange(range)

If I misunderstood your question and this was not the result you want, I apologize.

Faun