5

I want to auto-update the linked objects in google slides (charts and tables). I saw answers from previous questions and assembled the code below : 

function onOpen() {

SlidesApp.getUi() // Or DocumentApp or FormApp.
    .createMenu('Update Charts')
    .addItem("Update now", 'refreshCharts').addToUi();
  refreshCharts();
}

function refreshCharts(){
    var gotSlides = SlidesApp.getActivePresentation().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();
        }
    }
}

It updates the charts when i go click on Update Charts> Update now. But it does not, first of all, update the chart on Opening the slides. And also another issue, it only works with charts right now, is there a way to include tables too in the script, so all charts and table can be updated?

Update to the question

Is there may be a way to directly link a button directly to the Update all button in Tools>Linked objects>Update All

Is it possible to have a button on the slides to directly do this?

jkhadka
  • 2,443
  • 8
  • 34
  • 56

2 Answers2

1

To update the charts, your refreshCharts() function should be the onOpen(). As for the tables, there does not seem to be an equivalent refresh() function as with SheetChart. There is an existing feature request on this issue, you can look into that link for updates.

A workaround for the moment would have to be manually updating them with the refresh button that shows up in the UI from the Linked Objects menu in tools -> linked objects. You can also create a function that will uptdate a table cell by cell, based on the Sheet you're populating it from.

AMolina
  • 1,355
  • 1
  • 7
  • 17
  • Hey @AMolina , can you give me more detail on the second part of your answer? I did not get what you meant by create a function that will update a table cell by cell. How do I proceed with this? – jkhadka Oct 01 '19 at 07:53
  • @hadik Basically meant to open an instance of the Spreadsheet where the information comes from, and use those values to change what's in the table. This is a convoluted way to move forward, but it would get the table updated. – AMolina Oct 01 '19 at 07:58
  • Hi @AMolina can you please show me just the basic part of the code on how to get going with this? Like how to pick up the linked tables from the slides, cause I dont know if there is something like ` slide.getSheetsTables();` to start getting the linked Spreadsheet. – jkhadka Oct 01 '19 at 08:01
  • After checking further, I think doing it how I had suggested would be too convoluted, I'd suggest you try to do it manually with the UI through the **tools->linked objects** sub-menu – AMolina Oct 01 '19 at 13:49
  • oh that is sad :( Do you know if there is a way to add a button on directly on the menu bar that is shortcut to the "Update All" button present in 'Tools>Linked Objects'. – jkhadka Oct 01 '19 at 14:00
  • Afraid not, if there was we could get it working with the API – AMolina Oct 01 '19 at 14:33
0

I wrote your script like this on a Google Spreadsheet that has all the charts and set the trigger to onChange. So, every time there is an update on the Spreadsheet the charts on the specific deck will auto-refresh:

function refreshChartsOnChange(){
    var getSlides = SlidesApp.openById('[ID of the Slide here]').getSlides();

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

        for (var j = 0; j < sheetCharts.length; j++) {
            var sheetChart= sheetCharts[j];
            sheetChart.refresh();
        }
    }
}
BlackWidow
  • 15
  • 9