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:

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:

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.

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.

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!

And we just end and close the function.

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

https://pixabay.com/photos/industry-craft-work-e-cigarette-5050243/

If this kind of stuff has value, please support me by tossing a one time PayPal kibble or monthly on Patreon
Profile Picture for Alan Levine aka CogDog
An early 90s builder of the web and blogging Alan Levine barks at CogDogBlog.com on web storytelling (#ds106 #4life), photography, bending WordPress, and serendipity in the infinite internet river. He thinks it's weird to write about himself in the third person.

Leave a Reply

Your email address will not be published. Required fields are marked *