如何从GAS中的循环返回多个值

Today I once again faced with a difficulty of creating a mechanism putting all rows (in my case bookings) that match a condition (in my case check-in date) in a single email.

今天我再一次面临着创建一个机制的困难,这个机制将所有行(在我的情况下都是预订)与一个条件(在我的情况下是签入日期)匹配在一封电子邮件中。

The problem is that if there is more than one matching row (e.g., when multiple check-ins match current date) than the script puts the values of one and first-found condition-matching row into email. Up to now I did a little of research (for further reference please check: Email All Rows in Single Email If Dates Match) and succeeded in building a source code which achieves a goal in a case only if there is only one condition-matching row, not at least two of them.

问题在于,如果存在多个匹配行(例如,当多个签入匹配当前日期时),则脚本将一个和首先找到的条件匹配行的值放入电子邮件中。到目前为止,我做了一些研究(如需进一步参考,请检查:单个电子邮件中的所有行,如果日期匹配)并成功构建源代码,只有在只有一个条件匹配行的情况下才能达到目标,至少两个。

Here is my source code. As a part, it checks whether formattedTodaysDate matches formattedCheckInDate (formattedCheckInDate == formattedTodaysDate) and then collects values of these condition-matching rows.

这是我的源代码。作为一个部分,它检查formattedTodaysDate是否匹配formattedCheckInDate(formattedCheckInDate == formattedTodaysDate),然后收集这些条件匹配行的值。

It fails to copy these values into email message if the number of condition-meet rows is greater than 1:

如果条件满足行的数量大于1,则无法将这些值复制到电子邮件中:

