Search code examples
google-apps-script

Chart modify() gives error Exception: Those columns are out of bounds


I started re-using a script attached to a spreadsheet that I made a few years ago. If I remember right, it was working fine but it now throws the error: Exception: Those columns are out of bounds. The script changes the several options on each chart that was saved to their own page. I can't see how if it's saved to it's own page it has columns. I tried .setPosition() which locked up the charts so that they can't load. I found that under embedded charts and I'm not sure if a chart that is given its own sheet  is considered embedded. My code is below if anyone can help.

Here is a copy of the sheets

Thanks Jamie

function GM3_modifyAllCharts() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets1 = ss.getActiveSheet();
 var sheets2 = ss.getSheets();
 for (i = 2; i < (sheets2.length - 6); i++) {
    var shtLabel= ss.getSheetByName("Graph Labels");
    var range = shtLabel.getRange((i-1),1);
    Logger.log(range.getValues());
    var bayValues = range.getValues();
    var range = shtLabel.getRange((i-1),2);
    Logger.log(range.getValues());
    var cropValues = range.getValues();
    var shtData= ss.getSheetByName("GM Data");
    var range = shtData.getRange(1,2);
   Logger.log(range.getValues());
    var dateValues = range.getValues();
    var formattedDate1 = " 1 "; //Utilities.formatDate(new Date(dateValues), "GMT+4", "M-d-yy");
    var formattedDate2 = " 2 "; //Utilities.formatDate(new Date(dateValues), "GMT+4", "D");
    var sheet2 = SpreadsheetApp.setActiveSheet(ss.getSheets()[i]);
    var chart = sheet2.getCharts()[0];
    Logger.log(sheet2.getName()); 

    chart = chart.modify()
   .setOption('titleTextStyle', {color: '#273746  ', fontSize: 14})
    .setOption('title', 'Sw '+ (i-1)+'   '+ bayValues + '   '+ cropValues)
    .setOption('subtitleTextStyle', {color: '#273746  ', fontSize: 14})
    .setOption('subtitle','Date: '+ formattedDate1+'   Julian: '+ formattedDate2)
    .build();

    sheet2.updateChart(chart); // <<<< Here is where the error happens.
  }
}

Solution

  • @Jason, The problem seems to be using the chart option from the upper right corner to move to it's own sheet. I don't think the sheet is seen as a spreadsheet any more but only a chart hence the columns error. If I create a chart in the GM Data sheet I can change the title but if moved to it own page it throws the column out of bounds error. I know this was working a couple years ago so I guess Google changed somthing and broke it. Still, thanks for your help.