0

I have this spreadsheet Example Sheet

As people sign in an automatic time stamp goes into column A, column L then calculates a date/time 12 hours in the future.

What I would like to do is have the sheet generate an automated e-mail to the address in column K at the designated time in Column L -ONLY IF- the person hasn't entered a time in the "Time Signed Out" of Column I.

Basically, people are good at signing in, but ALWAYS forget to sign out. I'm hoping to automate a reminder e-mail for them to go back in to the sheet and put a sign out time. If not they will get an e-mail reminder.

The e-mail would have a standard subject line of: "Please Remember to Sign Out" The e-mail would have a standard body of: "It appears you may not have signed out, please click this link to sign out now"

Can I use the time or minute trigger to make the script occur even when the sheet isn't open?

I'm okay at writing very A-B-C scripts, but this one is beyond me and the examples I am getting when I search don't cover this scenario.

Thanks in advance for any help.

DFW Emergency Management
  • 31
  • 1
  • 4
  • You might want to check this [related SO post](https://stackoverflow.com/a/21744490/5995040) for code implementation. Then use [Time-driven triggers](https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers) this will let your scripts execute at a particular time or recurring. Also be noted of the Apps Script services impose daily [quotas](https://developers.google.com/apps-script/guides/services/quotas) and hard limitations on some features. Hope this helps. – Mr.Rebot Sep 07 '17 at 17:47
  • I'm tried to adjust that code to fit my needs but I keep running into problems. The part that is really hard to get past is sending an e-mail only when there is no data entry in column I. I'm not sure how to write up that logic if/then in the context of the rest of the code. I'm going to keep messing with it, but I'm not confident I can solve it without help from a better coder. – DFW Emergency Management Sep 07 '17 at 19:06
  • Perhaps a simpler solution is to use a script that's on a clock trigger to run every 3 hrs (say) & build in the email logic around whether or not various fields are populated & the time that the script starts running. Would by easier to implement than writing a new clock trigger every time user logs in. It doesn't give you the level of responsiveness you're looking for, but is this good enough for the purpose? Please post some code so that we can examine the logic & advise. – Dean Ransevycz Sep 08 '17 at 05:15

1 Answers1

0

Yet Another Email Question

This is totally untested but written to your specification. I hope it works okay for you. You will need to run setup my trigger and the script itself to authorize it. You should also create a column that is normally empty until you send an email and then you fill it with something. Put something in the if statement like && vA[i][column number - 1] and after the sendEmail put in something like. sh.getRange(i+1,column number).setValue('Dont send again'); and then the next time you run the script it won't send emails to people who already received them.

function sendEmailToThoseWhoHaveNotSignedOut()
{
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var lastHeaderRow=1;
  var now=new Date();
  var rdq=MailApp.getRemainingDailyQuota();
  for(var i=lastHeaderRow;i<vA.length;i++)
  {
    if(new Date(vA[i][11]).valueOf()<now.valueOf() && !vA[i][8] && vA[i][10] && rdq>0)
    {
      MailApp.sendEmail(vA[i][10], 'Please Remember to Sign Out', 'It appears you may not have signed out, please click this link to sign out now.')
    }
  }
}

function setUpMyTrigger()
{
  if(!isTrigger('sendEmailToThoseWhoHaveNotSignedOut'))//This prevents you from setting up more than one trigger at a time for the same function.
  {
    ScriptApp.newTrigger('sendEmailToThoseWhoHaveNotSignedOut').timeBased().everyHours(1).create();
  }
}

function isTrigger(funcName)
{
  var r=false;
  if(funcName)
  {
    var allTriggers=ScriptApp.getProjectTriggers();
    var allHandlers=[];
    for(var i=0;i<allTriggers.length;i++)
    {
      allHandlers.push(allTriggers[i].getHandlerFunction());
    }
    if(allHandlers.indexOf(funcName)>-1)
    {
      r=true;
    }
  }
  return r;
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Cooper, thank you for the script above. It is almost working for me. I made slight adjustments to the column order etc, and now it will send e-mails every hour which is great. The problem I have right now, or at least as far as I can tell, is that it is sending the e-mail regardless of if there is "sign out" time entered into the cell. Also it is only sending to the bottom e-mail. As I stare at the code I can't figure out what parameter to change to fix this. So right now it is sending an e-mail every hour to the second name on the list in the example sheet above. – DFW Emergency Management Sep 27 '17 at 13:33
  • *** Correction, it is sending to both e-mails, or "all" e-mails regardless of if there is a signed out time in the sign out time column. How can I fix it so that it properly recognizes a time in that column/cell and only send an e-mail if it is empty? – DFW Emergency Management Sep 27 '17 at 13:41
  • Does your example sheet reflect the column adjustments that you mentioned above. – Cooper Sep 27 '17 at 15:24
  • Yes it does. That sheet is the one I am using, testing, and running the script in. – DFW Emergency Management Sep 27 '17 at 19:51
  • You copied the code incorrectly. It's supposed to be `!vA[i][8]`. – Cooper Sep 27 '17 at 21:31
  • If you leave the default (the top one) validation value on column i null "" then I don't think you'll run through all of the blank rows. – Cooper Sep 27 '17 at 21:50
  • It looks like you corrected the !vA[i][8] piece, I will make the fix to column i validation table. I will test it again and confirm all is good. Thanks for your help, it will help during potential disasters in the future. – DFW Emergency Management Sep 27 '17 at 22:00