Google slides auto update links /tables linked from google sheet

Question

I have a Google Slides presentation that has some linked cells/table to data in Google Sheets. Currently, I have to manually click each linked cell/table to update values. I need a script for Google Slides that would auto-update / batch-update / refresh these links, so that the values/tables get auto-updated. Is that possible?

Answer 1

Both yes and no.

Charts

Yes! those can be batch updated:

function onOpen() {

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

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();
        }
    }
}

Source: https://stackoverflow.com/a/48254442/

Shapes/Tables

No: https://issuetracker.google.com/issues/64027131

Update From Google 24/5/2019

https://gsuiteupdates.googleblog.com/2019/05/bulk-update-docs-slides.html

Answer 2

Has anyone gotten this to work?

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();
    }
}
}

I just get this error message " Unable to refresh chart. Please verify that the chart is a valid chart in Google Sheets."

Answer 3

The following example setup assumes there are five slides with some linked charts:

• first slide - main slide title/subtitle (no chart)
• second slide - contains a chart linked from the chart of a pivot table that counts the R&D staff and what they planned to do from the Google Form responses sheet
• third slide - contains a chart linked from the chart of a pivot table that counts the IME staff and what they planned to do from the Google Form responses sheet
• fourth slide - contains a chart linked from the chart of a pivot table that counts the PMO staff and what they planned to do from the Google Form responses sheet
• fifth slide - contains a chart linked from the chart of a pivot table that counts the total staff and what they planned to do from the Google Form responses sheet

The function below will update the slides with linked charts. For it to work, it requires the Advanced Google Services: Google Slides API https://developers.google.com/slides/.

You can activate this advanced feature from your Google Apps Script IDE under Resources > Advanced Google Services... > Google Slides API. The API version is set to v1. If you don't enable it, the script will complain Slides is not defined at updateSlideCharts(...)

See the link below for more detail about RefreshSheetsChartRequest in Google Slides API v1: https://developers.google.com/slides/reference/rest/v1/presentations/request#RefreshSheetsChartRequest

function updateSlideCharts() {
  var presentation = SlidesApp.openById(YOUR_SLIDE_ID); //you need to get this slide id from your slide URL 
  //if empty
  if (presentation == null) throw new Error('Presentation was not found');
  Logger.log("%s id = %s", presentation.getName(), presentation.getId());
  
  var slides = presentation.getSlides();
  if (slides == null) throw new Error('Slides were not found');
  Logger.log("Total of slides in %s: %d", presentation.getName(), slides.length);
  

  var presentationId = presentation.getId();
  var presentationRndChartId = slides[1].getSheetsCharts()[0].getObjectId();
  Logger.log("ObjectId of \"%s\": %s", slides[1].getSheetsCharts()[0].getTitle(), presentationRndChartId);
  var presentationImeChartId = slides[2].getSheetsCharts()[0].getObjectId();
  Logger.log("ObjectId of \"%s\": %s", slides[2].getSheetsCharts()[0].getTitle(), presentationImeChartId);
  var presentationPmoChartId = slides[3].getSheetsCharts()[0].getObjectId();
  Logger.log("ObjectId of \"%s\": %s", slides[3].getSheetsCharts()[0].getTitle(), presentationPmoChartId);
  var presentationStaffChartId = slides[4].getSheetsCharts()[0].getObjectId();
  Logger.log("ObjectId of \"%s\": %s", slides[4].getSheetsCharts()[0].getTitle(), presentationStaffChartId);
  
  var requests = [{
    refreshSheetsChart: {
      objectId: presentationRndChartId
    }
  }];
  
  // Execute the request.
  var batchUpdateResponse = Slides.Presentations.batchUpdate({
    requests: requests
  }, presentationId);
  Logger.log('Refreshed linked Sheets charts for \"%s\"', slides[1].getSheetsCharts()[0].getTitle());

  requests = [{
    refreshSheetsChart: {
      objectId: presentationImeChartId
    }
  }];
  
  // Execute the request.
  batchUpdateResponse = Slides.Presentations.batchUpdate({
    requests: requests
  }, presentationId);
  Logger.log('Refreshed linked Sheets charts for \"%s\"', slides[2].getSheetsCharts()[0].getTitle());

  requests = [{
    refreshSheetsChart: {
      objectId: presentationPmoChartId
    }
  }];
  
  // Execute the request.
  batchUpdateResponse = Slides.Presentations.batchUpdate({
    requests: requests
  }, presentationId);
  Logger.log('Refreshed linked Sheets charts for \"%s\"', slides[3].getSheetsCharts()[0].getTitle());
  
  var requests = [{
    refreshSheetsChart: {
      objectId: presentationStaffChartId
    }
  }];
  
  // Execute the request.
  var batchUpdateResponse = Slides.Presentations.batchUpdate({
    requests: requests
  }, presentationId);
  Logger.log('Refreshed linked Sheets charts for \"%s\"', slides[4].getSheetsCharts()[0].getTitle());
}

Answer 4

Linked Table

As of Aug 2021, there is still no .refresh() function for linked tables, but if your use case allows you to know the source spreadsheet and range at script writing, you can update a linked table by reading the text values, font colors, font styles, etc., from the spreadsheet and writing them to the table. Something like this:

 function updateSheetsChart()
{
  // The range on the source spreadsheet
  var sourceRange = SpreadsheetApp.openById(SOURCE_SPREADSHEET_ID).getRange(SOURCE_TABLE_RANGE)
  var source = {
    'values': sourceRange.getDisplayValues(),
    'backgrounds': sourceRange.getBackgrounds(),
    'textStyles': sourceRange.getTextStyles(),
    'fontColors': sourceRange.getFontColors()
  }
  // The linked table on the presentation
  var table = SlidesApp.getActivePresentation().getPageElementById(SHEETS_TABLE_OBJECT_ID).asTable()
  var columnCount = table.getNumColumns()
  var rowCount = table.getNumRows()
  for (var col = 0; col < columnCount; col++)
  {
    for (var row = 0; row < rowCount; row++)
    {
      var cell = table.getCell(row, col)
      // Cell text
      var cellText = cell.getText()
      cellText.setText(source.values[row][col])
      // Background color
      cell.getFill().setSolidFill(source.backgrounds[row][col])
      // Font style (bold)
      var cellTextStyle = cellText.getTextStyle()
      cellTextStyle.setBold(source.textStyles[row][col].isBold())
      // Text color
      cellTextStyle.setForegroundColor(source.fontColors[row][col])
    }
  }
}

You can add more lines for font size, italics, link URLs, etc. See the documentation for Range, Sheets TextStyle, and Slides TextStyle classes for the corresponding methods.

There are plenty of limitations: this won't copy full rich text (multiple colors or font styles in one cell), for example.

Also see this answer for a simpler workaround (but it doesn't transfer the cell formatting).