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.
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
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.
[{
address: "McDonalds, Nørre Voldgade, Copenhagen",
title: "McDonalds,Copenhagen",
lat: 55.6831144,
lng: 12.572139200000038,
clean address: "Nørre Voldgade 82, 1358 København K, Denmark",
info: "beside Norreport station",
view: "https://maps.googleapis.com/maps/api/streetview?size=640x363&pano=lnqDhRlxJrcVpy577rplpA&heading=135.0393518404748&pitch=-4.57184855488498&fov=65.58300662894399&key=AIzaSyDoWGxNZ2zOLzPyyssH508seSk4vd5YA9U"},
{
address: "McDonalds, Rue de Rivoli ,Paris",
title: "McDonalds,Paris",
lat: 48.8634243,
lng: 2.3337046000000328,
clean address: "184 Rue de Rivoli, 75001 Paris, France",
info: "across from Starbucks",
view: "https://maps.googleapis.com/maps/api/streetview?size=640x363&pano=k3eT9iE_VjICRbeWrjTn6Q&heading=290.11052999545245&pitch=-6.172257319951214&fov=91.10977066711101&key=AIzaSyDoWGxNZ2zOLzPyyssH508seSk4vd5YA9U"},
{
address: "McDonalds, Shaftesbury Ave, London",
title: "McDonalds,London",
lat: 51.5110535,
lng: -0.13389630000006036,
clean address: "25-27 Shaftesbury Ave, Soho, London W1V 7HA, UK",
info: "In Soho - across from st james tavern",
view: "https://maps.googleapis.com/maps/api/streetview?size=640x363&pano=YBjBL9yfOh4_zWjEPqNcCQ&heading=23.594077987732966&pitch=3.5610544610026835&fov=83.38041691240075&key=AIzaSyDoWGxNZ2zOLzPyyssH508seSk4vd5YA9U"},
{
address: "McDonalds, Via Firenze, Rome",
title: "McDonalds,Rome",
lat: 41.901763,
lng: 12.494508999999994,
clean address: "Via Firenze, 58, 00184 Roma, Italy",
info: "below the hotel republicca",
view: "https://maps.googleapis.com/maps/api/streetview?size=640x363&pano=KrKevdAvYXrPuIDIWQIKWg&heading=20.149922927646866&pitch=-8.726164471543427&fov=90&key=AIzaSyDoWGxNZ2zOLzPyyssH508seSk4vd5YA9U"},
{
address: "McDonalds, Calle de la Montera, Madrid",
title: "McDonalds, Madrid",
lat: 40.4198644,
lng: -3.7018295999999964,
clean address: "Calle de la Montera, 47, 28013 Madrid, Spain",
info: "beside the metro",
view: "https://maps.googleapis.com/maps/api/streetview?size=640x363&pano=cLN_6xZOrSpG7puX8K45JQ&heading=301.2074759781531&pitch=-0.6791947791215733&fov=65.85507238915599&key=AIzaSyDoWGxNZ2zOLzPyyssH508seSk4vd5YA9U"}],
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 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, below for your convenience, or published as a public webapp.
var Client = (function (ns) {
ns.init = function () {
resetCursor();
};
ns.createDeck = function () {
spinCursor();
var du = DomUtils.elem;
Provoke.run ("Server", "generateSlides",du('alias').value,du('readerkey').value,du('templateid').value,du('presoname').value)
.then (function (result) {
var url ="https://docs.google.com/presentation/d/" + result.presentationId + "/edit";
App.showNotification ("Your deck", '<a href="'+url+'" target="_blank">is here</a>');
resetCursor();
})
['catch'] (function (err) {
resetCursor();
App.showNotification ("creating deck" , err);
});
}
function resetCursor() {
DomUtils.hide ('spinner',true);
}
function spinCursor() {
DomUtils.hide ('spinner',false);
}
return ns;
})(Client || {});
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 + "_" + 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 modified 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 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)
// 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
indexCorrection += row[e].toString().length - match[0].length ;
// create a batch request to delete that
reqs.push(reqDeleteText (d.objectId + '_' + sindex , startIndex , endIndex ));
// add in the replacement text
reqs.push(reqInsertText ( d.objectId + '_' + sindex , row[e].toString() , startIndex));
}
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
var text = 'street view image';
indexCorrection += row[e].toString().length - text;
// create a batch request to delete that text
reqs.push(reqDeleteText (d.objectId + '_' + sindex , startIndex , endIndex ));
// add some comment
reqs.push (reqInsertText (d.objectId + '_' + sindex, text , startIndex));
// now add the image
// i should really do something here to preserve the aspect ratio...
reqs.push ({
createImage:{
url:row[e].toString(),
elementProperties: {
pageObjectId:templateSlide.objectId+'_'+sindex,
size:d.size,
transform:d.transform
}
}
})
}
}
});
}
});
}
});
return reqs;
},[]);
finally, delete the template
requests.push ({
deleteObject: {
objectId:templateSlide.objectId
}
});
Here’s the insert block. The insertionIndex will match the startIndex at which the template placeholder was found
/**
* generates a text insert req
* @param {string} objectId the object id
* @param {string} text the text to insert
* @param {number} startIndex
* @return {object} the insertion request
*/
function reqInsertText ( objectId , text , startIndex) {
return {
insertText: {
objectId: objectId,
text: text,
insertionIndex: startIndex
}
};
}
And the delete block function
/**
* generates a text delete req
* @param {string} objectId the object id
* @param {string} text the text to insert
* @param {number} startIndex
* @return {object} the insertion request
*/
function reqDeleteText ( objectId , startIndex , endIndex) {
return {
deleteText: {
objectId: objectId,
textRange: {
startIndex: startIndex,
endIndex: endIndex,
type:'FIXED_RANGE'
}
}
};
}
Now we’ve build the batch request, we can just call the API to execute it, and enjoy our new deck.
// execute the update
var response = Slides.Presentations.batchUpdate({
requests: requests
}, id);
}
For more like this, see Ephemeral Exchange