How can control the date format when generating a

2019-06-13 03:40发布

问题:

I generate a PDF file from a sheet of Google (that is populated with Google form) using a script and a template with Google Docs. But doing this I obtain that in google sheets date are in dd/mm/yyyy format while in the final PDF it's like this = Sat Feb 27 2016 00:00:00 GMT+0100 (CET)

The "core" of the script is (for each row of the google sheet)

var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(FileName);
var copyId = copyFile.getId();
var copyDoc = DocumentApp.openById(copyId);
var copyBody = copyDoc.getActiveSection();

for (;columnIndex < headerRow[0].length; columnIndex++) {
    copyBody.replaceText('%' + headerRow[0][columnIndex] + '%', RowData[i][columnIndex])
}

columnIndex = 0;

// Create the PDF file, rename it if required and delete the doc copy

copyDoc.saveAndClose();

var dir = DriveApp.getFoldersByName("PDF FOLDER").next();  
var newFile = dir.createFile(copyFile.getAs('application/pdf'));  

if (PDF_FILE_NAME !== '') {
    newFile.setName(FileName)
    copyFile.setTrashed(true)
}  

Where (and how) can I control the RowData[i] that are date type in order to obtain the correct format in doc/PDF file? (In the template the substitution with effective value is made with the %date name% instruction)

Thanks Vanni

回答1:

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 and is documented here.

It is quite straightforward to use, example :

var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");

Note, instead of using GMT or whatever GMT+XX value, use preferably the Session.getScriptTimeZone() that will automatically give the right value according to your timezone (even in case of daylight savings).

EDIT :

you could try getDisplayValue() too , it returns the value as a string representation of what is shown in a cell.