How to copy a Google Sheets chart to Google Documents

Question

Apparently Google Docs is able to embed charts from Google Sheets (I mean "normal" range-based charts and not ones from Google Charts). So, if I change the chart colours for example, I can update these changes in Google Docs.

Unfortunately, Google Docs does not have script recorder features and it doesn't help when I record the copy / paste from Google Sheets (of course, I only intended to use the record feature for help).

I believe I should embed the chart as any object via its url (at least its id part). I'm aware of method such as appendImage() (Body class), but I cannot find equivalent for charts.

Can anyone give me some guidance using Google Apps Script (not from API)?

Link to the Google Docs reference

Thanks for any insights!

Kessy Kessy · Accepted Answer · 2020-11-03T10:57:41

You can create a function to get the chart from sheets and insert it to the Document:

function getChart() {
  var sheet = SpreadsheetApp.openById('SPREADSHEET_ID').getSheets()[0]
  var documentBody = DocumentApp.openById('DOCUMENT_ID').getBody();
  var arr_charts = sheet.getCharts()
  documentBody.appendImage(arr_charts[0].getBlob());
}

That will get you the chart as an image on the Document.

To update it you can create a function that is triggered when the sheet is modified, onEdit(e), that gets the chart from the document and it replaces it with the new one.

How to copy a Google Sheets chart to Google Documents

1 Answers