Updating Embedded Charts in Google Docs with Google Apps Script

Question

TLDR; How do I use the Script Editor in Docs to update an embedded Sheets chart in the document?

I know there is a script that does this for Google Slides, but I'm trying to do it in Google Docs and can't find any documentation thereof.

https://developers.google.com/slides/how-tos/add-chart#refreshing_a_chart

To be specific, I have a Google Doc. This doc contains about thirty tables and embedded charts that are all linked to a separate Google Sheet. All thirty come from a single Google Sheet. Now, I can have our non-geeky people click on all thirty "Update" hover buttons every time the spreadsheet changes, but I expect the spreadsheet to change a lot, and I would like to idiot-proof the document to ensure it's always up-to-date. As far as I can tell, this isn't a feature Google Apps does out of the box, so I wanted to write a script to do it.

But I can't find any way to access an EmbeddedChart from a Google Doc.

If I could run something like this like you can in Sheets, I could probably figure it out, but I can't:

var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var charts = sheet.getCharts();
    for (var i in charts) {
       var chart = charts[i];
       // Update the chart
    }
}

Although docs has the following function, DocumentApp.getActiveDocument(), an object Document doesn't contain a function getCharts(). I believe they're considered to be Images, but Images don't have an update function.

Is it even possible to access/update an EmbeddedChart in Docs using a script? Maybe by running the script through the spreadsheet on edit and updating the doc from there? Seems weird that you can do it in Slides of all things, but not Docs.

I did not. We ended up using MS Office. We were really looking for a cross-platform solution, but we never found one. — GeneM

dima K dima K · Accepted Answer · 2018-09-18T13:09:13

in Google Docs DOM charts as InlineImages here is the script that logs images attributes. but in they looks to be read only and documentation for this method ends with dead end: "null if the element contains multiple values for this attribute." https://developers.google.com/apps-script/reference/document/inline-image#getLinkUrl()

function myFunction() {
  var doc = DocumentApp.getActiveDocument()
  var body = doc.getBody()
  var pars = body.getParagraphs()
  var atts = img.getAttributes();
  // Log the paragraph attributes.
  for (var att in atts) {
    Logger.log(att + ":" + atts[att]);
  }
  
  for (var par in pars) {
    Logger.log(pars[par].getText());
    var chN = pars[par].getNumChildren()
    Logger.log(chN);
    if(chN>0){
      var type = pars[par].getChild(0).getType()
      Logger.log(type);
      if(type=="INLINE_IMAGE"){
            
        var atts = pars[par].getChild(0).getAttributes()
        Logger.log(JSON.stringify(atts));
          for (var att in atts) {
            Logger.log(att + ":" + atts[att]);
          }
      }
    
    }
  }
  return
}

Updating Embedded Charts in Google Docs with Google Apps Script

1 Answers