Error “Cannot find method insertChart(EmbeddedChartBuilder)” when using Apps Script to create Embedded Charts

Question

I will like to take data from a spreadsheet "dataSheet" (shared with me as View only) to create a chart and output to my own spreadsheet "sheet".

I use Apps Script to create an embeddedChart and encountered this error: Cannot find method insertChart(EmbeddedChartBuilder).

I have followed Google's guide closely https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart but can't resolve the error.

I have debugged step-by-step and realised the error happens at .insertChart (last line of code).

var chartBuilder = sheet.newChart();
chartBuilder.addRange(dataSheet.getRange('R'+earliestRowUsed + 'C1:R'+latestRowUsed + 'C1'))
    .setChartType(Charts.ChartType.COLUMN)  
    .setPosition(5,5,0,0);

//collect data from dataSheet to add to Range.    
for (indicator = 0; indicator < indicatorP.length; indicator++) {  
    indicatorIndex = indicatorList[0].indexOf(indicatorP[indicator]);
    chartBuilder.addRange(dataSheet.getRange('R'+earliestRowUsed + 'C'+(dataCol + indicatorIndex) +':'+ 'R'+latestRowUsed + 'C'+(dataCol + indicatorIndex)));
  }

chartBuilder.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS); //join columns to create a table
chartBuilder.build();
sheet.insertChart(chartBuilder); //error takes place here.

I am open to using Charts or EmbeddedCharts to create my chart for my purpose (which is to take data from a shared view-only sheet to create a chart and embed in my sheet).

For working with Charts and Sheets, I found this Google guide https://developers.google.com/chart/interactive/docs/spreadsheets#embedding-achart-in-a-spreadsheet.

However the guide only shows a manual process to create Charts, and not with Apps Script. If anyone knows how, will appreciate your sharing as I haven't been able to find anything online.

Thank you.

You're not saving the output of .build(). Try sheet.insertChart(chartBuilder.build()); — Diego
Thanks @Diego. I had tried your way too before posting but it was not the cause. Both our methods of writing can be used to build charts. — hzh
Are you sure? I recreated your code with slight modifications (not all of your values indicator values are defined, so I hard-coded some ranges), and I was able to both reproduce and then resolve your issue by using sheet.insertChart(chartBuilder.build());. — Diego
So sorry you are right. Your method is the right one for new charts and mine is to modify existing charts developers.google.com/apps-script/reference/spreadsheet/…. I think my problem is due to something else. However i did not check further and did a walkaround instead. Thanks again. — hzh

RobertLCrocker RobertLCrocker · Accepted Answer · 2019-05-03T08:32:39

I think your problem is the "View Only" permissions. Pretty sure that if you want to take data from a sheet programmatically and use it somewhere else you'll need to ask for Edit privileges.

Error “Cannot find method insertChart(EmbeddedChartBuilder)” when using Apps Script to create Embedded Charts

2 Answers