Search code examples
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         [email protected]   gid=1427007470 
Matthew Steele      [email protected]   gid=870087782
Megan Stallworth    [email protected]   gid=394261269
Blair Tutye         [email protected]   gid=415034801
Keith Rogers        [email protected]   gid=1276626525
Miguel Quarles      [email protected]   gid=1453684542
    
Name:               (unique Email Subj,Body) tab 1 and tab 2    
Bobby Sanchez       [email protected]   gid=2115259318 and gid=1361306336

Dummy spreadsheet

enter image description here

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();
}