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

Pat Ryan
  • 5
  • 2
  • 4
  • Related question: [Is there a way in which I can automatically update a table in google slides linked to google sheets without clicking update?](https://stackoverflow.com/q/76208849/1330560) – Tedinoz Jun 18 '23 at 12:28
  • Does this answer your question? [Google slides auto update links /tables linked from google sheet](https://stackoverflow.com/questions/48897088/google-slides-auto-update-links-tables-linked-from-google-sheet) – Tedinoz Jun 18 '23 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! – Pat Ryan Jun 19 '23 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)? – Tedinoz Jun 21 '23 at 09: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. – Tedinoz Jun 21 '23 at 09:15
  • My "real" setup has several different Google Sheets files, some with multiple tabs, all feeding into ONE Slides presentation. E.g. slide 1 has a chart from spreadsheet-1, slide 2 has a chart from spreadsheet- 2/tab-1, slide 3 has a chart form spreadsheet-2/tab-2 etc. – Pat Ryan Jun 23 '23 at 07:37
  • I'm not saying the second one doesn't work, just that * I * couldn't make it work in my situation. I fully understand that's not necessarily a problem with the code. I'm not really an IT person. When I click Tools > Linked objects > Update all, it doesn't require me to specify any spreadsheet IDs or charts IDs. It just "knows" where they are linked from. So I was hoping that Apps Script could simply replicate that action. I shared the much simpler test files in my question, hoping someone could automate that and then I could try to learn from it, but I guess it's not that simple. Thanks. – Pat Ryan Jun 23 '23 at 07:50

1 Answers1

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();
    }
  }
}
Tedinoz
  • 5,911
  • 3
  • 25
  • 35