Merging data with a slides template

NOTE – as of 20 Nov 2017, Ephemeral exchange is migrating to a Firestore/ Firebase cloud function back end. See Migrating from App Engine to Cloud function

There are a few tutorials out there about how to use replaceAllText with a Slides template in order to generate a deck where placeholder values are replaced with values with a data set. The problem with replaceAllText mailmerge kind of thing (multiple copies of the same slide but with different contents), you can’t use replaceAllText, so things get a whole lot more complicated. This article will show you how to do that, including replacing placeholders with images, and is part of the set of demos associated with Ephemeral Exchange, which supplies the data for driving the slides.


Most of the demos and libraries mentioned in these pages are available in their own github repo, but for convenience there is a consolidated repo where I’ll push all the associated code. If you’d like to contribute, please make a PR there.



Here you’ll find an example of the template and the finished deck.
The template looks like this

The template looks like this

and an example finished slide looks like this


The data has been enriched with geocoding and images using some of the Ephemeral Exchange demo apps, and currently looks like this, from which you can deduce that I would like to make 5 slides  of the same format, one for each data item.

My template placeholders are of two types

  • {{field}} will get replaced with values from the data that match on field name – for example {{info}} will be replaced by for each data item.
  • {{[field]}} indicates that it the data value refers to a url, so it should replace it with an image

Get it

This app is on github, below for your convenience, or published as a public webapp.

To use, you just need to create a template and publish your data to Ephemeral Exchange. Here’s what it looks like –


The code

Just start by setting up a few variables for the test, including a few keys that will be used to retrieve the test data from the effex store.

and the data is retrieved with this function

Now we can start generating slides – firstly get the data,  duplicate the template slide deck, and pull out the slides.

The deck consists of more than one slide, but only one is required for duplicating for each data item

Next I need to find the objectIds of each of the elements on the template slide, and store them for later.

we’ll also need an alias for each individual slide

We’ll be using batchUpdate to create the deck, so the request body is an array of commands in sequence. We’ll need to repeat each set for each data item. One of the problems with batchUpdate is that nothing happens till you’ve built the entire request pack, so that means that you don’t know the object id of things you haven’t created yet – but you still need to refer to them throughout the request pack. Luckily, there is a way using objectIds, to assign a name of your own to the duplicate elements, so I’m using the list of page elements I created earlier to generate a name I can refer to for each element, for each of the data items.

Now for each element on the the template slide, we’ll find any text boxes with something in them – since my template values will be in one of those.

An element can contain multiple textElements. A textElement consists of some styling information, along with its content, and its character start and end positions. The reason that there are multiple textElements in an element is that they can each have different styling. The problem this gives is that the start and end positions will be wrong as we substitute text in the same element, because the text being substituted will be a different length to the text that replaced it. We therefore need to keep a correction factor to apply to start and end positions, that gets modified by the difference between the length of the old text and the new text.

Now look through each of the text elements finding any with some content

When we find some content, see if there are any template placeholders that need replaced by text from the data.

If we do get a match {{field}}, then we need to replace the placeholder with the data text. There is no replace in slides, so we need to execute an insert and a delete. We’ll look at those functions later on.  See how the indexCorrection is used to adjust for the difference in length between the old and new content and notice how the objectId is individualized for the duplicate element (even though we are still being driven by the template layout)

Perhaps this is a match for an image substitution {{[field]}}. The code is similar, except that we need to create an image using the url data value. The size of the image and its position on the page is copied from the element that holds its placeholder.  The rest of this code in this section is simply closing off the .reduce loop and block conditions we’ve come across on this page so far.

finally, delete the template

Here’s the insert block. The insertionIndex will match the startIndex at which the template placeholder was found

And the delete block function

Now we’ve build the batch request, we can just call the API to execute it, and enjoy our new deck.

For more like this, see Ephemeral Exchange

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.