Google Apps脚本:服务在一天中被调用太多次:电子邮件 [英] Google Apps Script: Service invoked too many times for one day: email
问题描述
我正在尝试遍历电子表格，并为每一行发送一封电子邮件.发送电子邮件后，我想删除该行.
I'm trying to loop through an spreadsheet, and for each row send an email. Once the email is sent, i'd like to delete that row.
但是这不起作用.
由于某种原因，它开始像发疯一样发送电子邮件，并在某个时候达到极限并退出.
For some reason, it starts sending emails like crazy, and at some point it reaches the limit and quits.
它实际上只删除一行.
请参见下面的代码:
function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var startRow = 2; // First row of data to process
var maxRows = sheet.getMaxRows();
var range = sheet.getRange(startRow, 1, maxRows, 50)
var values = range.getValues();
for (var row in values) {
Logger.log('ID=' + values[row][0]);
var theID = values[row][0];
var message = "";
var sendto = "";
var emailAddress = values[row][2];
if (emailAddress=="Autre"){sendto="test@test.com"}
if (emailAddress=="Autre1"){sendto="test1@test.com"}
if (emailAddress=="Autre2"){sendto="test2@test.com"}
message+="\n ID: " + values[row][1];
message+="\n Project Number: " + values[row][2];
var subject = "Project ID: " + values[row][1];
if (sendto!=''){
MailApp.sendEmail(sendto, subject, message);
sheet.deleteRow(row+2)
}
}
逻辑是，如果有有效的电子邮件，请发送电子邮件，然后删除该行.
Logic is, if there's a valid email, send the email, delete the row.
但是一旦执行，它只会删除一行，像发疯一样发送电子邮件，并收到最大电子邮件错误.
But once it executes, it only deletes one row, sends emails like crazy, and get the email max error.
有想法吗?
推荐答案
您可以查询每日限额此处.
关于您的代码，查看执行记录，脚本似乎没有将 row 识别为整数，但将其识别为字符串，它将2附加起来然后进行转换.因此，您要删除第2行(或02)，然后删除第12行，然后删除第22行，依此类推.
Regarding your code, looking at the execution transcript it looks like the script isn't recognizing row as an integer but as a string, it attaches the 2 and only then converts it. So you're deleting row 2 (or 02), then row 12, then row 22 and so on.
但这无关紧要，因为您的逻辑存在问题.我会尽力解释它，并希望它是可以理解的.
This however is irrelevant because there's a problem in your logic. I'll try my best to explain it and hope that it is understandable.
如果在每次迭代后都删除一行，那么在第一次迭代中它将删除第2行(因为 row = 0 并加上2)，在第二次迭代中它将删除第3行(因为 row = 1 并加上2)，但由于在第一次迭代中您已经删除了一行，所以开始时位于第3行的数据现在实际上位于第2行.
If you delete a row after every iteration, then in the first iteration it would delete row 2 (because row = 0 and you add 2), in the second iteration it would delete row 3 (because row = 1 and you add 2), but since in your first iteration you already deleted a row the data that was in row 3 at the beginning is now actually in row 2.
解决此问题的一种方法是反转循环，从最大值开始，然后递减计数.这样，当您删除一行时，它不会影响下一行.我已经对您的代码进行了一些重写，以便它可以按预期工作.
One way to tackle this problem is by reversing the loop, start at the max and count down. This way when you delete a row it has no effect on the next one. I've rewritten your code a bit, so that it should work like intended.
function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var startRow = 2; // First row of data to process
var lastRow = sheet.getLastRow(); // getLastRow() gives you the last row that has content, while getMaxRows gives you the maximum number of rows in your sheet
var range = sheet.getRange(startRow, 1, lastRow-1, 50) // -1 because you want the number of rows from your starting position and not the index of the last row
var values = range.getValues();
for (row = values.length-1; row >= 0; row--) {
var theID = values[row][0];
var message = "";
var sendto = "";
var emailAddress = values[row][2];
if (emailAddress=="Autre"){sendto="test@test.com"}
if (emailAddress=="Autre1"){sendto="test1@test.com"}
if (emailAddress=="Autre2"){sendto="test2@test.com"}
message+="\n ID: " + values[row][1];
message+="\n Project Number: " + values[row][2];
var subject = "Project ID: " + values[row][1];
if (sendto!=''){
MailApp.sendEmail(sendto, subject, message);
sheet.deleteRow(row+2)
}
}
}
我希望这是可以理解的，我不太会解释事情.
I hope this is somewhat understandable, I'm not very good at explaining things.
这篇关于Google Apps脚本:服务在一天中被调用太多次:电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
