Javascript / Google Apps Script for loop find match but continue

Question

I'm definitely not an expert and write in google apps script for simple workflows. One to help with repetitive tasks and two to continually try and learn programming..

I'm looking for assistant is how to approach the following setup. I'm not sure if thinking a "for" loop and maybe "if / else" is the way to approach.

I'll be reading data from a spreadsheet, specifically 4 rows, 4 columns. One of the columns will have dates. I'm comparing those dates to the current date to see if the date is 7 days prior. If so do something but then continue looking over the dates to see if another day is also within seven days and if so also do something.

For example: dates: 2/4/2014, 2/28/2014, 2/11/2014, 2/25/2014 today's date: 3/1/2014

I want to loop over the dates, see that 2/28/2014 is within seven days of today's date and send an email, then continue checking the dates, and see that 2/25/2014 is also within seven days and send another email.

I'm not looking for the answer I'm looking on how to approach this,

my thought (rough)

 for (i=0, i<dates.length; i++) {
   if (dates[i] <= (different between today's date and dates[i]) {
     send an email;
     continue looping;
   } else if {
     continue;
   }
  }

hopefully kinda of get the point. :)

EDIT: UPDATE** Here is what I've come up with so far... Below is the code and spreadsheet values for the dates.

Dates in spreadsheet C1:C4 (2/5/2014, 2/15/2014, 2/28/2014, 3/2/2014)

