How to Send Grouped Bulk Emails by Party in Google Sheets (with Filters)

Recently, I had a situation where I needed to notify multiple parties about discrepancies in their deliveries. But here’s the twist — the data was in Google Sheets, and I didn’t want to send individual emails for each row. I wanted to group the entries by Party, apply certain conditions (like only rows where “Email Sent” is blank and “Difference” is not zero), and send a single consolidated email per party. And of course, mark them as “Email Sent” once done.

If you’ve been in a similar situation, this blog is for you.

🧩 The Spreadsheet Setup

I had two sheets:

• Expiry Details (the transaction log)
• Party (a lookup sheet with Party and their email IDs)

From the “Expiry Details” sheet, I wanted to filter rows where:

• Email Sent is blank
• Difference ≠ 0
• Debit Note Quantity is not empty
• Group them by Party
• Lookup Party Email from the Party sheet
• Send a single email with a styled HTML table
• Mark “Email Sent” as “Yes” after sending

🧠 What I thought?

I broke this into small logical blocks:

1. Filter rows with all the required conditions.
2. Group them using a groupMap[party] object.
3. Format HTML emails with nice tables.
4. Send email using GmailApp.
5. Mark those rows with “Yes” to avoid duplicates later.

💻 The Apps Script That Did It

Here’s the script I used:

function sendDiscrepancyEmails() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const expirySheet = ss.getSheetByName(“Expiry Details”);
const partySheet = ss.getSheetByName(“Party”);

const expiryData = expirySheet.getDataRange().getValues();
const headers = expiryData[0];
const rows = expiryData.slice(1);

const emailSentIndex = headers.indexOf(“Email Sent”);
const differenceIndex = headers.indexOf(“Difference”);
const partyIndex = headers.indexOf(“Party”);
const debitNoteDateIndex = headers.indexOf(“Debit Note Date”);
const debitNoteQtyIndex = headers.indexOf(“Debit Note Quantity”);

// Create Party Email Lookup from Party Sheet
const partyData = partySheet.getDataRange().getValues();
const partyEmailMap = {};
for (let i = 1; i < partyData.length; i++) {
const name = partyData[i][0];
const email = partyData[i][1];
if (name && email) partyEmailMap[name] = email;
}

// Group by Party with additional condition: Debit Note Quantity must not be empty
const groupMap = {};
rows.forEach((row, i) => {
const difference = row[differenceIndex];
const emailSent = row[emailSentIndex];
const party = row[partyIndex];
const debitNoteQty = row[debitNoteQtyIndex];

if (emailSent === “” && difference !== 0 && debitNoteQty !== “”) {
if (!groupMap[party]) groupMap[party] = [];
groupMap[party].push({ rowIndex: i + 1, rowData: row }); // i+1 for actual sheet row
}
});

for (const party in groupMap) {
const email = partyEmailMap[party];
if (!email) {
Logger.log(“Email not found for party: “ + party);
continue;
}

const group = groupMap[party];

let html = `
<p>Dear ${party},</p>
<p>Please find below the details of items with discrepancies:</p>
<table border=”1″ cellpadding=”5″ cellspacing=”0″ style=”border-collapse: collapse;”>
<tr style=”background-color: lightskyblue; font-weight: bold;”>
<th>Debit Note No.</th>
<th>Debit Note Date</th>
<th>Item</th>
<th>Quantity</th>
<th>Debit Note Quantity</th>
<th>Difference</th>
</tr>
`;

group.forEach(({ rowData }) => {
const dateRaw = rowData[debitNoteDateIndex];
const debitNoteDateFormatted = (dateRaw instanceof Date)
? Utilities.formatDate(dateRaw, Session.getScriptTimeZone(), “dd/MM/yyyy”)
: dateRaw;

html += `
<tr>
<td>${rowData[headers.indexOf(“Debit Note No.”)]}</td>
<td>${debitNoteDateFormatted}</td>
<td>${rowData[headers.indexOf(“Item”)]}</td>
<td>${rowData[headers.indexOf(“Quantity”)]}</td>
<td>${rowData[headers.indexOf(“Debit Note Quantity”)]}</td>
<td>${rowData[headers.indexOf(“Difference”)]}</td>
</tr>
`;
});

html += `
</table>
<p>Regards,<br>Your Company</p>
`;

GmailApp.sendEmail(email, `Difference in Received Quantity & Debit Note Quantity`, ‘’, { htmlBody: html });

// Update ‘Email Sent’ to Yes
group.forEach(({ rowIndex }) => {
expirySheet.getRange(rowIndex + 1, emailSentIndex + 1).setValue(“Yes”);
});
}
}

✨ How can this help us?

• No manual copy-pasting or composing emails.
• Grouped messages keep communication clean.
• You avoid emailing the same party again and again.

You can even schedule this to run every day using a trigger.

🏁 Conclusion

This small automation saved me hours of repeated emailing. If you’re working with grouped party-wise data, you should definitely give this a try. You can extend this logic for follow-ups, status reporting, and client summaries as well.

Automate your Google Sheets!!