If there’s one thing guaranteed to drive an analyst to drink or violence, it’s a request to update a 500-slide Powerpoint deck with thousands of once-tidy datapoints into a wipe-right-on-click abomination.

I suspect that marketers are doing everything in their power to drag us analysts into the mud, to suffer as they suffer.

Luckily, with help from Google’s Apps Script and their APIs, analysts can output all their data into a nice, tidy Google Sheet and auto-populate it into a Google Slides document with a single button press. Think Mail Merge for Google Slides.

Here’s a quick demo of what I mean:

(Mobile users, please click on the video title to view in YouTube app until I figure out adding breakpoints in blogdown, sorry!)

This post looks at how to do this, by following these steps:

  1. Copying over some template documents.
  2. Enabling the Slides & Sheets API in a Google Developer Project.
  3. Setting up some field codes in your template, so the API knows what to replace.
  4. Populating replacement text in a Google Sheet.
  5. Editing a little apps script.
  6. Pressing a button and revelling in your new-found power.

There’s quite a lot to do, but you only have to do it once. After everything is set up, you never have to adjust a shape fill again. Let’s get to it:

Copying over template documents

I’ve set up 2 template documents to get you started.

Open each of these documents, click the ‘file’ menu, then ‘Make a copy’. Make sure that you also add each file to your Google Drive, so it can be found by the Sheets and Slides APIs (File > Add to My Drive).

Open up Apps Script

We’re using to read in data from Google Sheets using the , then sending this data (again using Apps Script) to a new Slides document using the .

To make this work for you, you’ll need to change a few values in Apps Script.

Open up Apps Script by navigating to the Slides API Google Sheet you just copied over (titled “An end to death by Powerpoint”). Click on Tools > Script Editor.

The Script Editor interface

The Script Editor interface

You should see a screen like the one above. There are several scripts attached to this Sheets document - you can navigate between the scripts using the pane on the left hand side (marked in red). You’ll be returning to this tab throughout the steps below, so keep it handy.

Enabling the APIs in a GCP project

Navigate to . You’ll need a Google Cloud Platform (GCP) project in which you can enable the APIs. If you don’t know what this means, please refer to the for more information.

Once you’re in your GCP project, click on the top-left hamburger menu and navigate to APIs and services --> Enable APIs and Services.

Search for “Slides API”, click on the “Google Slides API” button and then make sure the API is enabled. Repeat the steps above for the Sheets and Drive APIs.

Finally, you need a note of your project number as we’ll be using it shortly.

This is available on the home screen of Google Cloud Platform ()

Getting your Google Cloud Platform Project Number

Getting your Google Cloud Platform Project Number

Keep it secret, keep it safe.

Set the project in Apps Script

Because you’ve copied these documents over from my Google Drive, you need to re-home them in your own GCP project so they can have access to your enabled APIs and documents.

Return to the Apps Script tab in your browser and click on Resources > Cloud Platform Project from the top menu.

Changing the GCP project

Changing the GCP project

Enter the project number, which you so meticulously noted down earlier, in the text entry field and click the “Set Project” button, then confirm the change in the confirmation window.

Once this completes, you should see a small text notification to confirm the change. You can close the window after this.

A quick(ish) demo

Let’s see the merge in action.

  1. Edit some values in column B (for text replacements) or column F (for new images) of the Google Sheets document you just copied over. If you edit anything in column F, make it a link to a publicly accessible image. You can also change the name of your output deck in cell J3.
  2. Click on the “Merge to slides” button.
  • The first time you run this, you’ll need to go through some authentication steps. See below for details.
  1. Cell J4 should change to *** PENDING ***, to indicate that your slides are being generated.
  2. When ready, J4 should turn green and provide you with a link to your new deck of slides.

First-time Authentication

The first time you use the script, you’ll have to grant each API access to your Google account and also accept use of an unverified app.

The three auth steps needed for first-time use

The three auth steps needed for first-time use

  1. After you click “Merge to slides”, you’ll be greeted with the following auth login screen: Click on ‘Continue’.

  2. Shortly afterwards, a terrifying screen will appear. This is important - my apps scripts have not been verified by Google and you should only continue if you’re comfortable doing so. You can read through all the code in the Apps Script window in order to see everything that’s happening. If you’re not comfortable, don’t authorise this app (that will be the end of this demo for you, though). If you are comfortable, click on ‘Advanced’ and Go to Slides API (unsafe).

  3. Assuming you accept, you should now be greeted with an authorisation screen, which is asking for access to your Google Drive. Click “Allow” if you’re happy to continue.

Assuming you proceed, you can run the script and your slides should be generated - get your link from cell J4.

If that’s enough cognitive load for you for one day, you can use the placeholder values provided here, move and change elements in the Slides Template as you see fit, and the merge should work for you. Adding / removing new fields is possible with a few more steps, but if you get bored you should be able to start out with this bare bones template.

If you want to build your own, more flexible templates and entry documents, read on.

How it all works

There are 2 main documents for you to work with:

The Google Sheet, which stores your editable values.

The Google Sheet Fields you can edit

The Google Sheet Fields you can edit

Text Replacements

  1. Field codes. These are unique placeholder values used to identify elements within your slide document to be replaced.
  2. Replacement text. This is where you enter the value you’d like to appear in place of the Field Code when you merge the slides.
  3. Field notes. A notes field for you to remind yourself what value should be entered in the Replacement text column.

