Google Apps Script refresh Sheets-Charts not working

user_12889782 0
Viewed 115

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.

google-slides google-apps-script google-sheets
1 Answers

In your situation, how about using flash() 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

  2. Copy and paste the chart to the 1st page of Google Slides with the link of Spreadsheet.

  3. 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:

user_71086530
Related