Google-sheets – How to format the date in this Google Apps Script

google sheetsgoogle-apps-script

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);
}

Best Answer

Use the following code to change the date notation.

Code

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

References