Send a single email containing all matching rows based on date criteria

Recently, I attempted to create a script that would email the details of all upcoming arrivals for both today and tomorrow in a single message. However, my script ended up sending separate emails for each booking detail, regardless of whether it matched the conditional expressions for today or tomorrow.

As a novice in scripting with Google Apps Script, I tried searching for solutions to combine and filter array values for emailing but didn't find a clear path forward.

Instead of the current setup, I aim to (1) check if the date matches today's and/or tomorrow's dates, then (2) gather all relevant booking details, and finally (3) sort them into today-arrivals and tomorrow-arrivals sections within a single email message.

Below is the source code I have been working on:

/* Sending notifications on upcoming arrivals for today and tomorrow. */
function NotifyUpcomingArrivals() {
    // Code implementation
}

I am seeking guidance on how to modify this code to achieve the desired outcome. Any help through code examples or recommended readings would be greatly appreciated by the community.

Update 1: After incorporating some minor adjustments based on suggestions, the script now functions flawlessly. Thank you for the assistance!

javascriptarraysgoogle-apps-scriptgoogle-sheetsgoogle-drive-api

Answer №1

Declare your message as a global variable outside the for loop and call the sendEmail function from outside the loop. The data is grouped by date, which is why results for today and tomorrow are sent separately.

 /* Sending notifications about upcoming arrivals for today and tomorrow. */
    function NotifyUpcomingArrivals() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = SpreadsheetApp.getActiveSheet();
        var startRow = 2; // Starting row for processing bookings.
        var numRows = sheet.getLastRow()-1; // Number of booking rows to process.
    var message = undefined, subject =undefined;

    var recipientEmail = "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0d636c796c61746c4d6f687f69746c637e666c746c383b237f78">[email protected]</a>";
 var bookingsListLink = "http://bitly.com/b56bookingslist";
            var senderName = "Ruslan Seletsky's Robot (Mini-Hotel Berdyanskaya 56)";
            var replyTo = "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0577707669646b45676077617c646b766e647c6430332b7770">[email protected]</a>";
        var dataRange = sheet.getRange(startRow, 1, numRows, 18); 
        var data = dataRange.getValues(); 
        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 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");

            // calculations and formatting omitted for brevity
            
                if (formattedCheckInDate == formattedTodaysDate && reviewDummy !== "–") { 
                var todaysArrivalsMessage = "Bookings for" + " today (" + formattedTodaysDate + ")" + "\n" + "\n" + contactFullName + " (row # " + bookingNumber + ")";
                Logger.log("Notification sent for today's upcoming arrival for booking #" + bookingNumber + " (" + contactFullName + ")");
                }

                if (formattedCheckInDate == formattedTomorrowsDate && reviewDummy !== "–") { 
                var tomorrowsArrivalsMessage = "\n" + "\nBookings for" + "tomorrow (" + formattedTomorrowsDate + ")" + "\n" + "\n" + contactFullName + " (row # " + bookingNumber + ")";
                Logger.log("Notification sent for tomorrow's upcoming arrival for booking #" + bookingNumber + " (" + contactFullName + ")");
                }
                message += todaysArrivalsMessage + tomorrowsArrivalsMessage;
           }
 MailApp.sendEmail(recipientEmail, subject, message, {name: senderName, replyTo: replyTo});
                SpreadsheetApp.flush(); 
    }

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

The JSON response from Ajax is not coming back as anticipated

