Merging data with a slides template

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 is that it applies to all slides in the deck. This means that if you wanted to do a 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.

Code

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.

Objective

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.

{{field}} will get replaced with values from the data that match on field name - for example {{info}} will be replaced by data.info 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, here, 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 -

Demo

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.

var efx = cEffexApiClient.EffexApiClient;

var alias = "macdo";

var updater = "uxk-a10-b2kdijqnlmfc";

var templateId = "1iLG2bDiNwsUXNO0b3Gxlm4GPgZiRgJVcChkWn-N3BbU";

var presoName = "macdo";

and the data is retrieved with this function

function getDataFromStore() {

// pull from store

var result = efx.read(alias, updater);

if (!result.ok) {

throw JSON.stringify(result);

}

return result.value;

}

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

function generateSlides() {

// get the data from the store

var data = getDataFromStore();

// get the template and the parent folder

var template = DriveApp.getFileById(templateId);

var folder = template.getParents().next();

// make a copy of the slide template

var copy = template.makeCopy(presoName, folder);

var id = copy.getId();

// get the slide template

var template = Slides.Presentations.get(id);

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

// find the template slide (actually just the 2nd one, but could be fancier search)

var templateSlide = template.slides[1];

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

// need to map the created objectids to ones of my choosing so I can find them later

var objectIds = templateSlide.pageElements.map(function(d) {

return d.objectId;

});

we'll also need an alias for each individual slide

objectIds.push (templateSlide.objectId);

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.

var requests = data.reduce(function(reqs, row, sindex) {

// the duplication request

reqs.push({

duplicateObject: {

objectId: templateSlide.objectId,

objectIds: objectIds.reduce(function(p, c) {

p[c] = c + "_" + sindex;

return p;

}, {})

}

});

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.

templateSlide.pageElements.forEach(function(d, pindex) {

// d = templateSlide.pageElements[0]

if (d.shape && d.shape.text && d.shape.text) {

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 modifed by the difference between the length of the old text and the new text.

// as we change the length of the text run, the start index will increasingly get out of step

var indexCorrection = 0;

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

// need to look through each of the textElements

d.shape.text.textElements.forEach(function(t) {

// see if this chunk has some content

if (t.textRun && t.textRun.content) {

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

// there's some content here, see if it needs to be changed

Object.keys(row).forEach(function(e) {

// this will find any matching templated {{properties}}

var rx = new RegExp("\\{\\{" + e + "\\}\\}");

var match = rx.exec (t.textRun.content);

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 functiona 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)

// if we have a match

if (match) {

// first delete the text that is there

// the text to delete is the length of the match + where it starts + the offset of the text element

var startIndex = match.index + (t.startIndex || 0) + indexCorrection;

var endIndex = startIndex + match[0].length;

// fix the indexCorrection to take account of the future amended length of the textRun

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.

else {

// maybe this is an image match

var rx = new RegExp("\\{\\{\\[" + e + "\\]\\}\\}");

var match = rx.exec (t.textRun.content);

if (match) {

// first delete the text that is there

// the text to delete is the length of the match + where it starts + the offset of the text element

var startIndex = match.index + (t.startIndex || 0) + indexCorrection;

var endIndex = startIndex + match[0].length;

// fix the indexCorrection to take account of the future amended length of the textRun