Google Apps Script – Basic Date Manipulation in Google Script

google sheetsgoogle-apps-script

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.

Best Answer

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.

Related Topic