Send personalised emails in Google Sheets

Here’s a step-by-step approach on how to create a Google Sheet and use Apps Script to send personalized emails to every email ID in a new row in the sheet:

Step 1: Setup Google Sheet to export

• Create a new Google Sheet by going to Google Drive and selecting “New” > “Google Sheets”.
• Rename the sheet to something descriptive like “Email List”.
• Add column headings for “Name”, “Email”, and any other details you want to personalize the email with, such as “Event Name” or “Discount Code”.
• Add a new row for each person you want to send a personalized email to, including their name, email address, and any other details to personalize the email with.

Step 2: Write Apps Script to send personalise email

• Open the Apps Script editor by selecting “Tools” > “Script editor” from the Google Sheets menu.
• In the Apps Script editor, create a new script file by selecting “File” > “New” > “Script file”.
• Name the script file something descriptive like “SendPersonalizedEmails”.
• Copy and paste the following code into the script file:

function sendPersonalizedEmails() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Email List'); // Replace 'Email List' with the name of your sheet
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var numRows = data.length;

  // Loop through each row in the sheet and send a personalized email
  for (var i = 1; i < numRows; i++) { // Start at row 1 to skip column headings
    var name = data[i][0];
    var email = data[i][1];
    var eventName = data[i][2];
    var discountCode = data[i][3];
    var subject = 'Personalized Email for ' + eventName;
    var body = 'Dear ' + name + ',\n\nThank you for your interest in ' + eventName + '.\n\nAs a special thank you, please use the following discount code when registering: ' + discountCode + '.\n\nWe look forward to seeing you at the event!\n\nSincerely,\nYour Name';
    MailApp.sendEmail(email, subject, body);
  }
}

• Save the script file.
• Test the sendPersonalizedEmails() function by selecting “Run” > “sendPersonalizedEmails” from the menu in the script editor.
• Grant the necessary permissions when prompted.
• Check the email address in the first row of the sheet to make sure that a personalized email was sent.
• You can now update the data in the sheet and run the sendPersonalizedEmails() function again to send personalized emails to everyone on the list.

That’s it! You can now use this script to send personalized emails to every email ID in a new row in a Google Sheet.