SendEmail duration / Count per week in Googlsheet AppScript

userNVbm1 0
Viewed 27

I'm sorry if this question was already asked, I tried to look for it but couldn't find it.

I'm trying to send out alert emails with app script based on a specific cell value in google sheets. To point out the value is generated by a formula not by a user.

I created the below with a trigger that runs weekly.

My challenge is, that I want to be able to set up the trigger for the script to run daily to check for the condition but only send the email one time every 7 days until the condition is no longer met.

I greatly appreciate any advice if this is possible.

    function CheckSessions() {
  // Fetch the sessions available
  var sessionsRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Billing").getRange("K1"); 
  var sessions = sessionsRange.getValue();
  var ui = SpreadsheetApp.getUi(); 
  // Check totals sessions
  if (sessions == 1){
    // Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Billing").getRange("H2");
var emailAddress = emailRange.getValue();
// Send Alert Email.
var message = 'This is your Alert email!'; // Second column
var subject = 'Your Google Spreadsheet Alert';
MailApp.sendEmail(emailAddress, subject, message);
}
}
google-sheets google-apps-script
2 Answers

I believe you're saying you want the script to run daily, but if you've already sent the email within the past 7 days, don't send it again even if the condition is true.

One approach is to store the date for the last time you sent the reminder, and then do some date math to see if it has been 7 days since the last reminder.

You could store that date in a designated cell of the spreadsheet, but there are some good reasons not to (e.g. so a spreadsheet editor doesn't accidentally overwrite it). This is a typical use case for PropertiesService.

Properties are stored as a string, so you'll have to convert dates to/from strings.

Properties store values based on a key. If your script is only managing email alerts to one address, you can use a static key like lastSent. If you're managing alerts to multiple addresses then you could key by the email address itself. That's what I've done here:

function CheckSessions()
{
  // Fetch the sessions available
  var sessionsRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Billing").getRange("K1");
  var sessions = sessionsRange.getValue();
  // Check totals sessions
  if (sessions == 1)
  {
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Billing").getRange("H2");
    var emailAddress = emailRange.getValue();
    // Check if we've sent to this email within 7 days
    var propertyStore = PropertiesService.getScriptProperties()
    var lastSent = propertyStore.getProperty(emailAddress)
    if (!lastSent || getDaysDifference(new Date(lastSent), new Date()) >= 7)
    {
      // Send Alert Email.
      var message = 'This is your Alert email!'; // Second column
      var subject = 'Your Google Spreadsheet Alert';
      MailApp.sendEmail(emailAddress, subject, message);
      // Store the date in properties as an ISO string, to be fetched on next execution
      propertyStore.setProperty(emailAddress, (new Date()).toISOString())
    } else
    {
      console.log("Last email was sent on %s so we won't send one today", lastSent)
    }

  }
}

/**
 * Get days elapsed between two dates
 * @param {Date} startDate 
 * @param {Date} endDate 
 */
function getDaysDifference(startDate, endDate)
{
  var start = new Date(startDate);
  var end = new Date(endDate);
  start.setHours(0, 0, 0, 0);
  end.setHours(0, 0, 0, 0);
  var days = Math.round((end.getTime() - start.getTime()) / (1000 * 60 * 60 * 24));
  return days;
}
Aaron Dunigan AtLee0

Send Email once a day

function CheckSessions() {
  const ss = SpreadsheetApp.getActive()
  var sessions = ss.getSheetByName("Billing").getRange("K1").getValue();
  if (sessions == 1) {
    var emailAddress = ss.getSheetByName("Billing").getRange("H2").getValue();
    var message = 'This is your Alert email!'; 
    var subject = 'Your Google Spreadsheet Alert';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

function createTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "CheckSessions").length == 0) {
    ScriptApp.newTrigger("CheckSessions").timeBased().everyDays(1).atHour(2).create();//keeps you from creating more than one
  }
}
user30hu0
Related