-
-
Save jpinnix/8f22b0be3e8d16201a575cb0ea8c7812 to your computer and use it in GitHub Desktop.
Appscript to generate an email when setting "Added to Roam" column to TRUE. Only works with gmail address
This file contains 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
// This constant is written in column C for rows for which an email | |
// has been sent successfully. | |
var ADDED = true; | |
var EMAIL_SENT = true; | |
/** | |
* Sends non-duplicate emails with data from the current spreadsheet. | |
*/ | |
function sendEmails() { | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
var startRow = 1; // First row of data to process | |
// Fetch the range of cells A2:B3 | |
var dataRange = sheet.getDataRange(); | |
// Fetch values for each row in the Range. | |
var data = dataRange.getValues(); | |
for (var i = startRow; i < data.length; ++i) { | |
var row = data[i]; | |
var emailAddress = row[1]; // 2nd column | |
var name = row[2]; | |
var url = "YOUR PUBLIC GRAPH URL HERE"; | |
var message = `Hi ${name}! | |
Thank you for your support. You have been added to my public Roam graph and should be able to access it via your graph dashboard. | |
If you are unable to see it there, the direct URL is ${url} | |
Thank you again! | |
YOURNAMEHERE`; | |
var added = row[5]; // F (6th) column | |
var emailSent = row[6]; // G (7th) column | |
if (emailSent !== EMAIL_SENT && added == ADDED) { // Prevents sending duplicates | |
var subject = 'Access to my public Roam graph'; | |
MailApp.sendEmail(emailAddress, subject, message); | |
Logger.log("Email sent to " + emailAddress); | |
sheet.getRange(startRow + i, 7).setValue(EMAIL_SENT); | |
// Make sure the cell is updated right away in case the script is interrupted | |
SpreadsheetApp.flush(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment