Skip to content

Instantly share code, notes, and snippets.

@jpinnix
Forked from kylestratis/Code.gs
Created June 16, 2021 20:46
Show Gist options
  • Save jpinnix/8f22b0be3e8d16201a575cb0ea8c7812 to your computer and use it in GitHub Desktop.
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 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