Adding image from spreadsheet into an e-mail with Apps Script

Question

I'm creating an automatic e-mail which includes many data that change every week. I'm new in Apps Script and I would like to add an image at the end of the e-mail.

Here the code :

// Drive where is stored the image
const folder = DriveApp.getFolderById("1XXXXXXXXX");

// Retrieve ID file where is stored the image
const file = folder.getFilesByName("file")
const fileIDs = [];

while (file.hasNext()) {
    var files = file.next();
    fileIDs.push(files.getId());
}

var ssFile = SpreadsheetApp.openById(fileIDs[0]);
SpreadsheetApp.setActiveSpreadsheet(ssFile);

//Spreadsheet
var mail = ssFile.getSheetByName("Mail");

//Retrieve image from the spreadsheet
var retrieveImage = mail.getImages()[0];
var arrayImage = new Array();
var image = {};

arrayImage[0] = retrieveImage.getAs('image/png')
image["image"+0] = arrayImage[0];

//Fonction to send mail
function sendEmailS(){
     var message = "Test";
     message += "<img src='cid:image" +0+ "'> <br>";

     GmailApp.sendEmail("[email protected]", "subject", "", 
                         {
                          htmlBody: message,
                          inlineImages: image
                         }
     );
}

I've got the error that getAs is not a function. Could help me or give me any clue to finish my script ?

Tanaike · Accepted Answer · 2022-07-20 12:45:25Z

Issue and workaround:

From your showing script and the error of I've got the error that getAs is not a function., I thought that the reason for your issue is due to that the image cannot be retrieved as a blob from Spreadsheet.

In the current stage, unfortunately, there is no method for directly retrieving the image on Spreadsheet as a blob. So, in this answer, I would like to propose a workaround. In this workaround, a Google Apps Script library is used. This library supports for the processes that the current Google services cannot directly achieve.

Usage:

1. Install Google Apps Script library.

Please install DocsServiceApp of Google Apps Script library. You can see how to install it at here.

2. Modified script.

When your script is modified using this library, it becomes as follows.

function sendEmailS() {
  // Drive where is stored the image
  const folder = DriveApp.getFolderById("1XXXXXXXXX");

  // Retrieve ID file where is stored the image
  const file = folder.getFilesByName("file")
  const fileIDs = [];

  while (file.hasNext()) {
    var files = file.next();
    fileIDs.push(files.getId());
  }

  var ssFile = SpreadsheetApp.openById(fileIDs[0]);
  SpreadsheetApp.setActiveSpreadsheet(ssFile);

  //Spreadsheet
  var mail = ssFile.getSheetByName("Mail");

  //Retrieve image from the spreadsheet
  var retrieveImage = mail.getImages()[0];

  var arrayImage = new Array();
  var image = {};

  const anchor = retrieveImage.getAnchorCell().getA1Notation();
  const res = DocsServiceApp.openBySpreadsheetId(fileIDs[0]).getSheetByName("Mail").getImages();
  const obj = res.find(({ range: { a1Notation } }) => a1Notation == anchor);
  if (!obj) return;
  arrayImage[0] = obj.image.blob;
  image["image" + 0] = arrayImage[0];

  //Fonction to send mail
  var message = "Test";
  message += "<img src='cid:image" + 0 + "'> <br>";

  GmailApp.sendEmail("[email protected]", "subject", "",
    {
      htmlBody: message,
      inlineImages: image
    }
  );
}

3. Testing.

When this script is run, an image of mail.getImages()[0] is retrieved as a blob. And, an email is sent using the retrieved image blob.

Reference:

DocsServiceApp of Google Apps Script library

J M · Accepted Answer · 2022-07-20 12:33:38Z

1

Maybe some ideas here for you?.... ...this gets image file from G.drive and emails it...

function emailImage(){

fileList = DriveApp.getFilesByName('imageNameInDrive.jpg');
  while (fileList.hasNext()) { image = fileList.next().getId(); }
  var insertImage = DriveApp.getFileById(image).getBlob();  
    
  var message = 'Test<br>';
  message += '<img src="cid:insertImage" > <br>';
    
  GmailApp.sendEmail("[email protected]", "subject", "",
    {
      htmlBody: message,
      inlineImages: {
        insertImage: insertImage
      }
    }
    );
}

answered Jul 20, 2022 at 12:33

J M

963 bronze badges

Comments

Emel · Accepted Answer · 2022-07-20 14:43:45Z

1

In addition to Tanaike's answer, which in my opinion would be a good workaround, there is an open Feature Request for converting Spreadsheet images to BlobSource.

Remember to hit the +1 button to tell Google that you are also interested.

Update OverGridImage to support BlobSource interface

answered Jul 20, 2022 at 14:43

Emel

2,5041 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

Adding image from spreadsheet into an e-mail with Apps Script

3 Answers 3

Issue and workaround:

Usage:

1. Install Google Apps Script library.

2. Modified script.

3. Testing.

Reference:

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Issue and workaround:

Usage:

1. Install Google Apps Script library.

2. Modified script.

3. Testing.

Reference:

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related