My attempts to make a basic ajax call are failing; var getPrevious = function(){ console.log('ajaxing'); $.ajax({ type: 'GET', dataType: "json", url: 'http://'+DOMAIN+'/previous', ...

javascriptjqueryajax

Cease the firing of ion-change until the values have been successfully loaded from storage

My settings page contains multiple ion-toggle's. There's an onChange method to update local storage when toggled. Standard procedure involves checking existing values in storage on page load and mapping them to the toggles using ngModel. <tab ...

javascriptangularionic-frameworkionic4

Javascript - Verify the presence of two elements within an array

How can I ensure that both ages 10 and 18 exist in a JavaScript array of ages, rather than just one? var ages = [3, 10, 18, 20]; ages.filter(age => age === 10 || age === 18); // returns 10 and 18 ages.filter(age => age === 10 && age === 18); ...

javascriptarrays

Tips for creating an Array in Pascal

One way to initialize an array in Java is by using the following code: data[10] = {10,20,30,40,50,60,71,80,90,91}; What is the equivalent method for initializing an array in Pascal? ...

arraysinitializationpascal

Is the validity of the expression !args.value || args.value.length true?

After analyzing this segment of code, I noticed an interesting expression: !args.value || args.value.length For instance, consider the following scenario: let v = {}; console.log(!v.value); //outputs true console.log(v.value); //outputs undefined con ...

javascripttypescript

What is the proper syntax for implementing the $q.all method?

During my interview, I was asked the following question. Can you identify the correct syntax for using the $q.all method? • $q.all([promise1(), promise2]).then((values) => { … }); • $q.all("promise1", "promise2").then((values) => ...

javascriptangularjs

Issue with ng-maxlength directive

In my Angular 4 Application, I am attempting to implement validation for form inputs. Specifically, I want to restrict inputs with numbers to not accept more than 4 digits. If the input contains more than 4 digits, the form should be marked as invalid. I a ...

javascripthtmlangulartypescriptangular-validation

What could be causing Laravel to fail to show the blade template?

I've been learning about Laravel's websocket and following a tutorial on it. I tried to implement the code exactly as shown in the tutorial, but I'm running into some issues. Here is the code for the controller: class CommentController exte ...

javascriptjsonlaravelwebsocketresponse

How can I ensure that the height of my dropdown menu covers the entire screen without being limited by the page height

I'm trying to adjust a dropdown menu so that it fits perfectly within the screen size, covering the entire height without showing any content beneath or below it. Currently, the menu covers the screen on some pages but scrolls and appears too large du ...

javascripthtmlcss

Creating a dynamic list with items and hyperlinks through the use of JavaScript

I have a JavaScript function that successfully adds list items to an HTML list. I'm trying to figure out how to dynamically add a link with each item. Here's the code I currently have: $.ajax({ url: uri1 + "?region=" + regionForSearch, m ...

javascriptjqueryhtml

The address of a pointer to an integer array remains unchanged when it is dereferenced

Here is the code snippet I am analyzing: #include <iostream> using namespace std; int main() { int g[] = {9,8}; int (*j)[2] = &g; cout << "*(j):" << *(j) << endl; cout << "j:" << j << endl; ...

c++arrayspointersmemory-addressdereference

Can we send a dynamic form using AJAX?

I am seeking a method to submit a page filled with quantities to another page and then retrieve their values. Here is my setup: <input type="number" name="item1" /> <input type="number" name="item2" /> <input type="number" name="item3" /> ...

javascriptphpajax

Arranging div containers with nested content

Within my application, I am dynamically displaying images side by side to users. Users have the ability to assign points to these images, and I would like to give them the option to sort the images based on points, with the highest points displayed first i ...

javascriptjquerysortinghtml

Using Java recursion to find and return the object with the maximum element value

My task for a class project involves finding and returning an object with the maximum weight in an array using recursion. Despite my efforts, I can't seem to get the correct output. The issue lies in the method returning the Packet object at the speci ...

javaarraysobjectrecursion

THREE.IcosahedronGeometry does not contain a list of vertices

I recently took the Advanced Creative Coding with WebGL & Shaders course taught by Matt DesLauriersd. During one of his videos, he demonstrated how to extract the vertices array for an icosahedronGeometry object using the following code: const geometry = ...

javascriptthree.js

Determine the quantity of elements in an array by using pointers within another array

I'm attempting to determine the number of elements in either array a or b by utilizing their reference in array x. Here is my progress so far: int a[]={1,2,3,4,5}; int b[]={0,2,4,5}; int* x[]={a,b}; //The following calculations function as expected ...

c++arrayssizeof

Which type of element does Youtube utilize for the videos on its own domain - <iframe> or <video>?

Do they have a different method for incorporating their videos? My goal is to utilize the playbackRate property on a non-embedded YouTube video for a Chrome extension. ...

javascriptvideoiframehtml5-video

Despite implementing ECMAScript 6, Typescript is still unable to locate the name 'Promise'

I'm currently working on developing a node.js application in Visual Studio 2015, but I keep encountering an issue: TS2304 Cannot find the name 'Promise' In my project settings, I have ECMAScript 6 set up as the build system and ES 2015 a ...

javascripttypescriptvisual-studio-2015es6-promise

Unfamiliar function detected in the updated Vue Composition API

I am currently in the process of converting a basic SFC to utilize the new Vue CompositionAPI. The original code functions perfectly: export default { data() { return { miniState: true } }, methods: { setMiniState(state) { if ...

javascriptvue.jsvue-componentquasar-frameworkvue-composition-api

Having trouble viewing the page of a new package you published on the NPM Website?

Today, I officially released an NPM package called jhp-serve. It can be easily installed using npm install or run with npx. You can even find it in the search results here: https://www.npmjs.com/search?q=jhp. However, when attempting to view its page by cl ...

javascriptnode.jsnpm