google-apps-scriptgoogle-sheetsgoogle-slides

Google Apps Script refresh Sheets-Charts not working


I have a slide with an embedded chart from a sheets document. I'm updating the data for the sheet, and then sending a refresh to the sheet-chart and it's not working. When I open the slides document I can see the chart in that doc is updated, but only when I click on the Update button does it refresh:

My code to update the sheet data:

var values = [[ date, value ]];
var range = sheet.getRange("A"+rowIndex+":B"+rowIndex);
range.setValues(values);
rowIndex++;

My code to refresh:
jsonItem['slideIndex'] is a relative pointer to which slide I'm working on.

Deck.getSlides()[jsonItem['slideIndex']].getSheetsCharts().forEach(function(c){c.refresh();});

As you can see I'm using a nuclear option here, I'm iterating through all SheetsCharts on my slide and refreshing every single one, and I can't seem to make it work.


Solution

  • In your situation, how about using flush() as follows?

    Modified script:

    var values = [[ date, value ]];
    var range = sheet.getRange("A"+rowIndex+":B"+rowIndex);
    range.setValues(values);
    rowIndex++;
    
    // do something
    
    SpreadsheetApp.flush(); // Added
    
    // do something
    
    Deck.getSlides()[jsonItem['slideIndex']].getSheetsCharts().forEach(function(c){c.refresh();});
    

    Sample script:

    Unfortunately, from your question, I couldn't imagin your whole script. So, I added a simple sample script for refreshing the chart on Google Slides after the Spreadsheet is updated. Here, it is considered the following sample situation.

    1. Create a chart on the 1st sheet in Google Spreadsheet. The data is as follows. The columns "A" and "B" has the values.
        h1  h2
        a1  1
        a2  2
        a3  3
        a4  4
        a5  5
    
    1. Copy and paste the chart to the 1st page of Google Slides with the link of Spreadsheet.
    2. Update the value of the chart on the sheet and refresh the chart on the slide by a script.

    Script:

    // Modify the value of "B2" from 1 to 10.
    SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange("B2").setValue(10);
    
    SpreadsheetApp.flush();
    
    // Refresh the chart.
    SlidesApp.openById("###").getSlides()[0].getSheetsCharts()[0].refresh(); // Please set the Google Slides ID.
    

    Reference: