Automate Creating Personalised Google Slides with Google Sheets and Drive Images

Tejaksha K
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

  1. 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:

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:

  1. Open Template: The script opens the Google Slides template using the slideTemplateId.
  2. Extract Data from Google Sheets: It reads the user data (name, position, and photo URL) from the active sheet.
  3. 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.
  4. 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.
  5. 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:
  1. https://drive.google.com/open?id=FILE_ID
  2. https://drive.google.com/file/d/FILE_ID/view
  • 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.

--

--

Tejaksha K
Tejaksha K

Written by Tejaksha K

I'm a Full Stack Developer & Cloud Expert with experience in Google Cloud Platform & AWS.