google-sheetsgoogle-apps-scriptgmail

Google sheets apps script send multiple emails to different selected rows


I have an apps script that can send an email to a selected row however I would like it to work with multiples rows selected at the same time. I have found this response here however I just can't work out how to integrate it into my existing code. My (edited and redacted for privacy) code is as follows:

function draftEmailnew() {
  // define a couple constants for the function
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const ui = SpreadsheetApp.getUi();

  // Make sure the selected row has text
  if(sheet.getActiveCell().isBlank()) {
    ui.alert("Please select a row with a user");
    return;
  }

  // if valid, get the correct row of data.
    var row = sheet.getActiveCell().getRow();

  // get the range for that row
    var rowRange = sheet.getRange(row, 1, 1, 6).getValues();
    
  // Set variables to use in MailApp
    var name = rowRange[0][0];
    var email = rowRange[0][1];
    var username = rowRange[0][5];
    var subject = "xxx Database User Access" // Subject of email
    var emailBodyHtml =
"<p> Hi " + name + ",</p>" + 

"<p> You have been granted access to xxxxxxx</p>" + 

"<p> Your username is: <b>" + username + "</b></p>" + 

"<p> Please find attached ....." +  

"<p> Kind regards</p>" + 

"<p></p>" + 

"<p>name of company</p>";

  GmailApp.createDraft(email, subject, "", 
    {htmlBody: emailBodyHtml} // Options: Body (HTML)
    );

}

Solution

  • How to use getActiveRangeList(), Map, For loop.

    The updated code introduces the getActiveRangeList() method to capture all selected ranges in the spreadsheet. By utilizing map, it processes these ranges to extract their values effectively. The for loop then iterates through each user in the retrieved data, creating a draft email in Gmail for each individual. This approach enhances communication efficiency, particularly when sending notifications or access details to multiple users at once.

    Here's the full script

    function draftEmailnew() {
      
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      const ui = SpreadsheetApp.getUi();
    
      
      if (sheet.getActiveCell().isBlank()) {
        ui.alert("Please select a row with a user");
        return;
      }
    
    
    
      const getActiveRangelist = sheet.getActiveRangeList();
      const getResultActiveRange = getActiveRangelist.getRanges().map(rg => rg.getA1Notation()).flat();
    
    
     
    
      let rowRange = [];
    
      for (let j = 0; j < getResultActiveRange.length; j++) {
        rowRange.push(sheet.getRange(getResultActiveRange[j]).getValues());
      }
    
    
    
      for (let i = 0; i < rowRange.length; i++) {
        
        var name = rowRange[i][0][0];
        var email = rowRange[i][0][1];
        var username = rowRange[i][0][5];
        var subject = "xxx Database User Access" 
        var emailBodyHtml = `
          <p> Hi  ${name}  ,</p> 
    
          <p> You have been granted access to xxxxxxx</p>
    
          <p> Your username is: <b>"  ${username}  "</b></p>
    
          <p> Please find attached .....</p>
    
          <p> Kind regards</p>
    
          <p></p>
    
          <p>name of company</p>`;
    
    
    
        GmailApp.createDraft(email, subject, "",
          { htmlBody: emailBodyHtml } 
        );
      }
    
    }
    

    Sample output.

    Receiving email in draft.

    Sample2

    Active data in spreadsheet

    Sample1

    REFERENCE