2

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 FormatNumberDate 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.

1

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.

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.