Optimize Your Workflow: Creating Slides and Certificates with Mail Merge and Google Sheets (Part 1)

I used mail merge to to prepare PPT slides and print certificates for the Reflections Art Program. This article will demonstrate how to generate certificates for each student whose information is recorded in a Google Sheet. In the next article, I will delve into creating a PowerPoint presentation that highlights each student’s artwork based on the information in the same Google Sheet.

For those who are unfamiliar with Art Reflections, the task at hand involves a Google Sheet with hundreds of rows. Each row details information about an art project, including the student’s name, the title of the work, the art category, and the student’s grade level, among other things. The objective is to print certificates for each participating student that features their full name and art category.

My objective is to print certificates instead of handwriting them. While handwriting might be faster if you only have a few certificates to prepare, I anticipate being the chair for several years, and there are over 100 certificates to work on each time. Therefore, learning how to use mail merge is a valuable investment of my time.

To demonstrate the process, we will be using a mock Art Reflections spreadsheet. You can follow along in your Art Reflections Google Drive account. Start by filling the first row of a blank Google Sheet with the following columns: “firstName,” “lastName,” “artCategory,” and “certificates.” Copy and paste the corresponding information from your actual Art Reflections student entry form response into the new sheet, but leave the “certificates” column blank.

Let’s move on to creating a template for the certificates. The names at the bottom are for the school principal/the Reflections chair, and the PTA president, and they are identical for all certificates. Therefore, they do not require curly brackets.

Below are two links for the template I have created. The first link allows you to view the template slide, while the second link provides you with a copy of the template to use for your own certificates.

https://docs.google.com/presentation/d/1Y6X57unOYqkb46LkIs2X9pdKlnSPHD5Cz6qLzd3L9-g/edit?usp=sharing

https://docs.google.com/presentation/d/1Y6X57unOYqkb46LkIs2X9pdKlnSPHD5Cz6qLzd3L9-g/copy#slide=id.p

If you plan on creating your own certificate template, remember to adjust the page setup to match the dimensions of the certificate. To do so, click “File” and select “Page Setup.” As the certificate is the size of an A4 paper, we need the template to be 11.7 x 8.3 inches.

Next, let’s add the custom menu to the Google Sheet you just created. Click “Extensions” and select “Apps Script” to open the script window.

Copy and paste the following code, which was inspired by this article. Alternatively, you can find the code on my GitHub repository.

function mailMergeCertificatesFromSheets() {
  // Load data from the spreadsheet
  var dataSheet = SpreadsheetApp.getActiveSheet();
  var dataRange = dataSheet.getDataRange();
  var sheetContents = dataRange.getValues();

  // Save the header in a variable called header
  var header = sheetContents.shift();

  // Create an array to save the data to be written back to the sheet.
  // We'll use this array to save links to Google Slides.
  var updatedContents = [];

  // Create a new Google Slides presentation
  var presentation = createCopyOfSlidesTemplate();
  var slides = presentation.getSlides();
  var slide = slides[0];

  // Loop through each row in the sheet
  // for (var i = 0; i < sheetContents.length; i++) {
  // the output slides should be in the right order, not reverse
  for (var i = sheetContents.length-1; i >=0 ; i--) {
    var row = sheetContents[i];
    var firstName = row[0];
    var lastName = row[1];
    var artCategory = row[2];
    var slidesUrl = row[3];
    
    // Create a new slide in the presentation and populate it with data from the row
    var newSlide = slide.duplicate();
    newSlide.replaceAllText("{{firstName}}", firstName);
    newSlide.replaceAllText("{{lastName}}", lastName);
    newSlide.replaceAllText("{{artCategory}}", artCategory);

    // Generate a new Google Slides link for this specific slide
    // all slides are in the same presentation
    slidesUrl = `https://docs.google.com/presentation/d/${presentation.getId()}/edit#slide=id.${newSlide.getObjectId()}`;

    // Update the corresponding row in the sheet with the new Google Slides link
    row[3] = slidesUrl;  

    // Add the updated row to the array that will be written back to the sheet
    updatedContents.unshift(row);
  }

  // remove the template page in the presentation
  slide.remove();

  // Add the header to the array that will be written back to the sheet.
  updatedContents.unshift(header);

  // Write the updated data back to the Google Sheets spreadsheet.
  dataRange.setValues(updatedContents);
}


function createCopyOfSlidesTemplate() {
  // Change the TEMPLATE_ID to your template slide ID
  var TEMPLATE_ID = "1Y6X57unOYqkb46LkIs2X9pdKlnSPHD5Cz6qLzd3L9-g";

  // Create a copy of the file using DriveApp
  var copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy();

  // Load the copy using the SlidesApp.
  var slides = SlidesApp.openById(copy.getId());

  return slides;
}

function onOpen() {
 // Create a custom menu to make it easy to run the Mail Merge
 // script from the sheet.
 SpreadsheetApp.getUi().createMenu("Create_certificates")
   .addItem("Create certificates", "mailMergeCertificatesFromSheets")
   .addToUi();
}

Before running the script, you need to modify one variable — the slide ID. Replace the TEMPLATE_ID “1Y6X57unOYqkb46LkIs2X9pdKlnSPHD5Cz6qLzd3L9-g” with the ID of your own template slide. Every Google Slide presentation has a unique ID that can be obtained from its URL in the browser’s address bar. The slide ID is located between “…/d/” and “/edit#slide=id.p” in the URL, as shown in the following image.

Copy and paste your own template’s ID. Save the changes and click “Run” to execute the script.

When you execute the script for the first time, Google may display a warning message indicating that it’s unsafe to run. In this case, click “Advanced” and select “Go to … (unsafe)” to trust the developer and proceed.

If the custom menu doesn’t appear in the sheet after saving the script, you can try refreshing the page or closing and reopening the webpage/sheet. Once the custom menu is visible, you can run the script by clicking “Create_certificates” in the menu, which should appear after the “Help” option. Alternatively, you can also use the “Run” button as described earlier.

After the script finishes running, you will see the links for each certificate in the “certificates” column. These links open to the specific slide for each certificate, which are all contained within the same presentation.

Initially, the thumb image for each certificate may appear as if the contents in curly brackets have not been replaced. However, there is no need to panic, as opening the file will reveal that the student names have been correctly replaced.

To test the layout, print one certificate and adjust any necessary elements in the template. When printing, it’s best to set the “scale” option to default. If changes are made to the template, run the script again to generate new certificates. The links in the “certificates” column will be updated automatically. Once you are satisfied with the output, you can print all the certificates.

Remember to finish everything on Google Drive, as formatting in the presentation may change if downloaded.

To recap, the steps to create and print certificates are as follows:

1. Create a Google Sheet
2. Create a certificate template
3. Create the script (and remember to modify the TEMPLATE_ID)
4. Run the script
5. Fine-tune the formatting as needed
6. Print all the certificates!