Is there a way to skip empty, blank or Value! cells when using urlfetchapp and or in my datarange?? or some other solution to my problem?

Question

This code below has been scraped and copy pasted part by part from the web and it does work and with some help I was able to somewhat accomplish one of my goals for this project. The problem is this: I have a google sheet with data and I'm using UrlFetchApp to attach images to emails, but when my script runs into empty, blank or Value! cells it stops running and breaks. Is there any way to prevent this? I'm a total newbie and have no idea. I've searched online and did find this information... How do I skip blank cells using MailApp.SendEmail? and I tried to apply it to my situation, but was unable.

 if (emailAddress.match('@')  === null){
       continue;  // skip this iteration of the loop and go to the next one
    }; 

I tried changing that to my project situation...

if (image01.match('jpg')  === null){
       continue;  // skip this iteration of the loop and go to the next one
    };  

But unfortunately 'image01.match' is not a function ( I know all you coders are laughing right now) So I tried a few more things and was also didn't work....

if ((row[3]).match('jpg')  === null){
       continue;  // skip this iteration of the loop and go to the next one
    };  

So here is the code in its entirety.... any help or suggestion is very much appreciated and Thanks!

function emailImage(){
 var EMAIL_SENT = "EMAIL_SENT";

   var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2;
    var numRows = sheet.getLastRow();
   // Fetch the range of cells
   var dataRange = sheet.getRange(startRow, 1, numRows, 24)  
   var data = dataRange.getValues(); 

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var subject = row[1];       // Second column
    var message = row[2];       // Third column
    var image01 = UrlFetchApp.fetch(row[3]).getBlob();
    var image02 = UrlFetchApp.fetch(row[4]).getBlob();
    var image03 = UrlFetchApp.fetch(row[5]).getBlob();


var emailSent = row[23];     // 
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message, {attachments: [image01, image02, image03]});
      sheet.getRange(startRow + i, 24).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Answer 1

This seems to work.. A combination of several efforts. Thanks MetaMan!! I'm going to test a little more first....

function emailImage() {
 var EMAIL_SENT = "EMAIL_SENT";

   var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2;
    var numRows = sheet.getLastRow();
   // Fetch the range of cells
   var dataRange = sheet.getRange(startRow, 1, numRows, 24)  
   var data = dataRange.getValues(); 

  for (let i = 0; i < data.length; ++i) {
    let row = data[i];
    let emailAddress = row[0];  // First column
    let subject = row[1];       // Second column
    let message = row[2];       // Third column
    let options ={attachments:[]};
    let urls=[row[3],row[4],row[5]].filter(e => e != '');
    urls.forEach(u=>{let img = UrlFetchApp.fetch(u).getBlob();if(img)options.attachments.push(img);});
    let emailSent = row[23];   
    if (emailAddress && subject && message && emailSent != "EMAIL_SENT") {  // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message, options);
      sh.getRange(sr + i, 24).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

Answer 2

Try this:

function emailImage() {
  const sh = SpreadsheetApp.getActiveSheet();
  const sr = 2;
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, 24);//your previous code was not calculating the number of rows properly so you were trying run the code in rows that probably did not have any data.
  const data = rg.getValues();
  for (let i = 0; i < data.length; ++i) {
    let row = data[i];
    let emailAddress = row[0];  // First column
    let subject = row[1];       // Second column
    let message = row[2];       // Third column
    let image01 = UrlFetchApp.fetch(row[3]).getBlob();
    let image02 = UrlFetchApp.fetch(row[4]).getBlob();
    let image03 = UrlFetchApp.fetch(row[5]).getBlob();
    let emailSent = row[23];   
    if (emailAddress && subject && message && image01 && image02 && image03 && emailSent != "EMAIL_SENT") {  // Prevents sending duplicates and emails without all of the data being present
      MailApp.sendEmail(emailAddress, subject, message, { attachments: [image01, image02, image03] });
      sh.getRange(sr + i, 24).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

Try it this way:

function emailImage() {
  const sh = SpreadsheetApp.getActiveSheet();
  const sr = 2;
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, 24)
  const data = rg.getValues();
  for (let i = 0; i < data.length; ++i) {
    let row = data[i];
    let emailAddress = row[0];  // First column
    let subject = row[1];       // Second column
    let message = row[2];       // Third column
    let options ={attachments:[]};
    let image01 = UrlFetchApp.fetch(row[3]).getBlob();
    if(image01)options.attachments.push(image01);
    let image02 = UrlFetchApp.fetch(row[4]).getBlob();
    if(image02)options.attachments.push(image02);
    let image03 = UrlFetchApp.fetch(row[5]).getBlob();
    if(image03)options.attachments.push(image03);
    let emailSent = row[23];   
    if (emailAddress && subject && message && emailSent != "EMAIL_SENT") {  // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message, options);
      sh.getRange(sr + i, 24).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

You could also tryit this way:

function emailImage() {
  const sh = SpreadsheetApp.getActiveSheet();
  const sr = 2;
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, 24)
  const data = rg.getValues();
  for (let i = 0; i < data.length; ++i) {
    let row = data[i];
    let emailAddress = row[0];  // First column
    let subject = row[1];       // Second column
    let message = row[2];       // Third column
    let options ={attachments:[]};
    let urls=[row[3],row[4],row[5]].filter(e => e != '');

The preceeding line removes the urls that are blank and the next line only pushes image blobs that are return non null. So you can use as many images as you have columns to hold or you could put all of the url into one cell and separate them with say control enter which put '\n' between them then you could use. something like row[3].toString().split('\n').filter(e=>e)...etc

    urls.forEach(u=>{let img = UrlFetchApp.fetch(u).getBlob();if(img)options.attachments.push(img);});
    let emailSent = row[23];   
    if (emailAddress && subject && message && emailSent != "EMAIL_SENT") {  // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message, options);
      sh.getRange(sr + i, 24).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
    }
  }
}

Generally, my best code doesn't reveal itself to me until I go through a couple of iterations.

Answer 3

blob is an object type and you cannot directly .match() it.

Instead, use try-catch like this:

try{
    var image01 = UrlFetchApp.fetch(row[3].toString()).getBlob();

    console.log(image01.getContentType());  //should be image/png.
    console.log(image01);                   //should be {}

  }
  catch(Exception)
  {
    console.log("Link error!");
  }

If there's any error in the link, catch will handle it. Otherwise, you can send the mail in the try part.

Hope this will help you. If this wasn't what you seeking, then I apologize.