function calculateDays() {  
  //Gets the dates of schedule 
  var ss = SpreadsheetApp.getActiveSheet();
  var dataRange = ss.getRange("C1:C4");
  var values = dataRange.getValues();

  //Caculation for single day  
  var one_day = (1000 * 60 * 60 * 24);

  //For loop to get the values from spreadsheet
  for (i = 0; i < values.length; i++) {
    //Puts dataRange values into date format
    var scheduledDays = new Date(values[i]);
    var today = new Date();
    //grabs times from today and scheduled days in millseconds and subtracts
    var difference = scheduledDays.getTime() - today.getTime();
    //divides to get number of dates, rounds, and converts to positive integer
    var diffDay = Math.abs(Math.floor(difference / one_day));
    Logger.log(diffDay);
 }

What i'm unsure about next is how to loop over the variable diffDays to determine if the days is seven days prior to today's date. I know one is.. Could be i'm just tired but thoughts would be helpful.

I tried

for (j = 0; j < diffDays; j++) {
   Logger.Log(diffDays[j]); 
}

As a starting point to see what was logged but it showed blank... That's why i fell stuck.

UPDATED Based on Serge reply - New issue

Here is the updated code using Serge's suggested code. Adjusted a bit to help understand values that are being returned to me in the logger. Also adjust adding 7 days to today date to get future date for comparison. (see below code for additional comments)

function myFunction() {
  var data = SpreadsheetApp.getActiveSheet().getRange("C1:C4").getValues();  
  var week = (1000*60*60*24*7);  
  Logger.log('a week is ' + week);  
  var todayDaysTime = new Date().setHours(0,0,0,0);
  Logger.log('this is today= ' +todayDaysTime);
  var weekPlusSevenDays = week + todayDaysTime;
  Logger.log('this is todayDaysTime + a week ' +weekPlusSevenDays);

  for (i=0 ; i<data.length; i++) {
    Logger.log('Value of date in cell = '+data[i][0].getTime());
    if (data[i][0].getTime() == weekPlusSevenDays) {
      //send an email;
      Logger.log('mail sent');
    }
  }
}

I've updated the dates in the spreadsheet column C to be 3/9/2014, 3/10/2014, 3/11/2014, and 3/12/2014. So what i would expect is that is the code would run, see that value 3/11/2014 in the spreadsheet is equal to todaysDaysTime + week. I'm trying to take todays date and add seven days to it and see if it matches a date in the spreadsheet. So far its not matching. Here is a copy of the logger.log.

[14-03-04 09:55:09:770 PST] a week is 604800000

[14-03-04 09:55:09:770 PST] this is today= 1393920000000

[14-03-04 09:55:09:770 PST] this is todayDaysTime + a week 1394524800000

[14-03-04 09:55:09:770 PST] Value of date in cell = 1394352000000

[14-03-04 09:55:09:770 PST] Value of date in cell = 1394434800000

[14-03-04 09:55:09:770 PST] Value of date in cell = 1394521200000

[14-03-04 09:55:09:771 PST] Value of date in cell = 1394607600000

I'm not sure why its not matching to future date properly. I'm looking to have an exact match so that when the day in the spreadsheet matches today's date + a week (7 days), it sends an email.

Any thoughts on why its not matching?

It's unecessary to use continue since the for loop will go to the next iteration on it's own when it's body finishes to run. — plalx
– plalx, Commented Mar 1, 2014 at 6:32

Serge insas · Accepted Answer · 2014-03-04 23:20:29Z

1

JavaScript date is a representation of the number of milliseconds counted from January the first 1970. Knowing that, it is very simple to get a value of 7 days before : you just have to subtract from today's value the right number of milliseconds, in your example 7*24*3600*1000.

To remember that and to check it yourself just type this simple statement in the script editor and run any other function then look at the logger :

Logger.log('reference JS date = '+new Date(0));// new Date(0) is the reference date for Javascript date objects.

Then a simple comparison inside the loop will do everything you need. You don't even have to define an else statement since the loop will continue by default.

The other important point is that the data you read from a spreadsheet is a 2D array, i.e. a matrix of matrix so you will have to use 2 indexes to get the right data. In the example snippet below I assumed dates were in column 1 so I used index [0] for the second index, the one that selects the column equivalent.

function myFunction() {
  var data = SpreadsheetApp.getActiveSheet().getRange(1,1,4,4).getValues();
  var sevenDayBeforeToday = new Date().setHours(0,0,0,0)-(7*24*3600*1000);// value in milliseconds, parenthesis are not necessary but make the code more readable imho...
  Logger.log('sevenDayBeforeToday = '+sevenDayBeforeToday);
  for (i=0 ; i<data.length; i++) {
    Logger.log('Value of date in cell = '+data[i][0].getTime());
    if (data[i][0].getTime() <= sevenDayBeforeToday) {
      //send an email;
      Logger.log('mail sent');
    }
  }
}

EDIT : here is a version that uses string comparison for easier debug and same result.

(note that I checked on a test SS the code above and it worked as expected when timeZone settings are set the same as spreadsheet, see logger below :) enter image description here

function myFunction() {
  var data = SpreadsheetApp.getActiveSheet().getRange(1,1,4,4).getValues();
  var sevenDayAfterToday = new Date().setHours(0,0,0,0)+(7*24*3600*1000);// value in milliseconds, parenthesis are not necessary but make the code more readable imho...
  Logger.log('sevenDayAfterToday = '+Utilities.formatDate(new Date(sevenDayAfterToday),Session.getScriptTimeZone(),'MM-dd-yyyy'));
  for (i=0 ; i<data.length; i++) {
    Logger.log('Value of date in cell = '+Utilities.formatDate(new Date(data[i][0]),Session.getScriptTimeZone(),'MM-dd-yyyy'));
    if (Utilities.formatDate(new Date(data[i][0]),Session.getScriptTimeZone(),'MM-dd-yyyy') == Utilities.formatDate(new Date(sevenDayAfterToday),Session.getScriptTimeZone(),'MM-dd-yyyy')) {
      //send an email;
      Logger.log('mail sent');
    }
  }
}

enter image description here

edited Mar 4, 2014 at 23:20

answered Mar 1, 2014 at 8:41

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

jjones312 Over a year ago

I just posted an updated of what i've been working.. Thanks for reply.. gonna read over and test check this weekend.. I'm I over thinking it? seems like my code is quite a bit lengthier... :)

Serge insas Over a year ago

I've read your update, try to add a few logged values to check if you really get the cells you expect... see my answer about 2 d arrays.

jjones312 Over a year ago

Added logging to my code and was returning some odd numbers. Also seemed to be lots of extra work to get a similar result. I'm playing with the sample you gave, but i'm curious for '.setHours(0,0,0,0)"??

Serge insas Over a year ago

It is used to get the date at 0 hour, 0 minutes 0 sec and 0 millisecs

Serge insas Over a year ago

seems I misunderstood indeed :) My code sends a mail if the date is 7 days before (-7*24*3600*1000) if you want 7 day after, change - to + and you'll get the expected result. sorry for the misunderstanding .

|

Collectives™ on Stack Overflow

Javascript / Google Apps Script for loop find match but continue

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related