根据单元格中的日期/时间在Google表格中自动生成电子邮件？ [英] Automatically Generate an E-mail in a Google Sheet based of a Date/Time in a Cell?

问题描述

我有这张电子表格
示例表 b
$ b

当人们登录自动时间戳记到A列时，L列将在未来计算12小时的日期/时间。

我想要做的是让工作表生成一个自动发送电子邮件到列K中的指定时间的地址。 -ONLY IF - 还没有进入第一栏时间签出的时间。

基本上，人们擅长登录，但总是忘记退出。
我希望能够自动提醒电子邮件，让他们重新回到工作表并提出退出时间。如果不是，他们会收到电子邮件提醒。

电子邮件的标准主题是：请记住退出
电子邮件将有一个标准的机构：看起来你可能没有注销，请点击此链接现在注销

我可以使用时间或即使在工作表未打开的情况下也可以使用脚本启动脚本？

我很擅长编写非常ABC的脚本，但是这个不在我身上，我搜索的例子并没有涵盖这种情况。

感谢您提供任何帮助。

这完全没有经过测试，但写入您的规格。我希望它适合你。您将需要运行安装我的触发器和脚本本身来授权它。您还应该创建一个通常为空的列，直到您发送一封电子邮件，然后填写一些内容。在if语句中加入一些内容，如&& vA [i] [列号-1] 并在sendEmail放入类似内容之后。 sh.getRange（i + 1，column number）.setValue（'Dont send again'）; 然后在下次运行脚本时它不会发送电子邮件给已经收到他们的人。

 函数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（）; （new Date（vA [i] [11]）。valueOf（）< now.valueOf（$ i 
 for（var i = lastHeaderRow; i< vA.length; i ++） （）&& vA [i] [8]& vA [i] [10]& rdq> 0）
 {
 MailApp.sendEmail（vA [i] [10]，'请记住退出'，'看起来你可能还没有登出，请点击此链接立即退出。'）
} 
} 
} 
 
函数setUpMyTrigger（）
 {
 if（！isTrigger（'sendEmailToThoseWhoHaveNotSignedOut'））//这可以防止您为同一函数一次设置多个触发器。 
 {
 ScriptApp.newTrigger（'sendEmailToThoseWhoHaveNotSignedOut'）。timeBased（）。everyHours（1）.create（）; 
 
 
 
函数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; 
} 
  

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.

解决方案

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;
}

这篇关于根据单元格中的日期/时间在Google表格中自动生成电子邮件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！