"sheet chart" vs. "embedded chart" - how to access via google apps script

[Jochen Brand]

Hi all,

I am unable to find a way to access a google sheets chart that lives on it's own sheet (rather than being part of a 'normal' spreadsheet).

I have this basic code that does something when the sheet is a object on a sheet, but it throws an error when trying to apply on a "sheet chart":

Error

Exception: Those columns are out of bounds.

function updateCharts2() {

  var spreadsheetId = 'xxx';

  var ss = SpreadsheetApp.openById(spreadsheetId);

  var dataSheet = ss.getSheetByName('Actuals');

  var dataRange = dataSheet.getRange("A1:K180");

 

  var chartSheet = ss.getSheetByName('Actuals');

  var chart = chartSheet.getCharts()[0];

  chart = chart.modify()

      .clearRanges()

      .addRange(dataRange)

      .build()

  chartSheet.updateChart(chart);

}

How would I do this with a chart that is not embedded but a "sheet chart"?

many thanks 

Jochen

[Carlo Alberto Ferraris]

FWIW, I'm running in seemingly the same exact problem: trying to update the ranges on a "sheet chart" fails - on the updateChart line - with

Error

Exception: Those columns are out of bounds.

[Jochen Brand]

Hi

tbh, I abandoned the idea and went forward with a helper sheet, where I referenced the data source for example with =indirect("data!A1:K100").

My initial problem was that the data sheet was updated by a script, which made the source range on the chart update with it - which I did not want.

So I thought first to programmatically correct the chart's data range....

Using =indirect works even easier as I can do it simply with formulas, but I have no idea if that would work for you.

regards