google-sheetsgoogle-apps-script
How to send emails with unique attachments in Google Apps Script?
I have implemented a function in Apps script, with the following goals:
Reference tab: Tracks a list of Users and their coinciding emails. Also provides unique links (in column N), which are separate spreadsheets, that store a user's records.
Report - Current Processing Period tab: Provides Admin staff a button to 'Email All'
Primary goal: Implement a master function, that sends unique emails to All users simultaneously, each with an attachment, that stores their coinciding records.
Current issue: The emails are being sent successfully as anticipated, but they're currently in separate functions (7). How can this be accomplished in one main function?
I have searched for comparable questions, but I'm having trouble identifying an example of attaching a unique sheet for each user on the email distribution list.
function sendEmailReportInsp1() {
let token = ScriptApp.getOAuthToken();
let spreadsheet = SpreadsheetApp.openById("1FihtFsJ6IxEEO38rTmmYtQySp6nE1ofuT8s4TNrqO0I");
var range = range ? range : "A1:K109";//Set the default to whatever you want
var [[emailInspName],, [emailAddr]] = spreadsheet.getRange("B4:B6").getDisplayValues();
var [[file_name]] = spreadsheet.getRange("A8").getDisplayValues();
let spreadsheetId = spreadsheet.getId();
let sheetId = "1427007470";
let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId +
'&range=' + range +
"&portrait=true" + "&exportFormat=pdf";
let request = UrlFetchApp.fetch(sheetUrl, {
headers: {
'Authorization': 'Bearer ' + token
}
});
let content = request.getContent();
let message = {
to: emailAddr,
subject: "Independent Contractor Invoice and Inspection Statement: " + emailInspName,
body: "Attached is your statement of Inspections for the latest pay period.\n\nAny questions?\n\nPlease contact Home Commercial Inspections.\n\nThank you,\nMr. James Jones",
attachments: [{
fileName: "Inspections: " + file_name,
content: content,
mimeType: "application/pdf"
}]
}
MailApp.sendEmail(message);
}
function sendEmailReportInsp2() {
let token = ScriptApp.getOAuthToken();
let spreadsheet = SpreadsheetApp.openById('1GBL_aex6FHQ1fFegB2Oue5GMp0RGpcCefhaMvwNDNsQ');
var range = range ? range : "A1:K109";//Set the default to whatever you want
var [[emailInspName],, [emailAddr]] = spreadsheet.getRange("B4:B6").getDisplayValues();
var [[file_name]] = spreadsheet.getRange("A8").getDisplayValues();
let spreadsheetId = spreadsheet.getId();
let sheetId = "870087782";
let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId +
'&range=' + range +
"&portrait=true" + "&exportFormat=pdf";
let request = UrlFetchApp.fetch(sheetUrl, {
headers: {
'Authorization': 'Bearer ' + token
}
});
let content = request.getContent();
let message = {
to: emailAddr,
subject: "Independent Contractor Invoice and Inspection Statement: ",
body: emailInspName + "\n\nPlease find attached your invoice and inspection statement for the processing period.\n\nFeel free to reach out if you have any questions.\n\nThanks,\nHome Commercial Inspections",
attachments: [{
fileName: "Invoice and Inspection statement for processing period",
content: content,
mimeType: "application/pdf"
}]
}
MailApp.sendEmail(message);
}
function sendEmailReportInsp3() {
let token = ScriptApp.getOAuthToken();
let spreadsheet = SpreadsheetApp.openById("1wWtv5PiTZVNKd9pi94brQOdy44yqToSoeO3a-wyyWVg");
var range = range ? range : "A1:K109";//Set the default to whatever you want
var [[emailInspName],, [emailAddr]] = spreadsheet.getRange("B4:B6").getDisplayValues();
var [[file_name]] = spreadsheet.getRange("A8").getDisplayValues();
let spreadsheetId = spreadsheet.getId();
let sheetId = "394261269";
let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId +
'&range=' + range +
"&portrait=true" + "&exportFormat=pdf";
let request = UrlFetchApp.fetch(sheetUrl, {
headers: {
'Authorization': 'Bearer ' + token
}
});
let content = request.getContent();
let message = {
to: emailAddr,
subject: "Independent Contractor Invoice and Inspection Statement: " + emailInspName,
body: "Attached is your statement of Inspections for the latest pay period.\n\nAny questions?\n\nPlease contact Home Commercial Inspections.\n\nThank you,\nMr. James Jones",
attachments: [{
fileName: "Invoice and Inspection statement for processing period",
content: content,
mimeType: "application/pdf"
}]
}
MailApp.sendEmail(message);
}
function sendEmailReportInsp4() {
let token = ScriptApp.getOAuthToken();
let spreadsheet = SpreadsheetApp.openById("1OAFDcl81ryrRxQNWv2mxs5ufBeN-DKNobU3PbB1dYSM");
var range = range ? range : "A1:K109";//Set the default to whatever you want
var [[emailInspName],, [emailAddr]] = spreadsheet.getRange("B4:B6").getDisplayValues();
var [[file_name]] = spreadsheet.getRange("A8").getDisplayValues();
let spreadsheetId = spreadsheet.getId();
let sheetId = "415034801";
let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId +
'&range=' + range +
"&portrait=true" + "&exportFormat=pdf";
let request = UrlFetchApp.fetch(sheetUrl, {
headers: {
'Authorization': 'Bearer ' + token
}
});
let content = request.getContent();
let message = {
to: emailAddr,
subject: "Independent Contractor Invoice and Inspection Statement: " + emailInspName,
body: "Attached is your statement of Inspections for the latest pay period.\n\nAny questions?\n\nPlease contact Home Commercial Inspections.\n\nThank you,\nMr. James Jones",
attachments: [{
fileName: "Inspections: " + file_name,
content: content,
mimeType: "application/pdf"
}]
}
MailApp.sendEmail(message);
}
function sendEmailReportInsp5() {
let token = ScriptApp.getOAuthToken();
let spreadsheet = SpreadsheetApp.openById("1RYXgtJZLPcTKzbmTbfu46T-DVLv5s2jHTJibxSJZ3ZE");
var range = range ? range : "A1:K109";//Set the default to whatever you want
var [[emailInspName],, [emailAddr]] = spreadsheet.getRange("B4:B6").getDisplayValues();
var [[file_name]] = spreadsheet.getRange("A8").getDisplayValues();
let spreadsheetId = spreadsheet.getId();
let sheetId = "1276626525";
let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId +
'&range=' + range +
"&portrait=true" + "&exportFormat=pdf";
let request = UrlFetchApp.fetch(sheetUrl, {
headers: {
'Authorization': 'Bearer ' + token
}
});
let content = request.getContent();
let message = {
to: emailAddr,
subject: "Independent Contractor Invoice and Inspection Statement: " + emailInspName,
body: "Attached is your statement of Inspections for the latest pay period.\n\nAny questions?\n\nPlease contact Home Commercial Inspections.\n\nThank you,\nMr. James Jones",
attachments: [{
fileName: "Inspections: " + file_name,
content: content,
mimeType: "application/pdf"
}]
}
MailApp.sendEmail(message);
}
function sendEmailReportInsp6() {
let token = ScriptApp.getOAuthToken();
let spreadsheet = SpreadsheetApp.openById("19IjM4PD7PgoGSOLiGNKqyuJUdDMUICGlEvRAUuR_AFs");
var range = range ? range : "A1:K109";//Set the default to whatever you want
var [[emailInspName],, [emailAddr]] = spreadsheet.getRange("B4:B6").getDisplayValues();
let spreadsheetId = spreadsheet.getId();
let sheetId = "1453684542";
var [[file_name]] = spreadsheet.getRange("A8").getDisplayValues();
let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId +
'&range=' + range +
"&portrait=true" + "&exportFormat=pdf";
let request = UrlFetchApp.fetch(sheetUrl, {
headers: {
'Authorization': 'Bearer ' + token
}
});
let content = request.getContent();
let message = {
to: emailAddr,
subject: "Independent Contractor Invoice and Inspection Statement: " + emailInspName,
body: "Attached is your statement of Inspections for the latest pay period.\n\nAny questions?\n\nPlease contact Home Commercial Inspections.\n\nThank you,\nMr. James Jones",
attachments: [{
fileName: "Inspections: " + file_name,
content: content,
mimeType: "application/pdf"
}]
}
MailApp.sendEmail(message);
}
// *** Unique Email Template (requires sheets gid=2115259318 and gid=1361306336)
function sendEmailReport_SpecialRep() {
let token = ScriptApp.getOAuthToken();
let spreadsheet = SpreadsheetApp.openById('1ztpubW5-h9myOpC9GGMfxzGoQYtM46czOqiE8tidCl8');
var range = range ? range : "A1:K109";//Set the default to whatever you want
var [[emailInspName],, [emailAddr]] = spreadsheet.getRange("L2:L4").getDisplayValues();
var [[file_name]] = spreadsheet.getRange("A1").getDisplayValues();
let spreadsheetId = spreadsheet.getId();
let sheetId = "2115259318"; //gid=2115259318 and gid=1361306336
let sheetUrl = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?" + "format=xlsx" + "&gid=" + sheetId +
'&range=' + range +
"&portrait=true" + "&exportFormat=pdf";
let request = UrlFetchApp.fetch(sheetUrl, {
headers: {
'Authorization': 'Bearer ' + token
}
});
let content = request.getContent();
let message = {
to: emailAddr,
subject: "Independent Contractor (IC) Current Payee Totals",
body: emailInspName + "\n\nPlease find attached the IC Payee Totals for the current processing period.\n\nFeel free to reach out if you have any questions.\n\nThanks,\nHome Commercial Inspections",
attachments: [{
fileName: "Payee Totals for current processing period",
content: content,
mimeType: "application/pdf"
}]
}
MailApp.sendEmail(message);
}
For example:
Name: (Same Email Subj,Body) tab (to be Attached in email):
James Smith TestEmail01@gmail.com gid=1427007470
Matthew Steele TestEmail02@gmail.com gid=870087782
Megan Stallworth TestEmail03@gmail.com gid=394261269
Blair Tutye TestEmail04@gmail.com gid=415034801
Keith Rogers TestEmail05@gmail.com gid=1276626525
Miguel Quarles TestEmail06@gmail.com gid=1453684542
Name: (unique Email Subj,Body) tab 1 and tab 2
Bobby Sanchez TestEmail09@gmail.com gid=2115259318 and gid=1361306336
Solution
About your question The emails are being sent successfully as anticipated, but they're currently in separate functions (7). How can this be accomplished in one main function?, as a simple script, how about the following script?
In this modification, from The emails are being sent successfully as anticipated, it supposes that each function works fine.
Sample script:
By this modification, when sample is run, all 7 functions are run in order.
function sample() {
sendEmailReportInsp1();
sendEmailReportInsp2();
sendEmailReportInsp3();
sendEmailReportInsp4();
sendEmailReportInsp5();
sendEmailReportInsp6();
sendEmailReport_SpecialRep();
}