MailApp.sendEmail无法与onEdit函数配合使用(Google表格脚本编辑器) [英] MailApp.sendEmail not working with onEdit function (Google Sheets Script Editor)
问题描述
我正在尝试创建弹出式窗口和电子邮件通知,以用于在编辑电子表格时超出某些每日限制的情况.
I'm trying to create pop-up and email notifications for when certain daily limits are exceeded on edit of the spreadsheet.
当与今天的日期有关的值超出限制并且我运行脚本时,将同时发送弹出窗口通知和电子邮件通知.但是当使用onEdit函数时,即编辑在编辑范围内定义的列..仅引发弹出通知,不发送电子邮件.
When the values relating to today's date exceed the limits and I run the script, both the pop-up and email notifications are sent. But when using the onEdit function, i.e editing the column defined in the edit range.. only pop-up notifications are raised and no emails are sent.
有人知道为什么onEdit函数适用于SpreadsheetApp.getUi().alert但不适用于MailApp.sendEmail吗?以及为什么在运行代码而不是onEdit时起作用?
Does anyone know why the onEdit function would work for SpreadsheetApp.getUi().alert but not MailApp.sendEmail? And why it works when the code is run, but not onEdit?
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Lab Analysis');
// define edit range
var editRange = sheet.getActiveRange();
var editRow = editRange.getRow();
var editCol = editRange.getColumn();
var range = sheet.getRange("AG6:AG");
var rangeRowStart = range.getRow();
var rangeRowEnd = rangeRowStart + range.getHeight();
var rangeColStart = range.getColumn();
var rangeColEnd = rangeColStart + range.getWidth();
// if cells lie within the edit range, run the following script
if (editRow >= rangeRowStart && editRow <= rangeRowEnd
&& editCol >= rangeColStart && editCol <= rangeColEnd) {
// set today's date and store a date object for today
var date = ss.getSheetByName('Daily Process
Limits').getRange("B1").setValue(new Date()).getValue();
// get values in date range
var daterange = sheet.getRange("A6:A").getValues();
// iterate the values in the range object
for(var i=0; i<daterange.length; i++) {
// compare only month/day/year in the date objects
if (new Date(daterange[i]).setHours(0,0,0,0) ==
date.setHours(0,0,0,0)) {
// if there's a match, set the row
// i is 0 indexed, add 6 to get correct row
var today_row = (i+6);
var today_set = ss.getSheetByName('Daily Process
Limits').getRange("D1").setValue(today_row);
var today_fos_tac_f1 = sheet.getRange("AE"+today_row).getValue();
var today_fos_tac_f2 = sheet.getRange("AF"+today_row).getValue();
var today_fos_tac_pf = sheet.getRange("AG"+today_row).getValue();
// pop up notifications to operator
if (today_fos_tac_f1 > 0.3) {
SpreadsheetApp.getUi().alert('pop up notification content'); }
if (today_fos_tac_f2 > 0.3) {
SpreadsheetApp.getUi().alert('pop up notification content'); }
if (today_fos_tac_pf > 0.3) {
SpreadsheetApp.getUi().alert('pop up notification content'); }
// Set email addresses
var emails = ['emailaddress@gmail.com'];
// send email notification to site manager
if (today_fos_tac_f1 > 0.3) {
MailApp.sendEmail(emails, 'High FOS:TAC in Fermenter 1', 'email content');}
if (today_fos_tac_f2 > 0.3){
MailApp.sendEmail(emails, 'High FOS:TAC in Fermenter 2', 'email content');}
if (today_fos_tac_pf > 0.3){
MailApp.sendEmail(emails, 'High FOS:TAC in Post Fermenter', 'email content');}
}
}
}}
推荐答案
我认为问题在于,由于简单的触发器会在不要求授权的情况下自动触发,因此无法访问需要授权的服务.参考
I think the issue is that since simple triggers fire automatically without asking for authorization they cannot access services that require authorization. Reference
这篇关于MailApp.sendEmail无法与onEdit函数配合使用(Google表格脚本编辑器)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!