Refreshing Linked Charts in Google Documents via Scripts

Other Questions Google docs
1

Hey everyone! I’m trying to figure out how to update charts in a Google Doc that are linked to a Google Sheet. I know there’s a way to do this in Slides, but I can’t find anything for Docs.

Basically, I’ve got this document with about 30 tables and charts all connected to one spreadsheet. It’s a pain to manually update each one when the data changes. I was hoping to write a script to make it easier, but I’m stuck.

I’ve looked into using DocumentApp.getActiveDocument(), but it doesn’t seem to have a getCharts() function. The charts might be treated as images, but those don’t have an update option either.

Does anyone know if it’s even possible to refresh these embedded charts in Docs using a script? Maybe there’s a way to do it from the spreadsheet side? Any help would be awesome!

Here’s a bit of code I thought might work, but it’s for Sheets, not Docs:

function refreshCharts() {
  var doc = DocumentApp.getActiveDocument();
  var elements = doc.getBody().getChildElements();
  for (var i = 0; i < elements.length; i++) {
    var element = elements[i];
    if (element.getType() === DocumentApp.ElementType.INLINE_IMAGE) {
      // How to update this if it's a chart?
    }
  }
}

Thanks in advance for any suggestions!

2

I’ve actually tackled a similar problem before, and unfortunately, there’s no direct way to refresh linked charts in Google Docs using Apps Script. The limitation is that Docs treats these charts as static images once they’re inserted.

However, I found a workaround that might help. Instead of trying to update the charts in the Doc, you can update them in the source Sheet and then re-insert them into the Doc. Here’s the general approach:

  1. Use Sheets API to update the charts in your spreadsheet.
  2. Delete the old charts from your Doc.
  3. Insert the updated charts from the Sheet into your Doc.

It’s not perfect, but it’s the closest I’ve gotten to automating this process. You’ll need to use both the Sheets and Docs APIs, and it requires some setup. The trickiest part is matching the old charts to the new ones for proper placement.

If you’re interested, I can share some sample code to get you started. It’s a bit involved, but it might save you time in the long run compared to manual updates.

3

hey luna23, i feel ur pain! been there, done that. sadly, google docs is a pain when it comes to updating linked charts automatically. one trick i’ve used is to create a custom menu in the sheet that triggers a script to update the doc. it’s not perfect, but it beats manual updates. lemme know if u want more details on how to set it up!

4

I’ve encountered this issue before, and it’s frustrating that Google Docs doesn’t have a built-in solution. While there’s no direct method to refresh linked charts, I’ve found a workaround that might help.

Instead of updating charts in the Doc, consider using Google Apps Script to generate a new Doc with fresh data. Here’s the basic idea:

  1. Create a template Doc with placeholders for your charts.
  2. Write a script that reads data from your Sheet, creates new charts, and inserts them into a copy of the template.
  3. Run this script whenever you need to update your report.

This approach maintains your original Sheet as the single source of truth and automates the creation of up-to-date reports. It’s not perfect, but it’s more efficient than manual updates or complex API interactions.

The downside is you’ll have multiple versions of your Doc, but you could implement a system to archive old versions automatically.