Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

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

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@email.com", "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 ?

about 4 years ago · Santiago Trujillo
3 answers
Answer question

0

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@email.com", "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
about 4 years ago · Santiago Trujillo Report

0

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@gmail.com", "subject", "",
    {
      htmlBody: message,
      inlineImages: {
        insertImage: insertImage
      }
    }
    );
}
about 4 years ago · Santiago Trujillo Report

0

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
about 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!