How to Send Emails from Google Sheets (3 Methods)
Learn 3 ways to send emails directly from Google Sheets — Apps Script, Google Forms triggers, and add-ons. Includes templates and code examples.
Google Sheets + email is one of the most common automation requests for small businesses. Whether it’s sending personalized outreach, follow-up reminders, or automated reports — you don’t need expensive software to do it.
Here are three methods, ranked from most flexible to easiest.
Method 1: Apps Script (Most Flexible)
Google Apps Script gives you full control over email sending with the built-in MailApp and GmailApp services.
Basic: Send One Email
function sendEmail() {
MailApp.sendEmail({
to: '[email protected]',
subject: 'Hello from Google Sheets',
body: 'This email was sent automatically from a spreadsheet.'
});
}
Mail Merge: Send Personalized Emails from Sheet Data
Set up your sheet like this:
| name | company | status | |
|---|---|---|---|
| Alice | [email protected] | TechCorp | pending |
| Bob | [email protected] | DataInc | pending |
function sendMailMerge() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Contacts');
const data = sheet.getDataRange().getValues();
const headers = data[0];
// Find column indexes
const nameCol = headers.indexOf('name');
const emailCol = headers.indexOf('email');
const companyCol = headers.indexOf('company');
const statusCol = headers.indexOf('status');
let sentCount = 0;
for (let i = 1; i < data.length; i++) {
const row = data[i];
// Skip already sent
if (row[statusCol] === 'sent') continue;
const name = row[nameCol];
const email = row[emailCol];
const company = row[companyCol];
// Personalized email
const subject = `Quick question for ${company}`;
const body = `Hi ${name},\n\nI noticed ${company} might benefit from automating your email verification workflow.\n\nWould you be open to a quick chat this week?\n\nBest,\nMakeinfo Team`;
try {
MailApp.sendEmail({
to: email,
subject: subject,
body: body
});
// Mark as sent
sheet.getRange(i + 1, statusCol + 1).setValue('sent');
sheet.getRange(i + 1, statusCol + 2).setValue(new Date());
sentCount++;
// Respect rate limits — pause between emails
Utilities.sleep(1000);
} catch (e) {
sheet.getRange(i + 1, statusCol + 1).setValue('error: ' + e.message);
}
}
SpreadsheetApp.getActiveSpreadsheet().toast(`Sent ${sentCount} emails`);
}
HTML Emails with Formatting
function sendHtmlEmail(to, name) {
const htmlBody = `
<div style="font-family: Arial, sans-serif; max-width: 600px;">
<h2 style="color: #5573f4;">Hi ${name},</h2>
<p>Thanks for your interest in our Google Sheets tools.</p>
<p>Here's what you can do next:</p>
<ul>
<li>Verify emails in bulk</li>
<li>Clean phone numbers automatically</li>
<li>Enrich contact data</li>
</ul>
<a href="https://www.makeinfo.co/products/smart-email-verifier-addon"
style="display: inline-block; padding: 12px 24px; background: #5573f4; color: white; text-decoration: none; border-radius: 6px;">
Try It Free
</a>
</div>
`;
MailApp.sendEmail({
to: to,
subject: 'Your Google Sheets toolkit is ready',
htmlBody: htmlBody,
body: `Hi ${name}, Thanks for your interest. Visit https://www.makeinfo.co to get started.`
});
}
Daily Email Limits
| Account Type | Daily Limit |
|---|---|
| Free Gmail | 100 emails/day |
| Google Workspace | 1,500 emails/day |
Check your remaining quota:
function checkQuota() {
const remaining = MailApp.getRemainingDailyQuota();
Logger.log(`Remaining emails today: ${remaining}`);
return remaining;
}
Method 2: Google Forms + Trigger (Easiest for Notifications)
If you want to send an email every time someone submits a Google Form (which feeds into a Sheet), use a form submit trigger.
Setup
- Create a Google Form linked to your Sheet
- Open Extensions > Apps Script
- Add this function:
function onFormSubmit(e) {
const responses = e.namedValues;
const name = responses['Name'][0];
const email = responses['Email'][0];
const message = responses['Message'][0];
// Send confirmation to submitter
MailApp.sendEmail({
to: email,
subject: 'We received your message',
body: `Hi ${name},\n\nThanks for reaching out. We'll get back to you within 24 hours.\n\nYour message:\n${message}`
});
// Notify yourself
MailApp.sendEmail({
to: '[email protected]',
subject: `New form submission from ${name}`,
body: `Name: ${name}\nEmail: ${email}\nMessage: ${message}`
});
}
- Set up the trigger: Triggers > Add Trigger > onFormSubmit > From spreadsheet > On form submit
Now every form submission automatically sends both a confirmation email and a notification.
Method 3: Scheduled Email Reports
Send automated reports on a schedule using time-driven triggers.
Weekly Summary Email
function sendWeeklySummary() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sales');
const data = sheet.getDataRange().getValues();
// Calculate totals
let totalSales = 0;
let newCustomers = 0;
const oneWeekAgo = new Date();
oneWeekAgo.setDate(oneWeekAgo.getDate() - 7);
for (let i = 1; i < data.length; i++) {
const date = new Date(data[i][0]);
if (date >= oneWeekAgo) {
totalSales += data[i][2]; // amount column
newCustomers++;
}
}
const report = `
Weekly Sales Summary
====================
Period: ${oneWeekAgo.toDateString()} - ${new Date().toDateString()}
New customers: ${newCustomers}
Total sales: $${totalSales.toFixed(2)}
Average order: $${newCustomers > 0 ? (totalSales / newCustomers).toFixed(2) : '0.00'}
`;
MailApp.sendEmail({
to: '[email protected]',
subject: `Weekly Sales Report - ${new Date().toDateString()}`,
body: report
});
}
Set up a weekly trigger: Triggers > Add Trigger > sendWeeklySummary > Time-driven > Week timer > Every Monday 9am
Best Practices
1. Always Verify Emails First
Sending to invalid addresses hurts your domain reputation. Use Smart Email Verifier to validate your list before sending.
2. Add a Sent/Status Column
Always track which rows have been emailed to prevent duplicates:
// Check before sending
if (row[statusCol] === 'sent') continue;
// Mark after sending
sheet.getRange(i + 1, statusCol + 1).setValue('sent');
3. Rate Limit Your Sends
Don’t blast all emails at once. Add delays:
Utilities.sleep(1000); // 1 second between emails
For large lists, split sends across multiple days.
4. Use GmailApp for Thread Management
If you need to reply to existing threads or use labels:
// Send as reply in a thread
const thread = GmailApp.search('subject:"Project Update" to:[email protected]')[0];
if (thread) {
thread.reply('Here is this week\'s update...');
}
5. Handle Errors Gracefully
try {
MailApp.sendEmail(options);
} catch (e) {
// Log error in the sheet
sheet.getRange(i + 1, errorCol + 1).setValue(e.message);
Logger.log(`Failed to send to ${email}: ${e.message}`);
}
Comparison: Which Method to Use?
| Need | Method | Difficulty |
|---|---|---|
| Personalized outreach to a list | Apps Script mail merge | Medium |
| Form submission notifications | Google Forms trigger | Easy |
| Scheduled reports | Time-driven trigger | Easy-Medium |
| HTML emails with branding | Apps Script + HTML template | Medium |
| Large-scale campaigns (1000+) | Use a proper ESP (Mailchimp, SendGrid) | N/A |
Not sure whether to script it yourself or use an add-on? We compare the options in the best cold email tools for small businesses on Gmail. Before you send, run your template through the free mail merge previewer to catch empty fields.
Related Resources
- Smart Email Verifier Add-on — verify emails before sending
- Best Cold Email Tools for Small Businesses on Gmail — pick a mail merge or outreach tool
- Gmail Sending Limits Explained — why your script stops at 100 emails a day
- Google Sheets as a Database — structure your contact data
- Google Sheets Productivity Tips — more Sheets automation ideas