Send an email from a list in Google Sheet

Here’s a step-by-step approach on how to create a Google Sheet and use Apps Script to send an email 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” and “Email”.
  • Add a new row for each person you want to email, including their name and email address.

Step 2: Write Apps Script to send the 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 “SendEmails”.
  • Copy and paste the following code into the script file:
function sendEmails() {
  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 an 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 subject = 'Test Email';
    var body = 'Dear ' + name + ',\n\nThis is a test email sent using Apps Script.\n\nSincerely,\nYour Name';
    MailApp.sendEmail(email, subject, body);
  • Save the script file.
  • Test the sendEmails() function by selecting “Run” > “sendEmails” from the menu in the script editor.
  • Grant the necessary permissions when prompted.
  • Check the email address in the sheet’s first row to ensure a test email was sent.
  • You can now update the data in the sheet and run the sendEmails() function again to send emails to everyone on the list.

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