How to Return Multiple Values From a Loop in GAS

Question

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.

It fails to copy these values into email message if the number of condition-meet rows is greater than 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 = "[email protected]"; // Электропочта горничной.
    var senderName = "name";
    var replyTo = "[email protected]";
    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.

Answer 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: "[email protected]",
          subject: emailSubject,
          htmlBody: TodayEmail,
        });
      }
      function TomorrowEmailSend() { //One email with the constructed email body for tomorrow
        var emailSubject = "Tomorrow's Prep";
        MailApp.sendEmail({
          to: "[email protected]",
          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.

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.