A helper script for managing a newsletter built on Google suite offerings

Code.gs

// README
// This lives in the Apps Script editor on Google Sheets
// Three sheets "unsubscribers", "subscribers", and "email" required
// Email lists are updated whenever this script is run
// "un/subscribers" receive Timestamp/Email values from Google Forms
// "email" contains fields for writing/sending email to subscriber lists

function sendEmail() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var unsubscribersSheet = spreadsheet.getSheetByName('unsubscribers');
  var unsubscribersValues = unsubscribersSheet.getDataRange().getValues();

  var unsubscribedEmails = [];
  for (var i = 1; i < unsubscribersValues.length; i++) {
    unsubscribedEmails.push(unsubscribersValues[i][1]);
  }

  var subscribersSheet = spreadsheet.getSheetByName('subscribers');
  var subscribersValues = subscribersSheet.getDataRange().getValues();

  var emailSheet = spreadsheet.getSheetByName('email');
  var subject = emailSheet.getRange(1, 2).getValue();
  var body = emailSheet.getRange(2, 2).getValue();
  var footer = emailSheet.getRange(3, 2).getValue();
  var emojis = emailSheet.getRange(4, 2).getValue();

  for (var i = 1; i < subscribersValues.length; i++) {
    var email = subscribersValues[i][1];
    if (unsubscribedEmails.includes(email)) {
      subscribersSheet.deleteRow(i+1);
      continue;
    }

    MailApp.sendEmail(email, subject + ' ' + emojis, body + '\n\n' + footer);
  }

  unsubscribersSheet.deleteRows(2, unsubscribersValues.length);
}