Skip to content

Instantly share code, notes, and snippets.

@forstmeier
Created September 12, 2021 18:18
Show Gist options
  • Save forstmeier/d928f22a30dcb7dc87b85340410c3fb4 to your computer and use it in GitHub Desktop.
Save forstmeier/d928f22a30dcb7dc87b85340410c3fb4 to your computer and use it in GitHub Desktop.
A helper script for managing a newsletter built on Google suite offerings
// 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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment