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
- https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/continue
- https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/try...catch
Related
-