google-sheetsgoogle-apps-scriptgoogle-slides

I have a google app script, where it converts a google slide template to a pdf, am facing pdf conversion issue


I'm encountering an issue with a Google Apps Script that converts a Google Slide template to a PDF. The Slide template contains two placeholders that need to be replaced:

  1. A placeholder for a name, which should be pulled from a Google Sheet mentioned in column A. Sheet link

  2. An image placeholder that should be replaced with an image from a Google Drive folder. The image should have the same name as the one in column A of the Google Sheet. Drive Image Folder

Google drive folder where generated pdf will be saved: folder

Currently, the script successfully creates a temporary slide with all the correct data. However, when it's converted to a PDF, the data is missing. Even after adding a delay before creating the PDF, the issue persists. Any ideas on how to resolve this?

Here is the script which am using:

function createPDFsAndGenerateLinks() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const rows = sheet.getDataRange().getValues();
  const folderIdForPDFs = '1up5Z6bwO4K_PuiNjgN0DRh_pNnU_Q6tQ';
  const templatePresentationId = '1sZ2HWnf1feqCzOOy17txAvRXyhhs0xCecxHPxvvR6gI';
  const imagesFolderId = '1zhWR4ADwvgT63uv23z7SSySYZTaZCkeq'; // Correctly included here

  Logger.log("Starting to process rows");

  rows.forEach((row, index) => {
    if (index === 0 || !row[0]) {
      Logger.log("Skipping row " + (index + 1) + " (header or empty)");
      return; // Skip header or empty rows
    }

    const name = row[0].trim(); // Trim to remove any accidental whitespace
    Logger.log("Processing " + name);
    
    const pdfFile = createTicketPDF(name, templatePresentationId, folderIdForPDFs, imagesFolderId);

    if (pdfFile) {
      Logger.log("Created PDF for " + name);
      sheet.getRange(index + 1, 6).setValue(pdfFile.getUrl()); // Set URL in column F
    } else {
      Logger.log("Failed to create PDF for " + name);
    }
  });

  Logger.log("Finished processing rows");
}

function createTicketPDF(name, templatePresentationId, folderIdForPDFs, imagesFolderId) {
  const copiedPresentation = DriveApp.getFileById(templatePresentationId).makeCopy();
  const presentationId = copiedPresentation.getId();
  Logger.log("Copied template for " + name);

  const slidesAppPresentation = SlidesApp.openById(presentationId);
  const slide = slidesAppPresentation.getSlides()[0];
  slide.replaceAllText('{{Name}}', name);
  Logger.log("Replaced placeholder with " + name);

  const imageFile = findImageByName(name + '.png', imagesFolderId);
  if (imageFile) {
    Logger.log("Found image for " + name);
    const imageBlob = imageFile.getBlob();
    insertImageAtPosition(slide, imageBlob, 5.05, 23.43, 19, 19);
  } else {
    Logger.log("No image found for " + name);
  }

  // Introduce a delay before exporting to PDF to ensure changes are applied
  Utilities.sleep(3000);

  const pdfBlob = DriveApp.getFileById(presentationId).getAs('application/pdf');
  const pdfName = `${name}_Ticket.pdf`;
  const pdfFile = DriveApp.getFolderById(folderIdForPDFs).createFile(pdfBlob).setName(pdfName);

  DriveApp.getFileById(presentationId).setTrashed(true);
  Logger.log("Created PDF for " + name);

  return pdfFile;
}

function findImageByName(name, imagesFolderId) {
  const folder = DriveApp.getFolderById(imagesFolderId);
  Logger.log("Searching for image in folder: " + folder.getName());
  const files = folder.getFilesByName(name);
  if (files.hasNext()) {
    const file = files.next();
    Logger.log("Found file: " + file.getName());
    return file;
  }
  Logger.log("No file found for " + name);
  return null;
}

function insertImageAtPosition(slide, imageBlob, xCm, yCm, widthCm, heightCm) {
  const xPoints = xCm * 28.3465;
  const yPoints = yCm * 28.3465;
  const widthPoints = widthCm * 28.3465;
  const heightPoints = heightCm * 28.3465;
  slide.insertImage(imageBlob, xPoints, yPoints, widthPoints, heightPoints);
}

i tried by adding delay in creating pdf, but didnt worked


Solution

  • In your showing script, how about the following modification?

    From:

    Utilities.sleep(3000);
    

    To:

    slidesAppPresentation.saveAndClose();