0

There are several Google Sheets that record different metrics for my organisation. In most of these I have added charts. Now I have linked those charts to Google Slides, so that it acts as a high-level management report. Some slides have only one chart, but some may have three or four. I don't know if that's even relevant.

Every day, after various updates have been run in the Sheets, I open the slides and click Tools > Linked objects > Update all. Except that sometimes I forget, or I'm away, or whatever.

So, can app script automate this process? I know that you can set triggers to run things at a certain time of day, but I have no idea how to write the script. And can it be done without me (the owner) being logged in? Presumably the code would have to list the ID of the slides.

In case it helps, I have created a Goggle Sheets and Google Slides file (with fake data) and shared them publicly.

https://docs.google.com/spreadsheets/d/1gpJ5M8gAIhTXf4YaqEk3uM0Ie8jyCQ5LJbe3qgcX_o8/edit?usp=sharing

https://docs.google.com/presentation/d/1-BAEl4DJOz4X8l5XBRsR-tKf_sNPWnTv5ujdvoghrd8/edit?usp=sharing

Thanks

Improve this question
7
  • Related question: Is there a way in which I can automatically update a table in google slides linked to google sheets without clicking update? Commented Jun 18, 2023 at 12:28
  • Does this answer your question? Google slides auto update links /tables linked from google sheet Commented Jun 18, 2023 at 12:28
  • Sorry no, I can't make either of those work. The 1st one seems to want a spreadsheet ID and table ID, maybe a chart ID? Do charts have their own IDs? I can't find one on the chart in the test files I shared. The second one seemed more promising, but it reported "TypeError: Cannot read properties of undefined (reading 'getSheetsCharts')" and I have no idea what to do about that. My real slide deck has links from many different spreadsheets and I can update them all manually with just a few clicks. So I would have thought that it would be a fairly easy task for Apps Script. But maybe not! Commented Jun 19, 2023 at 18:11
  • Would you please clarify two things: #1) There are several Google Sheets Do you mean i) several spreadsheet files or ii) a single spreadsheet file which contains several sheets/tabs, or iii) something altogether different (in which case would you please explain in detail)? #2) Now I have linked those charts to Google Slides...Some slides have only one chart but some may have three or four. Do you mean that i) several "Slides" files or ii) a single "Slides" file , or iii) something altogether different (in which case would you please explain in detail)? Commented Jun 21, 2023 at 9:11
  • but it reported "TypeError: Cannot read properties of undefined (reading 'getSheetsCharts')" Would you advise whether you installed and ran this from a spreadsheet file or from a slides file. FYI, the second question DOES work. When you provide the information in the "clarification" question, I'll post an answer. Commented Jun 21, 2023 at 9:15

1 Answer 1

Reset to default
0

You want to automate the process of updating charts in Google Slides from the linked charts in Google Sheets. You have a single Slides file that contains several linked charts from multiple Google Sheets.

Google slides auto update links /tables linked from google sheet provides the key to updating charts in linked sheets. In that question, the script was bound to the Google Slides file.

In this answer,

  • the script is bound to one of the Google spreadsheet files.
  • insert the ID for your Slides file
  • when the spreadsheet charts have been updated, run the script to automatically update the Slides.
function refreshCharts(){
  var slideId = "<<Insert Slides ID>>"
  var pres = SlidesApp.openById(slideId)
  var gotSlides = pres.getSlides();

  for (var i = 0; i < gotSlides.length; i++) {
    var slide = gotSlides[i];
    var sheetsCharts = slide.getSheetsCharts();

    for (var k = 0; k < sheetsCharts.length; k++) {
      var shChart = sheetsCharts[k];
      shChart.refresh();
    }
  }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.