How to Use a Google Script to Create a Slide Deck When a Form Is Submitted

A pod of sperm whales off the coast of Mauritius. Image by Gabriel Barathieu via Wikimedia Commons.

Every July, I celebrate my remission anniversary. I was diagnosed with cancer when I was 31 and that July, I underwent surgery to remove the tumor. This year I celebrate seven years cancer free.

To celebrate, we are hosting a little gathering and because this is the kind of person I am, everyone is invited to present a lightning talk. (Mine is going to be about sperm whales which are my favorite whales.) I wanted to create a Google Apps Script that upon submission of a Google Form does the following:

1. Transforms the form response data
2. Copies a Google Slides template file
3. Populates the new deck title card with the associated values from the form response
4. Emails the slide deck to the form respondent

Here’s a sequence diagram of the whole workflow— in case that’s how your brain rolls.

A diagram of the sequence starting with the User filling out the Form and ending with the User receiving an email.

With this script, my party guests can generate a slide deck for their lightning talk by filling out a form. Because why not?

You probably have no need for this script because why would anyone replicate this use case? But maybe it can help you in whatever problem you’re trying to solve.

So anyway, here’s that code you didn’t ask for.

Prerequisites

• Well … a Google account
• Enough familiarity with JavaScript and Google Apps Scripts that you can muddle your way through some basic functions and variables in a standalone script

Setup

1. Create a Google Slides template

In my case, I wanted to create a slide deck with a title card populated with data provided by the user (the user being one of my party guests). This meant I needed to create a template deck with a title card like the following.

The template for the title card of the Slides deck.

I know this looks like Jinga, but Google Apps Script doesn’t have any sort of templating language. The double brackets are just a method of visually representing the values of the template: {{title}}, {{tag_line}}, and {{name}}. You can represent them any way you like because ultimately, the code performs a Find and Replace on the text. We’ll get to the script in a moment.

(This might be obvious to you, but it wasn’t immediately obvious to me when I was reading other tutorials, so that’s why I wanted to explain it.)

2. Create a Google Form

I created a Google Form to collect the data I need to fill in the values in the Slides template. I turned on the option to collect email addresses because I need an email address to add the user as an Editor on the slide deck.

Update the Responses setting to collect email addresses.

I also needed to link the form responses to a Google Sheets. This automatically created a new Sheets in the same folder as the folder I created the form.

Link the Form responses to a Google Sheets.

3. Create a Google Apps Script

I decided I wanted a standalone script which means that it is not bound to any other Google Apps such as Docs or Sheets. You can learn more about the differences between standalone and bound scripts in the References and Resources section below.

The option to create a Google App Scripts file is nested under More in the + Newmenu.

Create a Google Apps Script file.

Copy and paste the code below into this file.

The Code

Here’s the code. It’s also right below. Copy and paste the code into the Google Apps Script file you created.

/**
When a specified form is submitted,
triggers a series of functions that result in a slide deck created
with values from the submitted form response.
*/

// ID of slide deck template
const SLIDES_TEMPLATE_FILE_ID = '<REPLACE-THIS-WITH-THE-ID-OF-YOUR-SLIDE-DECK-TEMPLATE-FILE>'
// ID of folder to save to
const LIGHTNING_TALKS_SLIDES_FOLDER_ID = '<REPLACE-THIS-WITH-THE-ID-OF-YOUR-FOLDER>'
// URL of spreadsheet associated with form responses
const LIGHTNING_TALKS_SHEET = SpreadsheetApp.openByUrl('<REPLACE-THIS-WITH-THE-URL-OF-YOUR-FORM-RESPONSE-SPREADSHEET>');

function formatFormResponse(formResponse) {
  // Create key-value pairs from form submission event values
  console.log("Generating form response key-value pairs from form response with key 'name': " + formResponse[2]);
  
  var formResponseObject = {};
  formResponseObject['timestamp'] = formResponse[0];
  formResponseObject['name'] = formResponse[1];
  formResponseObject['topic'] = formResponse[2];
  formResponseObject['title'] = formResponse[3];
  formResponseObject['tag_line'] = formResponse[4];
  formResponseObject['email'] = formResponse[5];
  
  console.log("Form response key-value pairs created")  
  
  return formResponseObject
}

function createNewSlideDeck(formResponseObject) {
  // Create a new slide deck by copying the template slide deck
  console.log("Creating a new slide deck by copying the template slide deck with form response with key 'name': " + formResponseObject['name'])
  
  // Get the template file
  var file = DriveApp.getFileById(SLIDES_TEMPLATE_FILE_ID);
  // Get the folder we want to save our new slide deck in and create a copy of the template there
  var folder = DriveApp.getFolderById(LIGHTNING_TALKS_SLIDES_FOLDER_ID);
  var filename = formResponseObject['name'] + '-lightning-talk'
  var newSlideDeck = file.makeCopy(filename, folder);
  
  console.log("New slide deck created with filename: " + filename)
  
  return newSlideDeck
}

function populateNewSlideDeck(formResponseObject) {
  // Populate the new slide deck with the form responses
  console.log("Populating the new slide deck using the form response with key 'name': " + formResponseObject['name']) 
  
  // Create the copy
  var newSlideDeck = createNewSlideDeck(formResponseObject) 
  // Open the new slide deck
  var openedSlideDeck = SlidesApp.openById(newSlideDeck.getId())
  // Get the first slide in the deck
  var titleSlide = openedSlideDeck.getSlides()[0];
  
  // Replace all instances of the template variables with the corresponding form response
  titleSlide.replaceAllText('{{title}}', formResponseObject['title']); 
  titleSlide.replaceAllText('{{tag_line}}', formResponseObject['tag_line']);  
  titleSlide.replaceAllText('{{name}}', formResponseObject['name']);  
  
  // Save and close the slide deck to persist our changes
  openedSlideDeck.saveAndClose();
  console.log("Slide deck saved and closed")
  // Add the form respondent's email as editor for the slide deck
  DriveApp.getFileById(newSlideDeck.getId()).addEditor(formResponseObject['email']);
  
  console.log("Editor added to slide deck")
  
  return newSlideDeck
}

