What trigger should I use for Personalized Gmail Mail Merge from Google Sheets Columns?

This script is designed for: On Demand. Configure it under Apps Script > Triggers (clock icon).

What OAuth permissions does Personalized Gmail Mail Merge from Google Sheets Columns require?

This script requests the following authorized scopes: https://www.googleapis.com/auth/gmail.send, https://www.googleapis.com/auth/spreadsheets.

Gmail AutomationDifficulty: Intermediate⚡ On Demand

Personalized Gmail Mail Merge from Google Sheets Columns

Sends personalized outreach emails from your Gmail account to contacts listed in a spreadsheet, injecting dynamic first names, company names, and custom proposal links.

Required OAuth Scopes: https://www.googleapis.com/auth/gmail.send • https://www.googleapis.com/auth/spreadsheets

Code.gs (Apps Script V8 Runtime)

function executeMailMerge() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Outreach');
  const data = sheet.getDataRange().getValues();
  
  // Headers: [Email, FirstName, CompanyName, Status]
  for (let i = 1; i < data.length; i++) {
    const email = data[i][0];
    const name = data[i][1];
    const company = data[i][2];
    const status = data[i][3];
    
    if (status !== 'Sent' && email) {
      const subject = 'Collaboration inquiry for ' + company;
      const htmlBody = '<p>Hi ' + name + ',</p>' +
        '<p>I reviewed your work at <strong>' + company + '</strong> and wanted to reach out regarding our Google Workspace solutions.</p>' +
        '<p>Best regards,<br/>GoogleSolution Team</p>';
        
      GmailApp.sendEmail(email, subject, '', { htmlBody: htmlBody });
      sheet.getRange(i + 1, 4).setValue('Sent');
      Utilities.sleep(1000); // 1-second pause to prevent rate limit throttling
    }
  }
}

Line-by-Line Code Explanation

Line 5-10: Extracts recipient parameters and checks if already sent.

Line 12-19: Formats customized message and dispatches via GmailApp.

Line 20: Writes "Sent" status so duplicate emails are never dispatched.

Line 21: Throttles execution by 1 second to respect Google sending rates.

Step-by-Step Setup & Deployment

Create a sheet tab named "Outreach" with columns: Email, FirstName, CompanyName, Status.
Run `executeMailMerge` to send personalized emails.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Gmail Automation Snippets

Gmail