我对我的Google Apps自定义脚本感到困惑(特别是对于Google Spreadsheets) .
我从https://developers.google.com/apps-script/articles/sending_emails#section-2-improvements(请参阅"Section 2: Improvements")重现了这一课,但它不起作用:即使单元格已经包含"EMAIL_SENT"(我使用"NOTIFICATION_SENT"而不是这个),脚本也会再次重新发送所有电子邮件 .
以下是自定义脚本的来源:
var NOTIFICATION_SENT = "NOTIFICATION_SENT";
function NotifyNewBooking() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 47; // Number of rows to process
// Fetch the range of cells A2:N48
var dataRange = sheet.getRange(startRow, 1, numRows, 17)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = "natalya@berdyanskaya56.ru";
var subject = "Строка № " + row + ": " + "новое бронирование";
var message = "Уведомление о новом бронировании!" + "\n" + "\nДанные нового бронирования:" + "\n" + "\nЗаезд: " + row[0] + "\nВыезд: " + row[1] + "\nНомер: " + "«" + row[2] + "»" + "\nТип размещения: " + row[3] + "\nЦена за ночь: " + row[4] + " руб." + "\nВнесённый депозит: " + row[8] + " руб." + "\n" + "\nИмя и фамилия гостя: " + row[5] + "\nМобильный телефон: " + row[6] + "\nЭлектронная почта: " + row[7] + "\n" + "\nПримечание: " + row[11] + "\n" + "\n* Просмотреть список всех бронирований можно тут: " + "www.bitly.com/b56guests";
var notificationSent = row[17]; // The column where notification status is put
if (notificationSent != NOTIFICATION_SENT) { // Prevents sending duplicates
MailApp.sendEmail(emailAddress, subject, message, {name: "Мини-гостиница Бердянская 56", replyTo: "ruslan@berdyanskaya56.ru"});
sheet.getRange(startRow + i, 17).setValue(NOTIFICATION_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
此处的目标是仅在列 row[17]
中的单元格不包含 NOTIFICATION_SENT
时发送电子邮件 . 请给我一些强有力的建议如何解决它 .
非常感谢你提前!
1 回答
问题出在这一行:
var notificationSent = row[17];
尝试通过记录值
Logger.log(row[17])
和Logger.log(row[16])
来调试代码,您将在行[17]中获得undefined
并在行[16]中获得NOTIFICATION_SENT
.Why? 因为范围 . getValues返回一个二维数组,数组索引从0开始 .
它也在documentation中指定 .
因此,虽然行很好,因为你已经在for循环中将i初始化为0 .
获取列时,应从索引中减去1 .
因为如果你有17列,那么数组中的第一个值将从索引0开始到16 .