Google-sheets – Forcibly format column as Date in sheets onOpen() with Javascript

google sheets

I have a simple sheet that gets some date data submitted via a form. The form works great, data is collected, but I have to click on the column, and do Format → Number → Date in order to get dates to sort properly when used in the subsequent pivot table reports.

I don't know why dates aren't being treated as dates when the form submits it, but I don't care, my thought was just to have a trigger as to when the sheet opens, the column gets formatted so the user doesn't have to do it by hand.

I've done the tutorials for adding the onOpen() event to customise a menu, and called some other Javascript, but don't I have a clue as to what the necessary Javascript is to format the column—in this case, Column C. If somebody can point me in the right direction, I would appreciate it.

Best Answer

The method setNumberFormat does the job:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("C:C");
  range.setNumberFormat("yyyy/MM/dd");
}

This formats column C as yyyy/MM/dd. Many other date formats are available: the method setNumberFormat supports Java's SimpleDateFormat.

Related Topic