chris lockhart
Automatically refresh all Google Sheets data sources and charts in a deck
September 4, 2020

Let's say that you have a Google Slides presentation with Google Sheets charts that pull data from BigQuery. How do you do a global update, refreshing all data sources, charts, and then refreshing those components in a deck? We can do this with script.google.com. Note that this builds off our previous post.

Create a project and then dump in the following code.

function refresh_slides(slidesId) {
  // open slides
  var slides = SlidesApp.openById(slidesId).getSlides();

  // loop over every slide in the deck
  for (var i = 0; i < slides.length; i++) {
    // open specific slide
    var slide = slides[i];

    // loop over all charts in the slide
    var charts = slide.getSheetsCharts();
    for (var j = 0; j < charts.length; j++) {
      // open specific chart
      var chart = charts[j];

      // refresh sheet containing chart data
      var sheetId = chart.getSpreadsheetId();
      var sheet = SpreadsheetApp.openById(sheetId);
      SpreadsheetApp.enableAllDataSourcesExecution();
      sheet.getDataSourcePivotTables()[0].refreshData();
      sheet.waitForAllDataExecutionsCompletion(120);

      // refresh the chart in the deck
      chart.refresh();
    }
  }
}


That's all there is to it! Then, you can schedule it with Google Apps Script triggers.