9

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.

  • 1
  • Thank you. Is there no other way? It's seems incredibly complicated to extract, for example, the month of a date! Plus it has to take into account the time zone... That's a bit twisted for simple (fixed) date operations. – anderstood Jan 12 '16 at 21:45
  • I added an answer focused in the code of the case that was exposed, but the question is two broad (date manipulation). Maybe you should start by looking for JavaScript code snippets and online courses. – Rubén Jan 12 '16 at 21:55
  • @Rubén I understand. I did not want to ask a "write code for me" question so was looking for general information which I did not really found. Sergei insas's post you indicated is helpful. – anderstood Jan 12 '16 at 22:04
  • Regarding general information about JavaScript see developer.mozilla.org/en-US/docs/Web/JavaScript and regarding Google Apps Script see developers.google.com/apps-script. – Rubén Jan 12 '16 at 22:28
11

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.

4

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

  • 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 '16 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. – Rubén Jan 12 '16 at 22:13
  • @anderstood: I added a second example. – Rubén Jan 12 '16 at 23:31
  • Thank you again. Regarding the second example, as I had written, when the max is 01/12/2015 myfunction2 returns 10 and when the max is 02/12/2015, it returns 11. While I'm OK with the 11, I don't understand why is returns 10 for 01/12/2015. As I said, I believe this is due to GMT but I am not sure. Am I missing something? – anderstood Jan 12 '16 at 23:51
  • @anderstood: create a demo spredsheet share it with anyone with the link for view only and post a new question including the link to the demo spreadsheet. – Rubén Jan 13 '16 at 1:15

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.