Google Forms File upload - How to use image submitted by form and embed it in an email

Question

I'm a bit stuck on a project and could do with some help.

AIM: I'm trying to submit a google form, with various bits of data and an image. That would then trigger a HTML email using the form data and embed the image.

So far I can do everything except embed the image.

Here's what I have so far.

code.gs

      function getData() {
   var sh = SpreadsheetApp.getActive()
       .getSheetByName('Form responses 1');
   return sh.getRange(sh.getLastRow(), 1, 1, sh.getLastColumn())
       .getValues()[0]

}





function testEmail() {
   var htmlBody = HtmlService
       .createTemplateFromFile('mail_template')
       .evaluate()
       .getContent();
var mailADdy = "myemail@email.co.uk";
   MailApp.sendEmail({
       to:mailADdy,
       subject: 'Test Email markup - ' + new Date(),
       htmlBody: htmlBody,
   });

  }

mail_template.html

    <!DOCTYPE html>
<html>
  <head>
    <style>
      @media only screen and (max-device-width: 480px) {
        /* mobile-specific CSS styles go here */
      }
    </style>
  </head>
  <body>
   <? var data = getData(); ?>
   <? var first = data[2]; ?>
   <? var second = data[3]; ?>
       <? var third = data[4]; ?>
       <? var fourth = data[5]; ?>

  /* imageUpload var is the link to the image that's been uploaded, in "https://drive.google.com/open?id=ID" format"*/
       <? var imageUpload = data[6]; ?>

   /* Below are some things that I've tried and failed */
   <? var imageID = imageUpload.replace("https://drive.google.com/open?id=","");?>
  <? var imageBlob = UrlFetchApp.fetch(imageUpload).getBlob().setName("imageBlob");?>
   <? var imageBlob = DriveApp.getFileById(imageID).getBlob();?>





    <div class="main">
      <p style="text-align: center;"><strong>This is a test HTML email.</strong></p>
      <table style="margin-left: auto; margin-right: auto;">
        <tbody>
          <tr>
            <td style="text-align: left;">First question to be put here</td>
            <td style="text-align: right;"><strong><?= first ?></strong> </td>
          </tr>
          <tr>
            <td style="text-align: left;">Second question here</td>
            <td style="text-align: right;"><?= second ?></td>
          </tr>
          <tr>
            <td style="text-align: left;">Third question here</td>
            <td style="text-align: right;"><?= third ?></td>
          </tr>
          <tr>
            <td style="text-align: left;">Image Upload test</td>
            <td style="text-align: right;"><?= imageUpload ?></td>
          </tr>
        </tbody>
      </table>
      <p></p>
      <p style="text-align: center;"><img src="https://imageurl.com" alt="Logo" /></p>
    </div>
  </body>
</html>

The getData function returns the last row of data in my sheet (Which will be the recent form submission) testEmail sends the email.

mail_template contains the HTML for the email. Layout etc.

By using scriplets I can get the form values into the email and get it to send.

However whatever I've tried with the image, I can't get it right.

When I try to use the following scriptlet in the HTML file,

 <? var imageBlob = UrlFetchApp.fetch(imageUpload).getBlob().setName("imageBlob");?>

I get the following error.

Execution failed: You do not have permission to call fetch

I've also tried:

<? var imageBlob = DriveApp.getFileById(imageID).getBlob();?>

But still not working.

I did try following the info from this link, How to include inline images in email using MailApp

But cannot translate this to my example as they are not using a separate HTML file

Any help/pointers are appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 12:26

Firstly, the error that you get is because you never give GAS permission to access UrlFetch or DriveApp. This seems counterintuitive but if you look at your GAS script only you will notice you never call these functions in the GAS side. So, when you run the GAS script the compiler never sees a call to UrlFetch or DriveApp. Hence, doesn't ask your permission to run it. However, during runtime, the evaluate method tries to access these methods and the authorization fails.

This can be fixed by having a dummy function on the GAS side to call this function. Like so

function dummyFunction(){
 DriveApp.getRootFolder()
 UrlFetchApp.fetch("www.google.com")
}

You never need to run these functions, but during compilation, it will ask for authorization for these methods. Thus, when you run your evaluate method, there will be no issue with authorization.

Secondly, to introduce an inline image as described here you don't have to get the imageBlob on the HTML side. You will need to get it one GAS script. You just have to include an img tag on the html page like so.

<tr>
    <td style="text-align: left;">Image</td>
    <td style="text-align: right;"><img src ="cid:imageKey"/></td>
</tr>

on the GAS you will modify the code like so:

function testEmail() {
   var htmlBody = HtmlService
       .createTemplateFromFile('mail_template')
       .evaluate()
       .getContent();
        var mailADdy = "myemail@email.co.uk";
        var data = getData()
        var imageUpload = data[6]; 
        var imageID = imageUpload.replace("https://drive.google.com/open?id=","")
        // Use one of the below method to get the imageBloB
        var imageBlob = UrlFetchApp.fetch(imageUpload).getBlob().setName("imageBlob")
       //var imageBlob = DriveApp.getFileById(imageID).getBlob()

   MailApp.sendEmail({
       to:mailADdy,
       subject: 'Test Email markup - ' + new Date(),
       htmlBody: htmlBody,
       inlineImages:
       {
         imageKey: imageBlob,
       }
   });

  }

If you use this technique you don't have to call urlFetch or DriveApp on the html page and you can remove those scriptlet lines.

Thank you @Jack Works a treat. Really helpful :) – Munkey May 14 '17 at 19:57 — Munkey, May 14 '17 at 19:57

Google Forms File upload - How to use image submitted by form and embed it in an email

1 Answers1