Open a google sheet and name the columns email, first_name, url, subject
Populate the sheet with some test data
Go to "tools" > "script editor"
Delete the starter code.
Paste the code from this gist into the window
Save
Go to "file" > "new" > "html file"
name it "mail_template"
Save.
Done.
| /** | |
| * Sends emails with data from the current spreadsheet. | |
| * | |
| * sheet column names: | |
| * A B C D | |
| * email first_name url subject | |
| */ | |
| function sendEmails() { | |
| var sheet = SpreadsheetApp.getActiveSheet(); | |
| // Define the range of values to reference | |
| var startRow = 2; //<- skips the column headers | |
| var startCol = 1; //<- start with column A | |
| /* UPDATE THESE */ | |
| var numRows = 3; //<- how many rows to process | |
| var numCols = 4; //<- number of columns with useful data | |
| // turn the range into an object, saved as a variable | |
| var dataRange = sheet.getRange(startRow, startCol, numRows, numCols); | |
| // Fetch values for each row in the Range. | |
| var data = dataRange.getValues(); | |
| // Starting with the first row, iterate over this loop | |
| for (var i = 0; i < data.length; ++i) { | |
| var row = data[i]; | |
| var email = row[0]; | |
| var subject = row[3]; | |
| console.log(subject) | |
| var template = HtmlService.createTemplateFromFile('mail_template'); | |
| template.first_name = row[1]; | |
| template.url = row[2]; | |
| MailApp.sendEmail({ | |
| to: email, | |
| // bcc: Session.getActiveUser().getEmail(), | |
| replyTo: 'replace-reply-to@gmail.com', | |
| subject: subject, | |
| htmlBody: template.evaluate().getContent() | |
| }); | |
| } | |
| } |
| <html> | |
| <head> | |
| <base target="_top"> | |
| </head> | |
| <body> | |
| <p> | |
| Hello <?= first_name ?>, | |
| <br> | |
| Check out <a href="<?= url ?>">this</a> link! | |
| </p> | |
| </body> | |
| </html> | |