Search code examples
google-apps-scriptgoogle-slides

exporting google slide as image then sending it via email automatically


In relation to trying to change a textbox's input in google slides through a list in spreadsheet

I was able to successfully change the textbox's input from the spreadsheet. Now, what I want to do is the following:

  1. I have this spreadsheet which includes the name (which the slides will be getting the update) and email address.

enter image description here

  1. After running the script of changing the Google Slide's textbox to the names listed above, I want the individual slides to be sent to the respective email addresses as an inline image attachment.

I'm not sure how to do this so I would really need help in doing it.

Here's what I have so far:

function changeSlideTextAndDuplicate() {
  // Get the Google Sheet object.
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  // Get the range of cells containing the names.
  var range = sheet.getRange("A2:A" + sheet.getLastRow());

  // Get the Google Slide object.
  var presentation = SlidesApp.openById("1NI05tMvTqOT2jVCbozD_hcH5UoU_hjRHNKRkCSTxwko");

  // Get the template slide.
  var templateSlide = presentation.getSlides()[0];

  // Iterate over the range and change the input of the textbox on the slide.
  range.getDisplayValues().forEach(([name]) => {
    if (!name) return;

    // Get the second textbox on the slide.
    var textbox = presentation.appendSlide(templateSlide).getShapes()[4];

    // Replace the placeholder text in the textbox with the recipient's name.
    textbox.getText().setText(name);
  });
}

Solution

  • I believe your goal is as follows.

    • You want to retrieve the values from columns "A" and "B" of the active sheet of a Google Spreadsheet. The columns "A" and "B" are the text and the email.
    • From presentation.appendSlide(templateSlide).getShapes()[4];, you want to replace 5th shape with the text from column "A".
    • And, you want to send an email including the inline image converted from the appended slide.

    In this case, how about the following modification?

    Modified script:

    In this case, Slides API is used for retrieving the appended slide as a PNG image. So, please enable Slides API at Advanced Google services.

    function changeSlideTextAndDuplicate() {
      var presentationId = "1NI05tMvTqOT2jVCbozD_hcH5UoU_hjRHNKRkCSTxwko"; // Please set your presentation ID of Google Slide.
    
      var sheet = SpreadsheetApp.getActiveSheet(); // In this case, the active sheet is used. If you want to use the specific sheet, please modify this to SpreadsheetApp.getActiveSpreadsheet().getSheetByName("###Sheet name###")
      var range = sheet.getRange("A2:B" + sheet.getLastRow());
      var presentation = SlidesApp.openById(presentationId);
      var templateSlide = presentation.getSlides()[0];
      range.getDisplayValues().forEach(([name, email]) => {
        if (!name || !email) return;
        var newSlide = presentation.appendSlide(templateSlide);
        newSlide.getShapes()[4].getText().setText(name);
        presentation.saveAndClose();
        var { contentUrl } = Slides.Presentations.Pages.getThumbnail(presentationId, newSlide.getObjectId(), { "thumbnailProperties.thumbnailSize": "LARGE" });
        MailApp.sendEmail({
          to: email,
          subject: "sample email", // Please set your email title.
          htmlBody: '<img src="cid:image1">',
          inlineImages: { image1: UrlFetchApp.fetch(contentUrl).getBlob() }
        });
        presentation = SlidesApp.openById(presentationId);
      });
    }
    
    • When this script is run, the values are retrieved from columns "A" and "B" of the active sheet, and an email is sent using the updated slide and the retrieved email address.

    • If you want to change the image size, please modify "thumbnailProperties.thumbnailSize": "LARGE". Ref

    References: