How do I control the date format when generating a (PDF) from a google spreadsheet with script?

Question

I am seeking clarification on the question asked here. Serge insas recommends "You should convert your dates in formatted strings when you insert them in your doc in the replace loop. The function to use is Utilities.formatDate."

I want to do what Serge recommends, but I'm having some difficulty implementing it. The script runs great as is, but I can save a lot of time by not having to convert dates to text strings before I paste them into my spreadsheet. It makes far more sense to code the date format I want on my PDFs (d mmmm yyyy). Any assistance would be greatly appreciated.

//Creates the custom menu in the spreadsheet "Run Script"
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Scripts')
      .addItem('Create Certs', 'menuItem1')
//      .addItem('Change Cell Format', 'menuItem2')
//      .addItem('Reset Cell Format', 'menuItem3')
      .addToUi();
}
//Runs the menuItem 1 operation (Create Certs)
function menuItem1() {

//Defines the start row and calculates the number of rows to be processed
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = Browser.inputBox("Enter Start Row");
  var endRow = Browser.inputBox("Enter End Row");
  var numRows = (endRow - startRow) + 1;
  var dataRange = sheet.getRange(startRow, 1, numRows, 7);

 var counter =0;   
 var data = dataRange.getValues();   
 var templateDoc = DriveApp.getFileById("1baxSUxfSdzcVheR3Y2qgieWeSAqNybPfWct1913uRIc");   
 var templateCopy = templateDoc.makeCopy();
 var templateCopyId = templateCopy.getId();

//Begin Tew's code
 var dateOld;   
 var courseOld;   
 var fullNameOld;   
  for (var i = 0; i < data.length; ++i) {
        var doc = DocumentApp.openById(templateCopyId);
        var body = doc.getActiveSection();
        var row = data[i];
        var date = row[0]; //like this for testing to get a different value on each pdf
        var nic = row[1];
        var course = row[2];
        var lastname = row[3];
        var firstname = row[4];
        var middle = row[5]
        var email = row[6];
        //    var docname = lastname+" "+nic+" PME Cert"; //not used in this version
        var subjectTxt = "NWC "+ course +" Online PME Course Certificate";
        var fullBody = "PME COURSE COMPLETION CERTIFICATE" + "\n\n";
        fullBody += "Your " + course + " course completion certificate is attached." + "\n\n";
        fullBody += "NOTES:" + "\n";
        fullBody += "1. NWC does NOT mail hardcopy certificates." + "\n";
        fullBody += "2. NWC does not award certificates for the SNCO JPME course." + "\n\n";
        fullBody += "Regards," + "\n\n";
        fullBody += "Professor Steve Pierce" + "\n";
        fullBody += "U.S. Naval War College "+ "\n";
        fullBody += "Online PME Program Team" + "\n\n";
        fullBody += "Learn more about NWC's Online PME Program at the link below:" + "\n";
        fullBody += "http://www.usnwc.edu/Academics/College-of-Distance-Education/PME-(1).aspx" + "\n";

        var row = data[i];
        var fullName = firstname+' '+middle+''+lastname
        var fdate = Utilities.formatDate(new Date(date), "d mmmm yyyy");
        if(counter ==0){
          body.replaceText('fullName',fullName);  
          body.replaceText('course', course);
          body.replaceText('date', fdate); 
        }
        else {   
          body.replaceText(fullNameOld,fullName);
          body.replaceText(courseOld, course);
          body.replaceText(dateOld, fdate); 
        }

        dateOld = fdate; 
        courseOld = course;
        fullNameOld = fullName

        counter ++

          doc.saveAndClose()
          var attachment = doc.getAs("application/pdf")

          MailApp.sendEmail(email, subjectTxt, fullBody, {attachments: attachment});   
 } 
          DriveApp.getFileById(templateCopyId).setTrashed(true);
}

Where do you have the difficulty implementing it? Where did you try putting the formatting? — Robin Gertenbach
@RobinGertenbach I added the changes I tried to make to the code by following Serge insas' instructions. Please see line 57. I also changed the variable "date" to "fdate" in the replacement loop to insert the correct date format into the PDF. Thanks for your help! — Steve P
@RobinGertenbach Your solution worked perfectly. I can't thank you enough! — Steve P

Robin Gertenbach Robin Gertenbach · Accepted Answer · 2016-02-26T15:38:46

There are two issues with line 57.

Utilities.formatDate requires a date, a timezone, which is not present and then a format.
See here for the documentation

The other issue is the format string itself.

mmmm is a very long minute format. I assume you want the long month which would be MMMM.

So the command to use is

var fdate = Utilities.formatDate(new Date(date), "UTC", "d MMMM yyyy");

Which would return 26 February 2016 for today.
If you want a different format try taking a look at the SimpleDateFormat Class of Java which this method borrows the formatting options from.

How do I control the date format when generating a (PDF) from a google spreadsheet with script?

1 Answers