Automate Creating Personalised Google Slides with Google Sheets and Drive Images

4 min readNov 6, 2024

Creating personalised presentations for a large number of users (like event registrations, player profiles, or team slides) can be a time-consuming task if done manually. But with Google Apps Script, you can automate this process by pulling data from a Google Sheet, creating new slides from a template, and replacing placeholders with user-specific information such as names, positions, and photos.

In this tutorial, we’ll show you how to create personalized Google Slides for each user from data stored in Google Sheets. Additionally, we will also show you how to handle Google Drive image URLs for automatic photo insertion.

What We’ll Cover

Creating a custom Google Slides presentation for each row of data in a Google Sheet.
Replacing placeholders in the slides with user information.
Handling both types of Google Drive URLs for image insertion.
Saving the new presentations into a specific Google Drive folder.

Prerequisites

Google Slides Template: You’ll need a Google Slides template with placeholders for {{Name}}, {{Position}}, and a placeholder image.
Google Sheet with User Data: Your sheet should have data such as Name, Position, and the Photo URL of each user.
Folder to Save Presentations: A folder in Google Drive where the new slides will be saved.

Steps to Set Up the Script

Create a Google Slides Template:

Design a Google Slides template where you will have placeholders for user information. For example:

{{Name}}: Placeholder for the user’s name.
{{Position}}: Placeholder for the user’s position.
An image placeholder where the user’s photo will be inserted.

2. Create a Google Sheet:

Your Google Sheet should have columns like:

Name: The user's name.
Position: The user’s position.
Photo URL: The URL to their photo (either https://drive.google.com/open?id=FILE_ID or https://drive.google.com/file/d/FILE_ID/view).

3. Write the Script:

Use the Google Apps Script editor to automate the process. Here’s the script you’ll need:

function createPlayerSlides() {
  const slideTemplateId = 'YOUR_SLIDE_TEMPLATE_ID'; // Replace with your Google Slides Template ID
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

  const folderId = 'YOUR_FOLDER_ID'; // Replace with the ID of the folder to save slides
  
  // Log the template ID for debugging
  Logger.log("Template Slide ID: " + slideTemplateId);
  
  try {
    // Open the template slide
    const templateSlide = SlidesApp.openById(slideTemplateId);
  } catch (e) {
    Logger.log("Error opening template slide: " + e.message);
    throw e; // Stop further execution if template slide can't be opened
  }

  for (let i = 1; i < data.length; i++) {
    const [name, position, photoUrl] = data[i];
    
    // Log the data for each row
    Logger.log("Processing: " + name + ", " + position + ", " + photoUrl);
    
    // Validate if photoUrl exists and is a valid Google Drive link
    if (photoUrl && photoUrl.includes('drive.google.com')) {
      try {
        // Extract the file ID from the Google Drive link
        let fileId;
        
        // Try to match both types of Google Drive URLs
        const fileIdMatch1 = photoUrl.match(/(?:id=)([^&]+)/);  // For links like https://drive.google.com/open?id=FILE_ID
        const fileIdMatch2 = photoUrl.match(/(?:file\/d\/)([^\/]+)/);  // For links like https://drive.google.com/file/d/FILE_ID/view

        if (fileIdMatch1 && fileIdMatch1[1]) {
          fileId = fileIdMatch1[1]; // Extracted file ID for the first URL format
        } else if (fileIdMatch2 && fileIdMatch2[1]) {
          fileId = fileIdMatch2[1]; // Extracted file ID for the second URL format
        }
        
        if (fileId) {
          const publicPhotoUrl = `https://drive.google.com/uc?id=${fileId}`; // Create the direct image URL
          
          // Log the public URL for debugging
          Logger.log("Public Image URL: " + publicPhotoUrl);
          
          // Duplicate the template slide (or add a new slide)
          const slide = templateSlide.appendSlide(templateSlide.getSlides()[0]);

          // Replace placeholders in the new slide
          slide.replaceAllText("{{Name}}", name);
          slide.replaceAllText("{{Position}}", position);
          
          // Replace photo placeholder with the direct image URL
          const photoElement = slide.getImages()[0]; // Assumes the first image is the placeholder
          photoElement.replace(publicPhotoUrl);
        } else {
          Logger.log(`Invalid Google Drive link for ${name}: ${photoUrl}`);
        }
      } catch (error) {
        Logger.log(`Error processing photo for ${name}: ${error.message}`);
      }
    } else {
      Logger.log(`Invalid or missing photo URL for ${name}`);
    }
  }
  
  // Save the modified template
  templateSlide.saveAndClose();
  Logger.log('Slide creation process completed.');
}

How the Script Works:

Open Template: The script opens the Google Slides template using the slideTemplateId.
Extract Data from Google Sheets: It reads the user data (name, position, and photo URL) from the active sheet.
Process Google Drive Photo URL: It validates the photo URL, checks if it’s a valid Google Drive link, and extracts the fileId to generate a direct image URL.
Create Personalized Slide: It creates a new slide for each user, replacing the placeholders ({{Name}}, {{Position}}) with the user’s data and updating the image.
Save the Presentation: Finally, it saves the newly created slide in the specified folder.

Key Points:

Handling Two Google Drive URL Formats: The script handles both types of Google Drive links:

Public Images: Ensure that the images in Google Drive are publicly accessible. The script uses the https://drive.google.com/uc?id=FILE_ID format to get a direct link to the image.
Folder for Saving: Make sure the folder ID where the slides will be saved is correct.

Running the Script:

Go to Extensions > Apps Script in your Google Sheets.
Copy and paste the script into the editor.
Replace 'YOUR_SLIDE_TEMPLATE_ID' and 'YOUR_FOLDER_ID' with your actual template and folder IDs.
'YOUR_SLIDE_TEMPLATE_ID' will be as shown below.

'YOUR_FOLDER_ID' will be as shown below.

Run the script.

Conclusion:

By automating the creation of personalised Google Slides from a Google Sheet, you save time and effort in creating presentations for multiple users. This script also demonstrates how to efficiently handle Google Drive image URLs, making it a powerful tool for automating various tasks, such as creating registration slides, profile pages, or event materials.