Skip to content

Instantly share code, notes, and snippets.

@kylestratis
Last active November 9, 2021 20:03
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save kylestratis/cb46ab0bcf0d820aa29a8a98c59126f9 to your computer and use it in GitHub Desktop.
Save kylestratis/cb46ab0bcf0d820aa29a8a98c59126f9 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 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