13

I would like to define a custom function in Google Sheet. This function should do something very simple, yet I have not managed to find how to do it. I am very confused with all the answers I read because I can't find a clear reference about dealing with dates in google scripts.

From what I understand, basic date manipulations can be done with:

  • new Date(): from what I understand, it defines a object which has some properties. I don't know how to use the date of a cell and convert it as such an object.
  • Utilities.formatDate(): this is to change the format of the date seen as a string.
  • the library Moment (http://momentjs.com/)

In the end, how can I input two dates (e.g. 31/01/2016) and, say, find the maximum between the two and extract the month of the first one?

function myfun(date1,date2) {  
   // month = month of date 1
   // return maximum(date1,date2);  
}

I am also interested if someone can explain the outlines of dealing with dates, or indicate a good reference.

0

3 Answers 3

16

Getting and setting datetime values

Whenever your script calls .getValue() on a cell formatted as a date, date-time, or duration, it gets a JavaScript Date object. You can then apply various methods listed on that MDN page to this object, including getMonth() for the month. Example:

  var sheet = SpreadsheetApp.getActiveSheet();
  var value = sheet.getRange('A1').getValue();  // is a Date object
  Logger.log([value.getMonth(), value.getHours(), value.getUTCHours()]); 
  // examples of available methods

Conversely, you can assign a Date object to a cell using setValue, and it will be automatically formatted as such.

  sheet.getRange('B2').setValue(new Date());  // current timestamp

Timezones

The timestamp in Google Sheets is in local timezone, which is set via File > Spreadsheet settings. The script also operates within a timezone, which may be different and is found under File > Project properties in the script editor. If these timezones don't agree, you'll have a problem with timestamps.

To check whether the timezones agree, you can either compare both manually, or do something like this:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1').setFormula('=date(2015,1,1)');
Logger.log(sheet.getRange('A1').getValue().getHours());

This inputs the formula =date(2015,1,1) in the cell A1. The result will be 2015-01-01 00:00:00 in the spreadsheet's time zone. The script then gets the date and extracts hours in the local time of the script. If the timezones agree, you should see 0.0 in the log.

0
5

Short answer

Below two custom functions are presented as examples of how Google Sheets and Google Apps Script could work with dates.

Example 1: Get the newest date

get the newest date

function myFunction(date1,date2) {
  return new Date(Math.max(date1,date2)) 
}

Example 2: Get the month of the newest date

function myFunction2(date1,date2) {
  var date = new Date(Math.max(date1,date2));
  return date.getMonth();
}

Brief explanation

In the first example the Math.max JavaScript method does the job.

If order to get a date as result, a date object is created by using new Date().

In the second example, a date object is assigned to a variable, then the getMonth() JavaScript method is used to get the month index. Bear in mind that JavaScript use 0 for the starting element so 0 is for January, 1 for February and so on.

References

2
  • 1
    i) Are date1, date2 passed as numbers (typically: 54243)? ii) I now tried to extract the month of date2 using date2.getMonth() but when the cell is 01/12/2015 it returns 10 and 11 for 02/12/2015. I guess it's because of the time zone. The sheet function month() is so much simpler.
    – anderstood
    Jan 12, 2016 at 21:58
  • Google Sheets handle dates as serialized numbers the same as JavaScript but each one have different start date. So, if you want to use JavaScript date functions, first should convert the spreadsheet date to a JavaScript date. As you already noticed, the easier path is to use spreadsheet functions to handle spreadsheet dates and JavaScript functions to handle JavaScript dates. Jan 12, 2016 at 22:13
2

This is an old post, but still valuable. You can set the values back to date objects prior to comparing them:

var curDate = Utilities.formatDate(FullDT, "GMT-0400", "MM/dd/yyyy")  
var WhenToSend = values[i][3];  
WhenToSend = Utilities.formatDate(WhenToSend, "GMT-0400", "MM/dd/yyyy")  

  //set dates to be compared as Date objects  
  WhenToSend = new Date(WhenToSend);  
  curDate = new Date(curDate);  

if ((WhenToSend <= curDate)  
{then do this}  
else  
{do that}  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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