0

I'm working on a small project, using Google Sheets and Google Script to generate a tennis scheduling system. Basically the user enters their info on a Google Form, which is passed to Google Sheets where the script will generate or append the user's input to a new or existing date slot Sheet. I'm trying to figure out what I'm doing wrong with my date validation code here. The idea is that 'if user's entered date is older than today's date, write down an error message in the corresponding Form Responses row and do not process the entry. However, the date comparison is not working as I expected. Here is the code snippet that I'm using to accomplish that, with Boolean tests to show what I'm getting when I do said date comparison:

  var now = Utilities.formatDate(new Date(),"GMT+8","MMM/dd/yyyy");
  /** Retrieve player name, timeslot, court slot from latest form entry */
  var lastRow = sheet.getLastRow();
  var lastRowRange = sheet.getRange(lastRow, 1, 1, 6);
  var lastRowValues = lastRowRange.getValues();
  /** current date format - MMM/dd/yyyy */
  var dateStr = lastRowValues[0][2];
  var dateSlot2 = sheet.getRange(lastRow,3);
  var dateSlot = Utilities.formatDate(dateStr, "GMT+8", "MMM/dd/yyyy");
  dateSlot2.setValue(new Date()).setNumberFormat("MMM/dd/yyyy");
  var timeSlot = lastRowValues[0][3];
  var courtSlot = lastRowValues[0][4];
  /** test cases */
  var dateTest = (dateSlot >= now);   //always returns false
  var dateTest2 = (dateSlot2 >= now); //always returns true

This is my first time with Google Script, so any advice is greatly appreciated!

1
0

It looks that you are new to Google Apps Script and to JavaScript.

First you should be aware that Google Apps Script / JavaScript do not handle dates the same way that Google Sheets / spreadsheets do. Utilities.formatDate(...) returns a string. While in JavaScript it's possible to use > and < to compare strings is very unlikely that this behaviour is what you are looking for.

The most common practice to compare dates in Google Apps Script / JavaScript is to get the corresponding Date objects, then use Date.prototype.getTime() or another similar method to convert them into a serial number (milliseconds) or something similar. For more details please checkout this Q/A: Basic date manipulation in Google script

Related

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.