function NotifyChambermaidOfUpcomingArrivalsAndDepartures() {
    var ui = SpreadsheetApp.getUi(); // Используется только для тестирования.
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // Строка с первым бронированием для начала обработки.
    var numRows = sheet.getLastRow()-1; // Количество строк с бронированиями для обработки.
    var dataRange = sheet.getRange(startRow, 1, numRows, 18); // Охват столбцов для обработки ячеек, в данном случае с 1-го по 18-ый.
    var data = dataRange.getValues(); // Получить значение ячеек в каждой ячейке каждой строке в рамках столбцов, охваченных верхним кодом.
    /* Здесь установка параметров отправителя. */
    var message = undefined; 
    var subject = undefined;
    var recipientEmail = "[e-mail]"; // Электропочта горничной.
    var senderName = "name";
    var replyTo = "[e-mail]";
    for (var i = 0; i < data.length; ++i) {
        var row = data[i];
        var bookingNumber = [i+2];
        var todaysDate = new Date(); // Сегодняшняя дата.
        var tomorrowsDate = new Date(todaysDate.getTime()+(1*24*3600*1000)); // Завтрашняя дата.
        var dayAfterTomorrowsDate = new Date(todaysDate.getTime()+(2*24*3600*1000)); // Послезавтрашняя дата.
        var checkInDate = new Date(row[0]);
        var checkOutDate = new Date(row[1]);
        var checkInTime = "14:00";
        var checkOutTime = "12:00";
        var formattedTodaysDate = Utilities.formatDate(todaysDate, "GMT+0300", "dd.MM.yyyy");
        var formattedTomorrowsDate = Utilities.formatDate(tomorrowsDate, "GMT+0300", "dd.MM.yyyy");
        var formattedDayAfterTomorrowsDate = Utilities.formatDate(dayAfterTomorrowsDate, "GMT+0300", "dd.MM.yyyy");
        var formattedCheckInDate = Utilities.formatDate(checkInDate, "GMT+0300", "dd.MM.yyyy");
        var formattedCheckOutDate = Utilities.formatDate(checkOutDate, "GMT+0300", "dd.MM.yyyy");
        var fullCheckInDate = Utilities.formatDate(checkInDate, "GMT+0300", "dd.MM.yyyy") + " в " + checkInTime;
        var fullCheckOutDate = Utilities.formatDate(checkOutDate, "GMT+0300", "dd.MM.yyyy") + " в " + checkOutTime;
        var roomType = "«" + row[2] + "»";
        var numberOfGuests = row[3];
        var costPerNight = row[4];
        var prepaymentCost = row[8];
        var formattedCostPerNight = costPerNight + " руб. за номер/ночь";
        var formattedPrepaymentCost = prepaymentCost + " руб. предоплата";
        var contactFullName = row[5];
        var contactPhone = row[6];
        var contactEmail = row[7];
        var bookingNotes = row[11];
        var reviewDummy = row[13];
        /* Здесь расчёт промежуточных переменных. */
        var numberOfNights = Math.round(Math.abs((checkOutDate.getTime() - checkInDate.getTime())/(24*60*60*1000)));
        var unpaidCost = (costPerNight*numberOfNights)-prepaymentCost;
        var formattedNumberOfNights = numberOfNights + " ночей"; // Количество ночей проживания.
        var formattedUnpaidCost = unpaidCost + " руб. к оплате"; // Сумма, которую необходимо оплатить гостю по приезде.
            /* Сегодняшние заезды и выезды. */
            var todaysSection = "‖ Сегодня, " + formattedTodaysDate + ":";
            /* Сегодняшние заезды. */
            if (formattedCheckInDate == formattedTodaysDate && reviewDummy !== "–") { // Условное выражение, позволяющее отобрать бронирования с заездами сегодня, но кроме тех, которые уже были отменены гостями.
            var todaysArrivalsSection = "• Заезды:";
            var todaysArrivalsBookings = roomType + " в " + checkInTime + " (бронирование № " + bookingNumber + ")" + "\nТип размещения: " + numberOfGuests + "\nВыезд: " + fullCheckOutDate + " (" + formattedNumberOfNights + ")" + "\nГость: " + contactFullName + "\nМобильный телефон: " + contactPhone;
            var todaysArrivalsMessage = todaysArrivalsSection + "\n" + "\n" + todaysArrivalsBookings; 
            Logger.log("Сформирован набор бронирований для горничной с заездами сегодня: № " + bookingNumber + " (" + contactFullName + ")" + " и отправлен на " + recipientEmail + ".");
            }
            /* Сегодняшние выезды. */
            if (formattedCheckOutDate == formattedTodaysDate && reviewDummy !== "–") { // Условное выражение, позволяющее отобрать бронирования с выездами сегодня, но кроме тех, которые уже были отменены гостями.
            var todaysDeparturesSection = "◦ Выезды:";
            var todaysDeparturesBookings = roomType + " в " + checkOutTime + " (бронирование № " + bookingNumber + ")" + "\nГость: " + contactFullName + "\nМобильный телефон: " + contactPhone;
            var todaysDeparturesMessage = todaysDeparturesSection + "\n" + "\n" + todaysDeparturesBookings; 
            Logger.log("Сформирован набор бронирований для горничной с выездами сегодня: № " + bookingNumber + " (" + contactFullName + ")" + " и отправлен на " + recipientEmail + ".");
            }
            /* Завтрашние заезды и выезды. */
            var tomorrowsSection = "‖ Завтра, " + formattedTomorrowsDate + ":";
            /* Завтрашние заезды. */
            if (formattedCheckInDate == formattedTomorrowsDate && reviewDummy !== "–") { // Условное выражение, позволяющее отобрать бронирования с заездами завтра, но кроме тех, которые уже были отменены гостями.
            var tomorrowsArrivalsSection = "• Заезды:";
            var tomorrowsArrivalsBookings = roomType + " в " + checkInTime + " (бронирование № " + bookingNumber + ")" + "\nТип размещения: " + numberOfGuests + "\nВыезд: " + fullCheckOutDate + " (" + formattedNumberOfNights + ")" + "\nГость: " + contactFullName + "\nМобильный телефон: " + contactPhone;
            var tomorrowsArrivalsMessage = tomorrowsArrivalsSection + "\n" + "\n" + tomorrowsArrivalsBookings; 
            Logger.log("Сформирован набор бронирований для горничной с заездами завтра: № " + bookingNumber + " (" + contactFullName + ")" + " и отправлен на " + recipientEmail + ".");
            }
            /* Завтрашние выезды. */
            if (formattedCheckOutDate == formattedTomorrowsDate && reviewDummy !== "–") { // Условное выражение, позволяющее отобрать бронирования с выездами завтра, но кроме тех, которые уже были отменены гостями.
            var tomorrowsDeparturesSection = "◦ Выезды:";
            var tomorrowsDeparturesBookings = roomType + " в " + checkOutTime + " (бронирование № " + bookingNumber + ")" + "\nГость: " + contactFullName + "\nМобильный телефон: " + contactPhone;
            var tomorrowsDeparturesMessage = tomorrowsDeparturesSection + "\n" + "\n" + tomorrowsDeparturesBookings; 
            Logger.log("Сформирован набор бронирований для горничной с выездами завтра: № " + bookingNumber + " (" + contactFullName + ")" + " и отправлен на " + recipientEmail + ".");
            }
            /* Послезавтрашние заезды и выезды. */
            var dayAfterTomorrowsSection = "‖ Послезавтра, " + formattedDayAfterTomorrowsDate + ":";
            /* Послезавтрашние заезды. */
            if (formattedCheckInDate == formattedDayAfterTomorrowsDate && reviewDummy !== "–") { // Условное выражение, позволяющее отобрать бронирования с заездами послезавтра, но кроме тех, которые уже были отменены гостями.
            var dayAfterTomorrowsArrivalsSection = "• Заезды:";
            var dayAfterTomorrowsArrivalsBookings = roomType + " в " + checkInTime + " (бронирование № " + bookingNumber + ")" + "\nТип размещения: " + numberOfGuests + "\nВыезд: " + fullCheckOutDate + " (" + formattedNumberOfNights + ")" + "\nГость: " + contactFullName + "\nМобильный телефон: " + contactPhone;
            var dayAfterTomorrowsArrivalsMessage = dayAfterTomorrowsArrivalsSection + "\n" + "\n" + dayAfterTomorrowsArrivalsBookings; 
            Logger.log("Сформирован набор бронирований для горничной с заездами послезавтра: № " + bookingNumber + " (" + contactFullName + ")" + " и отправлен на " + recipientEmail + ".");
            }
            /* Послезавтрашние выезды. */
            if (formattedCheckOutDate == formattedDayAfterTomorrowsDate && reviewDummy !== "–") { // Условное выражение, позволяющее отобрать бронирования с выездами послезавтра, но кроме тех, которые уже были отменены гостями.
            var dayAfterTomorrowsDeparturesSection = "◦ Выезды:";
            var dayAfterTomorrowsDeparturesBookings = roomType + " в " + checkOutTime + " (бронирование № " + bookingNumber + ")" + "\nГость: " + contactFullName + "\nМобильный телефон: " + contactPhone;
            var dayAfterTomorrowsDeparturesMessage = dayAfterTomorrowsDeparturesSection + "\n" + "\n" + dayAfterTomorrowsDeparturesBookings; 
            Logger.log("Сформирован набор бронирований для горничной с выездами послезавтра: № " + bookingNumber + " (" + contactFullName + ")" + " и отправлен на " + recipientEmail + ".");
            }
            /* Сегодняшние заезды и выезды. */
            /* Если сегодняшние заезды не найдены. */
            if (todaysArrivalsMessage == undefined) {
            todaysArrivalsMessage = "• Заезды:" + "\n" + "\nБронирования с заездами сегодня не найдены. Возможно, кто-то ещё успеет сделать бронирование в течении дня.";
            }
            /* Если сегодняшние выезды не найдены. */
            if (todaysDeparturesMessage == undefined) {
            todaysDeparturesMessage = "◦ Выезды:" + "\n" + "\nБронирования с выездами сегодня не найдены.";
            }
            /* Завтрашние заезды и выезды. */
            /* Если завтрашние заезды не найдены. */
            if (tomorrowsArrivalsMessage == undefined) {
            tomorrowsArrivalsMessage = "• Заезды:" + "\n" + "\nБронирования с заездами завтра не найдены. Возможно, кто-то ещё успеет сделать бронирование до завтрашнего дня.";
            }
            /* Если завтрашние выезды не найдены. */
            if (tomorrowsDeparturesMessage == undefined) {
            tomorrowsDeparturesMessage = "◦ Выезды:" + "\n" + "\nБронирования с выездами завтра не найдены.";
            }
            /* Послезавтрашние заезды и выезды. */
            /* Если послезавтрашние заезды не найдены. */
            if (dayAfterTomorrowsArrivalsMessage == undefined) {
            dayAfterTomorrowsArrivalsMessage = "• Заезды:" + "\n" + "\nБронирования с заездами послезавтра не найдены. Возможно, кто-то ещё успеет сделать бронирование до послезавтрашнего дня.";
            }
            /* Если послезавтрашние выезды не найдены. */
            if (dayAfterTomorrowsDeparturesMessage == undefined) {
            dayAfterTomorrowsDeparturesMessage = "◦ Выезды:" + "\n" + "\nБронирования с выездами послезавтра не найдены.";
            }
            var subject = "Сводка заездов и выездов гостей" + " сегодня (" + formattedTodaysDate + ")," + " завтра (" + formattedTomorrowsDate + ")" + " и послезавтра (" + formattedDayAfterTomorrowsDate + ")";
            var messageBeginning = "Доброе утро," + "\n" + "\nВ этом письме собрана информация о заездах и выездах гостей сегодня, завтра и послезавтра.";
            var messageSignature = "Пожалуйста, сделайте всё возможное, чтобы номера были готовы к заезду гостей как сегодня, так завтра и послезавтра. После выезда гостей номера также должны быть подготовлены к заезду новых постояльцев." + "\n" + "\nУспешного рабочего дня!";
            var message = messageBeginning + "\n" + "\n" + todaysSection + "\n" + "\n" + todaysArrivalsMessage + "\n" + "\n" + todaysDeparturesMessage + "\n" + "\n" + "\n" + tomorrowsSection + "\n" + "\n" + tomorrowsArrivalsMessage + "\n" + "\n" + tomorrowsDeparturesMessage + "\n" + "\n" + "\n" + dayAfterTomorrowsSection + "\n" + "\n" + dayAfterTomorrowsArrivalsMessage + "\n" + "\n" + dayAfterTomorrowsDeparturesMessage + "\n" + "\n" + messageSignature;
            }
    ui.alert("Тестирования механизма формирования и отправки уведомлений о предстоящих заездах", "Тема письма:" + "\n" + "\n" + subject + "\n" + "\nТекст письма:" + "\n" + "\n" + message, ui.ButtonSet.OK); // Используется только для тестирования.
    // MailApp.sendEmail(recipientEmail, subject, message, {name: senderName, replyTo: replyTo});
    Logger.log("Горничной отправлено уведомление с предстоящими заездами и выездами сегодня, завтра и послезавтра (" + formattedTodaysDate + ", " + formattedTomorrowsDate + " и " + formattedDayAfterTomorrowsDate + ") на " + recipientEmail + ".");
    SpreadsheetApp.flush(); // Показываем пользователю что изменения вступают в силу.
}

