How to get my Google App Script to skip an error and go to the next item

Question

Help Needed... I have a script that basically sends out multiple emails (reports) to various recipients. However, when there is an error with a recipient's email address the script stops (eg if no email address is provided or an invalid email address for a recipient).

I need help in putting a line(s) of code that tells the script to skip over the one with the error and continue on to the next recipient.

I would appreciate any guidance provided. Respectfully, Kareem

This is the code below...

function emailALL() {

 var ui = SpreadsheetApp.getUi();
  var response = ui.alert('Confirm', 'Are you sure you want to email all the reports for this 
class?', ui.ButtonSet.YES_NO);



if (response == ui.Button.YES) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const studentTerm = ss.getSheetByName("Student Report");
  const sheetId = studentTerm.getSheetId();
  const sheet = ss.getSheetByName("Marks Master");
  const students = sheet.getRange("Ao2:Ao41").getValues();
  var loopCount= sheet.getRange("Ao1").getValues();

  var url_base = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/";
  let url = ss.getUrl();
  url += '#gid=';
  url += sheetId;
  Logger.log("Url: ", url);
  ss.setActiveSheet(studentTerm);

  for(var i=0; i<loopCount +1; i++){
    ss.getRange('C7').setValue(students[i]);
  
  // const sheet = ss.getRange(1, 1, 46, 16);
  // Subject of the email message
  const words = ss.getRange('C7');
  const title = words.getValues()[0];
  const term = ss.getRange("D9");
  const terms = term.getValues()[0];
  const email = ss.getRange('c3').getValues()[0];
  const school = ss.getRange('C1').getValues()[0];
  const subject = school + ": " + title + "_Term_"+ terms +" Report";
  const motto = ss.getRange('C2').getValues()[0];
  const body = "Good day Parent/Guardian, \n\nPlease find attached, school term report for " + title + "."+ "\n\nRegards, \n" + school + "\n" + motto + "\n";

  //"Good day Parent/Guardian," & vbLf & vbLf _
 // & "Please find attached, " & Title & "'s School Term Report." & vbLf & vbLf _
 // & "Regards," & vbLf _
  //& School & vbLf _
 // & Motto & vbLf
  
  // Email Text. You can add HTML code here - see ctrlq.org/html-mail
 
   // let sheet = ss.getSheetName("Student-Term");
  
 // const unformattedUrl = studentTerm.getUrl();
 // Logger.log("SpreadSheet Url " + unformattedUrl);
 // let formattedUrl = unformattedUrl.split("/");
 // formattedUrl = formattedUrl.slice(0, formattedUrl.length - 1);

 // formattedUrl = formattedUrl.join("/");
 // formattedUrl = formattedUrl + "/export?";
  
    const exportOptions ='export?exportFormat=pdf&format=pdf'   //export as pdf

      // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
      // following parameters are optional...
      + '&size=letter'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=false'        // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=true'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  Logger.log(url+exportOptions);
  var response = UrlFetchApp.fetch(url_base+exportOptions, params);
  var blob = response.getBlob().setName(title + '.pdf')
  
  var mailOptions = {
      attachments:blob
    }
  
  //var pdfFile = ss.getBlob().getAs('application/pdf').setName("Pdf1");
  
  //// Send the PDF file as an attachement 
    GmailApp.sendEmail(email, subject, body, mailOptions);
    Utilities.sleep(1000)
  }

}
else {}
}

score 0 · Accepted Answer · answered Mar 17 '22 at 03:41

After

const email = ss.getRange('c3').getValues()[0];

add

if(email === '') continue;

The above will handle those cases where the cell used to enter the email is blank (empty). A simple way to handle all other cases is by using try .. catch

Replace

GmailApp.sendEmail(email, subject, body, mailOptions);

by

try {
  GmailApp.sendEmail(email, subject, body, mailOptions);
} catch(error) {
  console.log(error.message, error.stack);
  continue;
}

For a more sophisticated approach take a checkout What's the best way to validate an email address in JavaScript?

References

How to get my Google App Script to skip an error and go to the next item

1 Answers1