Mass Producing Google Slides from Sheets: Scripts Are Your Friends

With technology work I find Need is more of a spawner of Invention than Desperation (or Necessity).

Not only did a need for a screen timer for the Open Education Global Conference spawn some productive play with OBS, earlier in the planning I came across another need where the pursuit added some new tools to the belt.

In setting up materials for our session facilitators, I saw a need to have a nice title slide for each of the presentations to use as a transition. With some 140 sessions, I was not about to generate them by hand. Yuck.

We had all the information needed in a Google Spreadsheet that help the data for the conference planning. A monster sheet with like 12 tabs, and a final schedule sheet built off a pile of formulas that had about 18 columns.

But among the 18 were the three items I wanted per slide – the session title, the names of presenters, and a URL for the session in our conference site.

Here is an example of the end product:

A sample title side with session name "The Impacts of the MOOCs in Higher Education During the Pandemic: A Taiwan Experience" a list of 8 presenters, and a URL. The details don't matter. Yet. — A sample slide used for announcing a session, and it was spawned by a script!

You can also see the full deck that was produced magically in about 45 seconds by a script that is explained below.

I found what seemed like the ideal add on in the Google Marketplace called Slide Creator:

The Google Slides Creator add-on from apps experts features a presentation merge function for Google Slides! Create custom Google presentation templates as a Google Slide and maintain personalized content, such as customer master data, price information and addresses in Google Sheets. Create marketing order customer specific slides with individual images, links or charts, like monthly KPI or Reporting diagrams, based on any Google Docs Slides presentation as template.

Great! I downloaded and started to play, and quickly discovered on activating that I had missed the fine print: “By using Free-Version of the add-on you can create up to 25 Google Docs Slides presentations per Day with a maximum of 10 Slides in your presentation template.“

Ahh, I fell into the old upsell hole. Silly me.

So I went back to the search well, and added some more keywords like “script”, and got to the method that worked. This is from some ad agency named UpBuild but hey, they shared a method for How to Inject Data from Google Sheets Into a Google Slides Presentation.

They offered a way to copy a sample “container” presentation that houses a template, and also includes a Google Script that does the work. Now as soon as you say “script” many eyes go glassy. Mine perk up.

Invariably someone else’s script is not just click and go, but in prying it open, experimenting, I always walk away having learned more than some push button product.

This kind of explanation for me is perfect (much more so than a video). All the steps, with a few screenshots to point the way. So you get a slide deck with one slide, and in it are placeholders like {{landing page}} – I know right away that these are the things that get replaced by data from the spreadsheet.

I changed up the Slide Master with the background for the conference,a dn created three placeholder text fields- one for the title, another for the presenters, and a third for the session URL. Create as many field as you have data/columns to pluck from.

A template with the conference backdrop and three fields to populate

But you get to the fun part via Tools –> Script Editor. I’ll break it down, but will also provide a link to a version you can explore.

The variable and function names in the original were obviously associated with the example being something in business; so I made them more relevant to my use.

We have a single function that starts by setting up the call to get the spreadsheet, and working our way down the hierarchy to get to the sheet within that has the data:

