Created
August 25, 2025 23:28
-
-
Save loopspace/f6ac15cad4aa9271ed4fa44b02220d50 to your computer and use it in GitHub Desktop.
Google Apps Script for sending emails from data in a spreadsheet
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| * App Script for sending emails directly from a spreadsheet. | |
| * This is designed to be contained in the App Script extension for the spreadsheet itself. | |
| */ | |
| /* I usually have several sheets in this spreadsheet */ | |
| const SheetName = '<Sheet name of data>'; | |
| /* | |
| * Each row consists of data, one column of which is the email addresses. | |
| * The column indexing is 0-based, so this is column A | |
| */ | |
| const EmailColumn = 0; | |
| /* | |
| * The idea is to assemble the actual text in the spreadsheet and have that in a column. | |
| * This makes it easier to check the text of each (or some) entries prior to sending. | |
| */ | |
| const EmailText = 2; | |
| /* I also tend to have a column with a boolean as to which rows to include in the mail merge */ | |
| const SendEmail = 1; | |
| /* And prior to sending it, I log everything so that I can double check */ | |
| const ActuallySend = false; | |
| /* Needed to set the reply-to, and I also CC myself in every email */ | |
| const myEmail = '[email protected]'; | |
| function sendDetailsByMail() { | |
| // Logger.log(MailApp.getRemainingDailyQuota()); // Full quota is 1500 | |
| const ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| const sheet = ss.getSheetByName(SheetName); | |
| /* Sometimes I end up setting the last row to a fixed value */ | |
| const lr = sheet.getLastRow(); | |
| /* First row is headers, and we make the header of the Email Text column the subject for the emails */ | |
| const subjcell = sheet.getRange(1,EmailText+1,1,1); | |
| const subject = subjcell.getValue(); | |
| /* Range for the emails */ | |
| const range = sheet.getRange(2,1,lr,Math.max(EmailColumn, EmailText, SendEmail)+1); | |
| const data = range.getValues(); | |
| var message; | |
| for (var i = 0; i < data.length; i++) { | |
| /* Only send email if there's an address, text, and flag */ | |
| if (data[i][EmailColumn] != '' && data[i][EmailText] != '' && data[i][SendEmail] | |
| ) { | |
| message = | |
| { | |
| to: data[i][EmailColumn], | |
| cc: myEmail, | |
| body: data[i][EmailText], | |
| replyTo: myEmail, | |
| subject: subject | |
| }; | |
| if (ActuallySend) { | |
| /* If we're actually sending email, then send it and log a success message */ | |
| MailApp.sendEmail(message); | |
| Logger.log(`Sending to ${data[i][EmailColumn]}: ${data[i][EmailText]}`); | |
| } else { | |
| /* If we're not sending emails, log the entire message (with headers) for checking */ | |
| Logger.log(message); | |
| } | |
| } | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment