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

Time:03-18

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 {}
}

CodePudding user response:

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

Related

-

  • Related