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