💻

Free Code

Users get ready-to-use code at no cost.

📋

Easy Copy

Copy and use the code instantly.

⚡

Quick Learning

Understand concepts fast and clearly.

📝

Step-by-Step

Follow simple instructions to implement.

📅 August 16, 2024 💻 Tutorial ⭐ Beginner Friendly

How to Schedule Bulk Emails from Google Sheets using Google Apps Script

Ashish Dwivedi

Editorial Team • Tech Writer

About This Tutorial

To schedule bulk emails from Google Sheets using Google Apps Script, first enter recipient emails, subjects, and messages in the sheet. Then, create a script to read each row and send emails using Gmail. Set up a time-based trigger to schedule when the emails should be automatically sent.

Step 1: Click on the Copy button to copy the code snippet.
Step 2: Paste the copied code into your project’s script editor.

Apps Scripts Blog

⚡ script1.js

function scheduleDripEmails() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var dataRange = sheet1.getDataRange();
  var data = dataRange.getValues();
  var numRows = data.length;


  for (var i = 1; i < numRows; i++) {
    var recipientName = data[i][0];
    var recipientEmail = data[i][1];
    var scheduleTime = new Date(data[i][2]);
    var emailSubject = sheet2.getRange("A1").getValue();
    var emailBody = sheet2.getRange("A2").getValue();


    // Create a time-driven trigger for this row
    ScriptApp.newTrigger("sendDripEmail")
      .timeBased()
      .at(scheduleTime)
      .create();


    sheet1.getRange(i + 1, 4).setValue("Scheduled");
    sheet1.getRange(i + 1, 5).setValue(scheduleTime);
    Logger.log("Scheduled email to " + recipientName + " (" + recipientEmail + ") for " + scheduleTime);
  }
}


function sendDripEmail() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var dataRange = sheet1.getDataRange();
  var data = dataRange.getValues();
  var numRows = data.length;


  for (var i = 1; i < numRows; i++) {
    var recipientName = data[i][0];
    var recipientEmail = data[i][1];
    var scheduleTime = new Date(data[i][2]);
    var currentTime = new Date();


    // Send the email if the current time matches the schedule time
    if (scheduleTime <= currentTime) {
      var emailSubject = sheet2.getRange("A1").getValue().replace("", recipientName);
      var emailBody = sheet2.getRange("A2").getValue().replace("", recipientName);


      try {
        GmailApp.sendEmail(recipientEmail, emailSubject, emailBody);
        sheet1.getRange(i + 1, 4).setValue("Sent");
        sheet1.getRange(i + 1, 5).setValue(new Date());
        Logger.log("Sent email to " + recipientName + " (" + recipientEmail + ")");
      } catch (e) {
        sheet1.getRange(i + 1, 4).setValue("Failed");
        sheet1.getRange(i + 1, 5).setValue(new Date());
        Logger.log("Failed to send email to " + recipientName + " (" + recipientEmail + ")");
      }


      // Clear the schedule time for this row so the email is not sent again
      sheet1.getRange(i + 1, 3).clearContent();
    }
  }
}