How to format the date in this Google Apps Script

Question

I've come across this rank checking script. I'm not sure exactly where I found it but it's built on Google Docs and a script which automates the rank checking process. I understand a bit of coding but not enough to really figure this out. I'm certain someone here can figure it out.

The script runs nightly, queries Google for my keywords and then populates blank cells in a Google Spreadsheet. The date format which appears is 15.06.2014 06:08:21

I'd like it to be a date format I'm more familiar with, such as mm/dd/yyyy or something like that.

Anyhow, I've dabbled with the script (what language is this written in? python?) and I see it sets a variable named curDat to new Date(). From my understanding, Date() is a function that is being called and no parameters are being passed to it - correct? Is the problem in the Date() function or is this just a simple case of my having to format the date in Google Spreadsheets?

Here's a paste of the entire script - it is short:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.insertColumnBefore(5);
  var column = sheet.getRange('B13:B');
  var values = column.getValues();
  var rankingdrop = sheet.getRange("c8").getValue();
  var ct = 0;
  var colors = [];
  while ( values[ct][0] != "" ) {
  var formu = sheet.getRange("d" + (ct+13)).getFormula();
    sheet.getRange("d" + (ct+13)).setFormula(formu);
    Utilities.sleep(3000);
  var rank = sheet.getRange("d" + (ct+13)).getValue();
  sheet.getRange("e" + (ct+13)).setValue(rank);
  var oldrank = sheet.getRange("f" + (ct+13)).getValue();
  var colorcell = sheet.getRange("e" + (ct+13));
    if(rank > oldrank){
    colorcell.setFontColor("#ff0000");
  } else if (rank < oldrank){
    colorcell.setFontColor("#088A08");
  }
    if (rank - oldrank >= rankingdrop) {
      var mailalert = true;
    }
    ct++;
  }
  var sendmail = sheet.getRange("c7").getValue();
  if (mailalert == true && sendmail == "yes")  {
    var emailAddress = sheet.getRange("c9").getValue();
    var message = "Ranking alert. Please check your rankings";
    var url = sheet.getRange("c4").getValue();
    var subject = ("Ranking Alert for " + url);
    MailApp.sendEmail(emailAddress, subject, message);
  }
  var curDat = new Date();
  var startOTY = curDat.getFullYear();
  var curValue = Date.parse(curDat);
  var begDat = new Date(startOTY,0,1);
  var begValue = Date.parse(begDat);
  var weekNum = parseInt((curValue - begValue)/604800000)+1;
  sheet.getRange("e12").setValue(curDat);
}

Answer 1

Use the following code to change the date notation.

Code

var curDate = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy")

References

• formatDate(date, timeZone, format)
• date notations

Answer 2

Use setNumberFormat to set the date (or number) format for a particular cell. This example puts today's date in A1 of the current sheet, formatted as MM/dd/yyyy:

var cell = SpreadsheetApp.getActiveSheet().getRange(1, 1);
cell.setValue(new Date()).setNumberFormat("MM/dd/yyyy");

Reasons to use setNumberFormat (and not Utilities.formatDate, as if often advised on the Internet):

1. Utilities.formatDate does not change the cell format; it converts its content to text. If you are going to use the date value in computations, you don't want it to be converted to text.
2. Utilities.formatDate requires one to specify a timezone. If you put something like "GMT+1" and your country observes Daylight Saving Time, you can expect hard-to-track bugs to come up a few months later. With setNumberFormat, the date value remains consistent with all other date values in the spreadsheet, governed by its timezone setting.

Answer 3

With Time:

  var formatedDate = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy-HH:mm:ss")