How can I fix it?

我该如何解决?

Solving-oriented help and knowledge-clarification comments are greatly appreciated and rewarded by community members.

面向求解的帮助和知识澄清评论受到社区成员的高度赞赏和奖励。

1 个解决方案

#1

After much testing and modification, I've built something you should be able to use. The code includes notes so that you understand each step. Be very careful when inserting your own values as the scope of your variables is crucial.

经过大量的测试和修改,我已经构建了一些你应该可以使用的东西。代码包含注释,以便您了解每个步骤。插入自己的值时要非常小心,因为变量的范围至关重要。

function EmailApplication() {
  var TodayEmailTemplate = HtmlService.createHtmlOutputFromFile("Today Email Template"); //These are defined outside of the loop so that the changes are persistent at the end of the loop
  var TomorrowEmailTemplate = HtmlService.createHtmlOutputFromFile("Tomorrow Email Template");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow(); //Ensure that there are no functions that fill your sheet with invisible data i.e =QUERY(); this will run the code infinitely
  var checkColumn = sheet.getLastColumn(); //The spreadsheet's last column (data-filled or otherwise) is a blank column called Check. No rows may exist right of this column
  var checkRange = sheet.getRange(2, checkColumn, (lastRow - 1), 1); //Entire column down to the last row with data
  var red = "#ff0404"; //Colors defined; any colors can be used to your preference. These are in HEX form to ensure preferred hue
  var yellow = "#ffec0a";
  var green = "#3bec3b";
  var check = checkRange.getBackgrounds(); //Array of background values used to visually display code progress/ status
  function EmailBuilder() {
    for (var i = 0; i < check.length; i++) { //Loop through the 2D array of background values
      if (check[i] == green) {
        continue; //Skip all completed rows; this is clearer further on
      } else {
        var statusCell = sheet.getRange((i+2), checkColumn, 1, 1); //Check cell in the currently processed row
        var dataRow = sheet.getRange((i+2), 1, 1, (checkColumn - 2)); //Row of values
        var data = dataRow.getValues(); //Array to allow indexing to define values; I don't call this 'row' as that implies I'm iterating through rows; this is also 2D array
        var name = data[0][1]; //Column 2 was Name column
        var room = data[0][2]; //Column 3 was Room Number column
        var rawTodaysDate = new Date();
        var rawTomorrowsDate = new Date(rawTodaysDate.getTime()+(1*24*3600*1000)); //I call unformatted dates 'raw' so that the formatted version can be the simple name [cleaner/clearer]
        var rawDayAfterTomorrowsDate = new Date(rawTodaysDate.getTime()+(2*24*3600*1000));
        var rawCheckInDate = sheet.getRange((i+2), 1, 1, 1).getValue(); //On my dummy sheet it was column 1
        var rawCheckOutDate = sheet.getRange((i+2), 4, 1, 1).getValue(); //On my dummy sheet it was column 4
        var todaysDate = Utilities.formatDate(rawTodaysDate, "GMT+0300", "dd.MM.yyyy"); 
        var tomorrowsDate = Utilities.formatDate(rawTomorrowsDate, "GMT+0300", "dd.MM.yyyy");
        var dayAfterTomorrowsDate = Utilities.formatDate(rawDayAfterTomorrowsDate, "GMT+0300", "dd.MM.yyyy");
        var checkInDate = Utilities.formatDate(rawCheckInDate, "GMT+0300", "dd.MM.yyyy");
        var checkOutDate = Utilities.formatDate(rawCheckOutDate, "GMT+0300", "dd.MM.yyyy");
        if (checkInDate == todaysDate) {
          var emailAppend = TodayEmailTemplate.append("Room " + room + " for " + name + "; Check-Out Date: " + checkOutDate + '<br>'); //You can fill with any HTML/ Javascript variable combo you want
          statusCell.setBackground(yellow); //Denote that today is ready to send
        }
        if (checkInDate == tomorrowsDate) {
          var emailAppend = TomorrowEmailTemplate.append("Room " + room + " for " + name + "; Check-Out Date: " + checkOutDate + '<br>'); //You can fill with any HTML/ Javascript variable combo you want
          statusCell.setBackground(red); //Denote that tomorrow is ready to send
        }
      }
    }
    TodayEmailTemplate.append('</body></html>'); //Finish the HTML to ensure proper structure
    TomorrowEmailTemplate.append('</body></html>');
    var TodayEmail = TodayEmailTemplate.getContent(); //Sendable output
    var TomorrowEmail = TomorrowEmailTemplate.getContent();
    function EmailSender() {
      function TodayEmailSend() { //One email with the constructed email body for today
        var emailSubject = "Today's To-Do List";
        MailApp.sendEmail({
          to: "[e-mail]",
          subject: emailSubject,
          htmlBody: TodayEmail,
        });
      }
      function TomorrowEmailSend() { //One email with the constructed email body for tomorrow
        var emailSubject = "Tomorrow's Prep";
        MailApp.sendEmail({
          to: "[e-mail]",
          subject: emailSubject,
          htmlBody: TomorrowEmail,
        });
      }
      function StatusLoop() { //Once emails are sent, change status's to denote that the progress has changed
        var check2 = checkRange.getBackgrounds();//Same loop, same array, different iterator to keep variables clean
        for (var j = 0; j < check2.length; j++) { 
          if (check2[j] != yellow) { //Ignores green, red, and blank (white)
            continue; //You could choose to change red as well, but the EmailBuilder will change it automatically tomorrow
          } else {
            var statusCell = sheet.getRange((j+2), checkColumn, 1, 1);
            statusCell.setBackground(green); //Change all yellow to green so that they are not used in tomorrow's EmailBuilder
          }
        }
      } //Simple calling of all the functions in the proper order so that the process is regimented and we can insure that each step is complete before moving on
      TodayEmailSend();
      TomorrowEmailSend();
      StatusLoop();
    }
    EmailSender();
  }
  EmailBuilder();
}

It's worth noting that your coloration can be anything you want. In fact, you do not even need the color tracking at all, it's merely a simple and effective way to track progress, catch errors and have an extremely straightforward conditional statement to check. Your conditional statements can be anything, and do not have to change the spreadsheet itself.

值得注意的是,您的颜色可以是您想要的任何颜色。事实上,你根本不需要颜色跟踪,它只是一种简单有效的方法来跟踪进度,捕获错误并有一个非常简单的条件语句来检查。条件语句可以是任何内容,也不必更改电子表格本身。

Also, I have included screenshots below of what the templates looked like for the emails and what the spreadsheet looked like pre, during and post function operation.

此外,我还在屏幕截图中列出了电子邮件模板的外观以及电子表格在功能操作之前,期间和之后的外观。

You'll notice that the HTML is missing </body> & </html>. These are added on part way through the code.

您会注意到HTML缺失 & 。这些是通过代码部分添加的。

It makes the status cell yellow if it's today, red if it's tomorrow, keeps it blank if it's neither. Then it makes it green after the email is sent.

它使状态单元格为黄色(如果是今天),如果是明天则为红色,如果不是,则将其保持为空白。然后在发送电子邮件后将其设为绿色。