Image Replacements

  1. Image code. Unique placeholder values to identify elements to be replaced with images.
  2. Image name. A description of the image to be included.
  3. Image link. A hyperlink to an image file you’d like to be inserted into the target object. Note that images will be cropped and centred into the target object.

Output details

  1. Template slide name. The name of the Google Slides document in your gDrive which contains the field codes / image codes you’d like to have swapped out. You can define multiple template documents - more on that later.
  2. Output deck name. What you’d like your merged document to be called.
  3. Latest Slides Link. A hyperlink to your generated slides. After you click the ‘Merge to slides’ button, this should turn red and display “PENDING” as a cell value. When the merge is complete, it should turn green and provide a link to your new slides.

The Google Slides Document

  • The Google Slide template, which has your template layout and placeholder objects.

If you’ve ever used mail merge, you probably understand this already. This is how the Slides template works:

How the values are matched in the template document and populated in the Slides document

How the values are matched in the template document and populated in the Slides document

Anywhere you define a {{fieldCode}} in your Google Slide can have its value updated as part of the merge. When the Apps Script calls the Slides API, it replaces the text / updates the image of any elements found in the Slides template which matches the field code. (Side note - it’s not mandatory to use {{}}, we do that simply to define unique character strings to search and replace on.

The way the API call is configured, it will update the value of multiple matches for the same field code. So, for example, if you put 17 {{companyLogo}} fields in your template document and provide a valid image URL in the Google Sheet, you should see 17 image replacements in your generated document.

Customising: adding and editing fields

If you successfully ran the demo, you should be able to customise the template slides and data collection sheet - adding new fields / removing unwanted ones from the collection sheet and Slides template.

To do this, you’ll need to:

  1. Add in your own {{fieldCode}} placeholders in the slide deck, then update the Google Sheet to include the new {{fieldCode}} placeholders and values.
  2. Copy over some auto-generated JavaScript from the Google Sheet –> Apps Script.
  3. Run the merge.

Let’s run through each step:

1. Add in your own {{fieldCode}} placeholders & update the Google Sheet

Open up your slide deck and start customising the document as you wish - you can add in new slides or edit existing elements. (You can also alter the slide master).

  1. Anywhere you’d like to see a new ‘merged’ value, add in the placeholder text.
  2. Return to the Google Sheet, and add a new row for your new value.

You must fill out the Field code (column A) and the Replacement Text (column B). Field notes are optional but recommended to help you keep track of what is what.

Repeat the above steps for any new text fields you’d like to add. You can follow the same process for images - add in a placeholder shape in the Slides template with your desired image dimemsions, fill it out with a {{fieldCode}} placeholder then return to the Google Sheet and add the {{fieldCode}}, Image name and Image Link information to the Sheet. Image links must be to publicly accessible image files. (There’s a good tip on using Google Images to publicly host images ).

2. Copy over the JavaScript

(It’s worth it, I promise).

With your fields all populated, head over to the “Request Statements” worksheet in the Google Sheet. This document contains a bunch of Apps Script text which is generated based on the values included in your “API fields” sheet. We need to get this into the Apps Script functions so the API calls search and replace on your new values.

1. Text selectors

First, we want to grab all our selector scripts, which read in the values from our Google Sheet and store them in variables to be sent to the API for updating.

  1. Copy all the text from cell B2 into your text editor of choice (I am a fan of Sublime Text).
  2. When you paste, you’ll see opening and closing double quotations - get rid of those.
  3. Navigate to your Apps Script tab and open the script called get_text.gs. Paste in the text you copied between the comments which say // PASTE text selectors below / // END text selectors (lines 3 - 45 of the script at the time of writing).
Replacing the text selection code

Replacing the text selection code

2. Text replacement requests

Next we will build an object containing multiple requests.

  1. Copy all the text from cell C2 (in the ‘Request Statements’ worksheet) and paste into a text editor.
  2. Remove the opening and closing double-quotes and remove the final comma from the last request in the list.
  3. Navigate to your Apps Script tab and back to the get_text.gs script. Paste in the text you copied between the comments which say // PASTE text requests below / // END text requests (lines 48 - 214 at the time of writing). Make sure the text is copied in between the square brackets [] of the opening variable declaration.

3. Image selectors

Now we rinse and repeat for images - this time making use of the request.

  1. Copy the text from cell D2 and paste into text editor.
  2. Remove opening / closing double-quotes.
  3. Open get_images.gs script in Apps Script tab. Paste the selector text between the image selector comments (lines 2-8).

4. Image replacement requests

  1. Copy the text from cell E2 and paste into text editor.
  2. Remove opening / closing double-quotes and final comma of last request.
  3. Return to get_images.gs script, paste selector text between image request comments (lines 11-47).

At this point you’d be right to point out that the apps script code could benefit from a lot of refactoring. My JavaScript is a bit rusty, and I hope to get to that in the future, but suggestions would be very welcome.

Run the updated slide merge

Output info

Output info

With these steps done, you’re all set up. Once your values are populated (including your desired document name in cell J3), you can click on the “Merge to slides” button and the link to your new document should populate in cell J4 when the merge is completed.

Fin

That’s it, and about time. I hope this helps you vanquish the beast of Powerpoint.

You can put together multiple template documents and specify your desired template in cell J2 of the ‘API fields’ worksheet. As long as your template document contains objects using the {{fieldCode}} placeholders, the replacements should work for you.

Credit to the master

Everything above is built on from .