google-apps-scriptgoogle-visualizationgoogle-workspace

AppScript throwing Exception error of Spreadsheets when trying to access Charts present in a Google Sheet

I am running the below code

var reportSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1HWg9ip6iC7wnw9e80IpM6ISKslMbtO591SLoKHTQGms/edit");

var reportStudentGraph = reportSheet.getSheetByName("student_usage_graph");
  
var reportDoc = DocumentApp.openByUrl("https://docs.google.com/document/d/1RGqWBsUH8vzfDYMf16hIjPJ6fG33BCVQJll_e9fA07k/edit")
 
var body = reportDoc.getBody();

//Get the Student Related Graphs
var student_usage_graphs = reportStudentGraph.getCharts();
Logger.log(student_usage_graphs.length)
  
//Start Writing into the Document
  body.appendParagraph("Academic Delivery and Learning Outcomes Report").setHeading(DocumentApp.ParagraphHeading.TITLE)
  
var graph_table = body.appendTable([["",""]]);
graph_table.setBorderColor('#ffffff');  // White for no border

graph_table.getCell(0,0).appendImage(student_usage_graphs[0]);
element = graph_table.getCell(0,0).findElement(DocumentApp.ElementType.INLINE_IMAGE).getElement().asInlineImage();
element.setHeight(320*element.getHeight()/element.getWidth()).setWidth(320);//ensuring the chart is sized to the table

The above code runs fine till the line where I access the student usage graph while appending the image in the table

graph_table.getCell(0,0).appendImage(student_usage_graphs[0]);

At the above line, I am getting the below error.

Exception: Service Spreadsheets failed while accessing document with id 1HWg9ip6iC7wnw9e80IpM6ISKslMbtO591SLoKHTQGms.

This means that there is no problem with accessing the sheet till this line, only when I access the charts I am getting the error.

I have tried making the chart as Blob as an alternative, which again threw the same error when I am exporting the chart as Blob.

When I comment out the line that I have highlighted I can access the spreadsheet and fetch/write other data from the sheets, wherever in the code tries to access the graphs in the spreadsheet I am getting the error above mentioned.

However when the below line is running

Logger.log(student_usage_graphs.length)

I am getting 1.0 as output which means it is detecting that one chart is present in the sheet, but when I access

student_usage_graphs[0]

is where I am getting the error mentioned.

When I am trying other operations on the sheet reportStudentGraph like read or write using getValue and setValue, I am able to read and write without any errors.

Not sure what mistake is happening here.

Can anybody help me on what I am doing wrong here?

Solution

  • I have experienced the same issue with you. I guess that this might be a bug. At that time, I used the following workaround. This workaround is from Ref and Ref.

    1. Create a Gooels Slide as a temporal file.
    2. Copy a chart from the Spreadsheet to the Slide.
    3. Retrieve an image blob from the Slide.
    4. Put the image blob into the Document.
    5. Delete the temporal file.

    In this workaround, the Spreadsheet chart is put into the Document through the Slide. When this workaround is used in your script, how about the following modification?

    Modified script:

    function myFunction() {
  var reportSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1HWg9ip6iC7wnw9e80IpM6ISKslMbtO591SLoKHTQGms/edit");
  var reportStudentGraph = reportSheet.getSheetByName("student_usage_graph");
  var reportDoc = DocumentApp.openByUrl("https://docs.google.com/document/d/1RGqWBsUH8vzfDYMf16hIjPJ6fG33BCVQJll_e9fA07k/edit");
  var body = reportDoc.getBody();
  var student_usage_graphs = reportStudentGraph.getCharts();
  Logger.log(student_usage_graphs.length)
  body.appendParagraph("Academic Delivery and Learning Outcomes Report").setHeading(DocumentApp.ParagraphHeading.TITLE);
  var graph_table = body.appendTable([["", ""]]);
  graph_table.setBorderColor('#ffffff');


  // --- I modified the below script.
  var temp = SlidesApp.create("temp");
  var image = temp.getSlides()[0].insertSheetsChart(student_usage_graphs[0]);
  temp.saveAndClose();
  var chart = image.asImage().getBlob();
  graph_table.getCell(0, 0).appendImage(chart);
  DriveApp.getFileById(temp.getId()).setTrashed(true);
  // ---


  element = graph_table.getCell(0, 0).findElement(DocumentApp.ElementType.INLINE_IMAGE).getElement().asInlineImage();
  element.setHeight(320 * element.getHeight() / element.getWidth()).setWidth(320);
}

    When I tested your showing script using a COMBO chart, I confirmed the same issue. And, when I tested this modified script with the same chart, I confirmed that no error occurred and the chart was correctly put into Document.

    Note:

    Reference: