0

Dates in Google script are driving me CRAZY!

I have looked all over the internet for something that will allow me to use dates in my google sheet cells from entries that may be dates or text (For example in the script below, the "modifiedDate" cell may contain either a date, blank or ext). I have tried to use 'getValues" and as you can see below, even "getDisplayValues" and I am not sure if EITHER is working!

I need to be able to determine if the value is a date or text, and if date, do something based on that date (in the example below, if the date is in 2 days time, send an email reminder).

The field in question may have: Display VALUE: "Next Appointment", "Wednesday, 30 March 2019 14:00", or be blank Values in formula bar: "Next Appointment", "20/03/2019 14:00:00", or also be blank (Date formats are in dd/mm/yyy with Australian Melbourne Timezone in my spreadsheet setting)

Here is my code thus far.. PLEASE LET ME KNOW WHER IT IS WRONG!

function studentAppointmentReminders() {
  var sS = SpreadsheetApp.getActiveSpreadsheet();
  var sheetURL = sS.getUrl();
  var masterSheet = "Registrations";

 var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
// need to know how to use this so I do not send too many emails and loose my students who have not been sent one..

  // Seperate Sheet details
  var currentSheet = "";
  var currentSheetName = "";
  var courseProvider = Some Institute";
  var newStatus = "";

  // Recurse through all sheets...
  var sheets = sS.getSheets();
  var sheetNames = [];
// Recurse through each sheet
  for(var s=0;s<sheets.length;s++) {
//    sheetNames.push([sheets[s].getName()]);
    currentSheetName = sheets[s].getName();
    //  Set this sheets name as the current sheet
    currentSheet = sS.getSheetByName([sheets[s].getName()]);

    // Make sure the current sheet is NOT the MasterSheet
  if ( masterSheet == currentSheetName ) {
      continue; // Skip this sheet
      }  // Otherwise carry on
        // Now get all records and students data from this sheet...
  var range = currentSheet.getDataRange();

// some cells have formulas, so use getDisplayValues to get the actual values and not formulas etc
  var values = range.getDisplayValues();  //  Gets Data stored NOT formulas
  // Check each student record in this sheet
//   for(var i=3;i<values.length;i++) { // Start after the third row
 for(var i=3;i<5;i++) { // Start after the third row
// changed for testing purposes

  var emailTo = "marty.lord@outlook.com";
//   var emailTo = values[i][4];

      // make sure dates are formatted to strings, and share the same timezone
    // utilities.formatDate(Date, Timzone, format)
  // for example: var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    var dateModified = values[i][11] ;
// Found the following regarding Java style date checking...
  if ( Object.prototype.toString.call(dateModified) !== "[object Date]" ) {
    dateModified = SpreadsheetApp.getActiveSheet().getRange(i+1,12).getValues();
  if ( Object.prototype.toString.call(dateModified) !== "[object Date]" ) {
    continue; // skip this record becuase it is NOT a date object
}
}
// the above SHOULD find the date if it is a display value or actual cell value right?

var modifiedDate = Utilities.formatDate (new Date (dateModified), SpreadsheetApp.getActiveSpreadsheet (). getSpreadsheetTimeZone (), "dd/mm/yyyy");
  if ( Object.prototype.toString.call(modifiedDate) !== "[object Date]" ) {
    continue; // skip this record becuase it is NOT a date object
}
    var dateToday = new Date ();
var todaysDate = Utilities.formatDate (new Date (dateToday), SpreadsheetApp.getActiveSpreadsheet (). getSpreadsheetTimeZone (), "dd/mm/yyyy");
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
    var MILLIS_PER_month = MILLIS_PER_DAY * 30; // Assumes 30 days in a month
    var MILLIS_PER_quarter = MILLIS_PER_DAY * 92; // assumes 2 months have 31 days, and 1 month has 30
    var MILLIS_PER_half = MILLIS_PER_DAY * 183;
    var MILLIS_PER_year = MILLIS_PER_DAY * 365; 

// If the script continues from this point, the appointment date is ok

// if the appointment is not two to three days away, omit it
    if ((todaysDate.getTime() - modifiedDate.getTime()) / MILLIS_PER_DAY > 3 && (todaysDate.getTime() - modifiedDate.getTime()) / MILLIS_PER_DAY < 2 ) { 
    continue; // Omit this Appointment reminder
}


// Script DOES NOT DO THE FOLLOWING!!

  // Sent Emails
// omitted subject, body etc (but I know that works via other scripts I am using that are NOT date reliant..

  MailApp.sendEmail({
    to: emailTo,
    subject: reportSubject,
      htmlBody: reportMessage + reportFooter
  }); // end of message contents

newStatus = "Pending Appointment";
        SpreadsheetApp.getActiveSheet().getRange(i, 15).setValue(newStatus);

} // (ML) Finished checking each row in the current sheet

} // (ML) Finished checking 's' all sheets

} // (ML) End of studentAppointments Function
  • There's not enough info here to form a good answer. If nothing else, share a copy of your sheet so we can see the script in action. If you're unsure what values you're actually working with, do some logging and debugging. – Joel Reid Apr 25 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.