function sendEmailWithSlideDeck(formResponseObject, newSlideDeck) {
  // Send email to slide deck respondent so they know their slide deck has been created
  console.log("Sending email to: " + formResponseObject['name'])
  
  // URL for the slide deck
  var slideDeckUrl = newSlideDeck.getUrl();
  // Link to the informational document about lightning talks
  var lightningTalkDoc = '<REPLACE-THIS-WITH-THE-URL-TO-GOOGLE-DOC>'
  
  // Send an email to the form respondent with the following values
  var emailAddress = formResponseObject['email'];
  var emailSubject = "Your lightning talk slide deck";
  var emailMessageBody = `Your lightning talk slide deck has been created here: ${slideDeckUrl}. \n\nFor more information on lightning talks, click here: ${lightningTalkDoc} \n\nPlease reach out if you have any problems or questions!`;
  GmailApp.sendEmail(emailAddress, emailSubject, emailMessageBody);
  
  console.log("Email sent")
}

function formWorkflow(e) {
  // Final workflow for the process:
  console.log("Starting workflow for form response with name: " + e.values[1]);
  
  // 1. Create key-value pairs from form response event
  var formResponseObject = formatFormResponse(e.values);
  // 2. Create a new slide deck and populate it with the key-value pairs
  newSlideDeck = populateNewSlideDeck(formResponseObject);
  // 3. Send the email
  sendEmailWithSlideDeck(formResponseObject, newSlideDeck);
  
  console.log("Workflow completed");
}

function main() {
  // Build the trigger that executes formWorkflow when a form is submitted to the linked spreadsheet
  console.log("Building trigger ...")
  
  ScriptApp.newTrigger('formWorkflow')
    .forSpreadsheet(LIGHTNING_TALKS_SHEET)
    .onFormSubmit()
    .create();
}

The following placeholders in the code need to be replaced with your own values. Most of these placeholders require an ID number. The ID number can be found in the URL of each Google Apps file.

• <REPLACE-THIS-WITH-THE-ID-OF-YOUR-SLIDE-DECK-TEMPLATE-FILE> is the ID of the Slides template you created.
• Similarly, <REPLACE-THIS-WITH-THE-ID-OF-YOUR-FOLDER> is the ID of the folder to which you want to copy the Slides template.
• <REPLACE-THIS-WITH-THE-URL-OF-YOUR-FORM-RESPONSE-SLIDESHEET> is the URL of the spreadsheet linked to the Form responses.
• <REPLACE-THIS-WITH-THE-URL-TO-GOOGLE-DOC> this is the URL of a Doc I wrote to explain what a lightning talk is. This is not critical for the script to work, so you can do whatever you want with it — just remember to update any references to its assigned variable in the code.

Hopefully, the comments I’ve provided in the code offer a clear explanation of what is going on, but in case it’s helpful, I’ll also provide a high-level summary:

I’ve broken down each step of the workflow into its own function to minimize responsibilities. One function creates a dictionary of key-value pairs from the Form response, another function copies the Slides template, and so on. formWorkflow() calls these functions to tie them all together. main() builds the trigger that executes the workflow when the Form is submitted.

I’ve included logging that I hope will make it easier to debug the code should errors arise.

Authorizing Access

The script needs access to the various Google Apps specified within the code to behave as expected. In order to do this, select the main() function from the menu bar in the Google Apps Script file and then click Run. You will be prompted to authorize access. The prompt is a pop-up, so if you don’t see the prompt, check to see if it was blocked.

Running the script like this will trigger the authorization prompt.

It is a good idea to make the Execution log visible when running the script for the first time. You can do this from the menu at the top of the Google Apps Script window.

Visible execution logs will make the code easier to debug.

Testing It Out

Try the workflow for yourself by filling out the Form and submitting it. If everything works, then you should end up with a new Slides file with a title card populated with the responses you provided in the Form.

You can find a log of script executions by selecting Executions from the menu on the left-hand side of the Google Apps Script window. This is helpful for debugging.

This is where you can find logs of every script execution.

Fingers crossed it works perfectly!

Resources and References

Google’s documentation is pretty good — even for someone who has ADHD like I do. I’ve tried to pull out specific documentation below for your convenience.

• Standalone scripts: Remember how I mentioned this code being a standalone script? You can learn more about what exactly that means here.
• Installable triggers: Because this is a standalone script, you need to “build” a trigger. That’s what the main() function does. Read more about installable triggers here, and about form triggers specifically here. Something to note is that in the case of this script, the form submission trigger originates in the spreadsheet associated with the form responses.
• Event objects: When the Form is submitted, an event object is created. The event object contains data about the event, including values which is where the Form responses provided by the user can be found. The values attribute of the event object is what is passed to the formatFormResponse() function. Learn more about the form submit event here.
• Authorization: Your script will need access to the various Google Apps it interacts with. You can learn more about authorization here.

Other resources for this post include this tutorial which helped me write my script.

In Closing

Probably no one will ever need any part of this code, but just in case, it’s here for you and I hope you found this helpful. I plan on doing more of these, so if you have any questions or comments, feel free to share.

The title card for my lightning talk.