function generateTitleSlides() {
  // URL for spreadsheet source, make sure includes '/edit at end
  var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/*****mumbojumbogoogleurlcruft****/edit"; 
  
  // create a reference to the spreadsheet
  var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
  
  // now we make another for this slide deck
  var deck = SlidesApp.getActivePresentation();
  
  // and now we get down to naming the particular sheet where our data lives. This is why we name our sheets, folks. 
  // get in the habit
  var sheet = ss.getSheetByName('Full Schedule');

function generateTitleSlides() {

// URL for spreadsheet source, make sure includes '/edit at end

var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/*****mumbojumbogoogleurlcruft****/edit";

// create a reference to the spreadsheet

var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);

// now we make another for this slide deck

var deck = SlidesApp.getActivePresentation();

// and now we get down to naming the particular sheet where our data lives. This is why we name our sheets, folks.

// get in the habit

var sheet = ss.getSheetByName('Full Schedule');

Next we call the parts of the spreadsheet we want to read from, I load them into a variable named conference. You could call yours fred. When I was testing I used just a few rows. Then I commented out the first one and used the full declaration at run time:

  // testing just use a few rows
  //var conference = sheet.getRange('A2:D5').getValues(); 
  
  // for real use them all, change to represent the range in your sheet
  // These are all the conference items
  var conference = sheet.getRange('A2:D197').getValues();
  
  // uncomment for a first test to peek at data
  // Logger.log(conference);

// testing just use a few rows

//var conference = sheet.getRange('A2:D5').getValues();

// for real use them all, change to represent the range in your sheet

// These are all the conference items

var conference = sheet.getRange('A2:D197').getValues();

// uncomment for a first test to peek at data

// Logger.log(conference);

For a first run you can use the Logger line to check the data you are getting. That’s what I did.

Next we get the slides in the deck. I have a title slide at the beginning, my template slide is the second one, but remember that array start counting at 0… so think like an array.

  // get all the slides
  var slides = deck.getSlides();
  
  // get the slide with the template
  var templateSlide = slides[1];
  
  // current length of the slide deck
  var presLength = slides.length;

// get all the slides

var slides = deck.getSlides();

// get the slide with the template

var templateSlide = slides[1];

// current length of the slide deck

var presLength = slides.length;

Now we get down to marching through all the rows, this is loop time! Our conference spreadsheet was structured so there were rows representing breaks between sessions; these are identified in the spreadsheet by a title named “(break)”- I added an extra conditional to skip these rows. Most likely you won’t need this check.

All the stuff in session maps to columns in the spreadsheet (array again) item [0] is column A, [1] is B, etc. I load the data into three aptly named variables.

  // loop through the conference data, each item is a session
  conference.forEach(function(session){
    // proceed if we have data and it is not a cell that contains a string that marks a gap in the schedule
    // (this was specific to the structure of this spreadsheet, most will not have it)
    if(session[0] && session[0]!='(break)'){
    
      // these map to the columns of the sheet that have the data we want
      var sesstionTitle = session[0];
      var sessionPresenters = session[1];
      var sessionURL = session[3];

// loop through the conference data, each item is a session

conference.forEach(function(session){

// proceed if we have data and it is not a cell that contains a string that marks a gap in the schedule

// (this was specific to the structure of this spreadsheet, most will not have it)

if(session[0] && session[0]!='(break)'){

// these map to the columns of the sheet that have the data we want

var sesstionTitle = session[0];

var sessionPresenters = session[1];

var sessionURL = session[3];

The rest of the code in the loop came from the original script. I can deduce it creates a new slide by duplicating the template, then it marches through the “shapes” on a slide, which include the fields, and then loads the data from my variables into matching fields.

Once I got the script working, I did not dwell too much on what was going on here.. .it worked!

      templateSlide.duplicate(); //duplicate the template page
      slides = deck.getSlides(); //update the slides array for indexes and length
      newSlide = slides[2]; // declare the new page to update
    
     // I guess shapes are the names for all elements in a slide?
     var shapes = (newSlide.getShapes());
       shapes.forEach(function(shape){
         // replace the placeholders with real content
         shape.getText().replaceAllText('{{title}}',sesstionTitle);
         shape.getText().replaceAllText('{{presenters}}',sessionPresenters);
         shape.getText().replaceAllText('{{link}}',sessionURL);
      }); 
      
     // update the slides, move the new one to the end (I think, Alan did not fiddle here)
     presLength = slides.length; 
     newSlide.move(presLength); 
      
    } // end our conditional statement
  }); //close our loop of conference sessions

templateSlide.duplicate(); //duplicate the template page

slides = deck.getSlides(); //update the slides array for indexes and length

newSlide = slides[2]; // declare the new page to update

// I guess shapes are the names for all elements in a slide?

var shapes = (newSlide.getShapes());

shapes.forEach(function(shape){

// replace the placeholders with real content

shape.getText().replaceAllText('{{title}}',sesstionTitle);

shape.getText().replaceAllText('{{presenters}}',sessionPresenters);

shape.getText().replaceAllText('{{link}}',sessionURL);

});

// update the slides, move the new one to the end (I think, Alan did not fiddle here)

presLength = slides.length;

newSlide.move(presLength);

} // end our conditional statement

}); //close our loop of conference sessions

And we just end and close the function.

// Remove the template slide (I left the template in in case I messed up)
// templateSlide.remove();
  
}

// Remove the template slide (I left the template in in case I messed up)

// templateSlide.remove();

}

The original script deleted the template. I commented that line out. I use this version as my generator, and when I got the slides I wanted, I shared a duplicate of the deck that I removed the template and the scripts.

I don’t know if all the explanation helps. But you can make a copy of my demo version of the real one I used (you will need a spreadsheet and to modify the script). But I would defer to the blog post I started from; their explanation is likely better than mine.

Regardless, this little play for a conference need got me doing some scripting I had never done before.

More than that, I know I might have something down the line where I want to generate a slide deck from a spreadsheet. And having some greasy elbows here, I don’t have to rely on some upsell tool that is most likely more limited than what I can do in ScriptVille.

Whether the script mumbo jumbo means anything does not matter. And creating title slides for conference is not uber significant. The seed to plant is that if you can organize information in a structured format of rows and columns, you can automate the creation of it in a slideshow format.

To me, buying an app is serving me a fish dinner; giving me a script I can modify is a lesson in fishing. I’d rather fish. You?

Featured Image: Pixabay photo by Manfred Schindler