Streamlining SlidesYearbook with Code

Do you see the guy in the photo with his head circled? That is Eduardo Jose Ruiz Garcia, an educator from Spain and a #Mex18 Google Innovator. I was his coach. Of course, in the Google Innovator community, coaches can also be students because I am inspired by his Book Truck project.

The most recent inspiration was Eduardo's question about coding certificates for students. He even sent me examples. Sadly, my first response was "There's an app for that" but seriously, coding it ourselves streamlines the process.

Yesterday, I drove 2-hours to attend an Alice Keeler face-to-face workshop. I am a student in her online GoSlowWorkshops, but sometimes we all need human interaction to push the learning even farther.

One of the lessons was how to code a certificate. My initial thoughts focused on Eduardo and my final thoughts focused on how to use this code for SlidesYearbook.

I need to focus on finishing the #Lon19 Google Innovator yearbook to meet the August 15th deadline, but before I do, I would like to write the coding process that will streamline SlidesYearbook. I do recognize that I will need to make changes to the code so that multiple pages can be created, but this is a great start.

How to Code a Certificate

Step 1: Create the Google Form

Create a Google Form that requires the user's email and includes three questions: 1) name, 2) activity, and 3) hours it took to complete the activity.

Step 2: Make a Certificate

Make a certificate template. We made our certificates in Google Docs, but you can also use Slides. Here is a link to my example template. When you select Use template, it will make a copy to your Google Drive.

Alice directed us to change the page setup to landscape and adjust the margins as needed. She then directed us to insert a 3x3 table so that the text and images would line up. We added traditional merge tags like <<name>> .

Next, we decorated the certificate. I normally do graphic design in Google Slides, but I learned something new about borders in Docs. After removing the 3x3 borders in Table Properties, we re-added the border by clicking on the table edge and using the new menu. Since I eat crayons (see the Google Form to understand this joke), I used multiple colors and a 3 pt border thickness.

You can't see it in the video but look at the address bar of your Google Doc.  Do you see the string of numbers? That is the document ID, and we will use it in the next step.

Step 3: Copy and Paste this Script

Go back to the edit version of your Google Form. In the upper right corner, click on the three dots and open the Script. Delete all writing to start from scratch.

Copy/paste the following script. Suggestion: use CTRL+Shift+V as you paste to minimize possible font errors. The //indicates my comments. Make sure to name and save the script.

function makeCertificate() {

//the first question is always which app am I using?
var form = FormApp.getActiveForm();
var responses = form.getResponses();

//how many are there? Length gets that information
var len = responses.length

var last = len-1;
var response = responses[last];

//we need the users email address

var email = response.getRespondentEmail();

//get the questions

var items = response.getItemResponses();

//use logger to determine the number for each question.
//When I ran logger for 0, I learned that 0 = name
//I changed 0 to 1 and found that 1 = activity and so on.

var name = items[0].getResponse();
var activity = items[1].getResponse();
var hours = items[2].getResponse();

//Drive app may be turned off by school districts
//'1rH7UgQCFkjiPYxjxV24fE5tRYct13zEWpqY6D6Vt5Ac' is the doc ID for the template.
//Change the ID to match your template.

var file = DriveApp.getFileById('1rH7UgQCFkjiPYxjxV24fE5tRYct13zEWpqY6D6Vt5Ac');

//Make a copy of the template and share it with the user.
//Sharing the copy of the doc or slide is important because not all students have emails
//For the students who do have emails, we are getting the URL to email it

var copy = file.makeCopy('Certificate for '+name+ ' for '+activity);
var docID = copy.getId();
var certificate = DocumentApp.openById(docID).addViewer(email);
var docUrl = certificate.getUrl();

//Now for the magic - replacing the text

var body = certificate.getBody();
body.replaceText('<<name>>', name);
body.replaceText('<<activity>>', activity);
body.replaceText('<<hours>>', hours);

//Some users will have emails

GmailApp.sendEmail(email, 'Your certificate for '+activity, name+'\nHere is your certificate\n'+docUrl);


You will need to make changes to the code.

Line 34



to the ID of your template. I highlighted where to find the ID.

Lines 47-50

If you change the questions on the Form, you will need to make changes to the code.

body.replaceText('<<name>>', name);
body.replaceText('<<activity>>', activity);
body.replaceText('<<hours>>', hours);

Step 4: Add Triggers

The idea is to automate the process. The teacher should not have to run the script manually. Our time is more precious than that. The next step is to set a trigger so that submitting the Form = certification creation and sharing.

  1. In the script, look for Edit -> Current Project's Triggers.
  2. Click +Add trigger
  3. Change Select event type from On open to On form submit.
  4. Select Save.

Step 5: Submit the Form

You are ready to use the Form + Certificate. Try my example form with your email address. Does anyone want to eat multiple crayons with me?

On my computer and wifi, it took 18 seconds to receive the certificate in my Gmail.


After my two-hour drive one-way from Fresno yesterday, I coded again. Alice Keeler is inspirational! I highly recommend her online GoSlowWorkshops and face-to-face events. Sometimes her coding is over my head, but she will notice my confusion (or any attendee's confusion) and adjust accordingly.

What I coded last night is the beginning of the SlidesYearbook mail merge using this template. I am nowhere near done but I have Alice's Level Up Game to guide me . . . . ONCE I finish the #Lon19 Google Innovator yearbook.

Now that I have written down my thoughts, I can focus on the 44-page #Lon19 SlidesYearbook. Back to work . . .