Make dates display as DD/MM/YYYY in an autogenetered PDF file.

[Admin Blindage]

Hi,

I`m trying to display dates from a google sheet data. We are able to display all information in the desired format except DATE formats. They are displaying with DATE, TIME, ZONE format. (File1)

We tried to change the format in google Sheet itself, tried different formulas and formats but couldn`t display the data correctly.

They way the data will be provided is always with same format.... from google sheets, with double clic and calendar selection......... (File2)

Maybe there is a way this makes the format we need, which is DD/MM/YYYY in the script we are using to generate and display the data. 

Hope I can get some help.

Thanks in advance

const sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1RYIXxECrXBmHApmrhGvTYEnLekuECfZUeZnR2EscO2o/edit#gid=50599692");

const docFile = DriveApp.getFileById("1MKLb4dG020kLwtz-LzalSZoMuyn3dg3Lf-GnxHbtWYk");

const tempFolder = DriveApp.getFolderById("1y13nBdkjhD6sdp1JUe6EZm0iNc2PqyBC");

const pdfFolder = DriveApp.getFolderById("1LKPEMgjntfqm4rA35Gml8Jwhd8hb2I0M");

const currentSheet = sheet.getSheetByName("Cuenta");

const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 25).getValues();

function createBulkPdfs(){

data.forEach((row, index) => {

createPDF(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[23], docFile, tempFolder, pdfFolder, row[3] + " Apartamento " + row[2] + " " + row[0] + " " + row[1], index)

});

}

function createPDF(firstName, lastName, apartment, mes, inicio, final, dia, emision, limite, corte, air, lt, duc, coc, energia, rata, credito, naturgy, total, balance, telefono, docFile, tempFolder, pdfFolder, pdfName, index) {

const tempFile = docFile.makeCopy(tempFolder);

const tempDocFile = DocumentApp.openById(tempFile.getId());

const body = tempDocFile.getBody()

body.replaceText("{first}", firstName);

body.replaceText("{last}", lastName);

body.replaceText("{last}", lastName);

body.replaceText("{apartment}", apartment);

body.replaceText("{mes}", mes);

body.replaceText("{emision}", emision);

body.replaceText("{limite}", limite);

body.replaceText("{corte}", corte);

body.replaceText("{balance}", balance);

body.replaceText("{air}", air);

body.replaceText("{Lt}", lt);

body.replaceText("{duc}", duc);

body.replaceText("{coc}", coc);

body.replaceText("{inicio}", inicio);

body.replaceText("{final}", final);

body.replaceText("{dia}", dia);

body.replaceText("{energia}", energia);

body.replaceText("{rata}", rata);

body.replaceText("{credit}", credito);

body.replaceText("{naturgy}", naturgy);

body.replaceText("{total}", total);

body.replaceText("{telefono}", telefono);

tempDocFile.saveAndClose();

const pdfContentBlob = tempFile.getAs(MimeType.PDF);

const pdfFile = pdfFolder.createFile(pdfContentBlob).setName(pdfName);

tempFolder.removeFile(tempFile);

const pdfUrl = pdfFile.getUrl();

currentSheet.getRange(index+2, 22, 1, 1).setValue(pdfUrl);

}

[Laurie J. Nason]

Hi, best way I have found is to use getDisplayValues() rather than just getValues() - this returns what is shown on the sheet - only issue might be that it does some strange things with other fields - such as possibly truncating numbers if you need the full number in the script.

e.g. for you

const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 25).getDisplayValues();

------ Original Message ------

From "Admin Blindage" <ad...@mundoblindage.com>

To "Google Apps Script Community" <google-apps-sc...@googlegroups.com>

Date 07/09/2022 23:51:19

Subject [Apps-Script] Make dates display as DD/MM/YYYY in an autogenetered PDF file.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/714af323-a951-4510-b7d2-ee438d25d291n%40googlegroups.com.

[account administrator]

Hi

The example below should help you to achieve the date in field {final} as DD/MM/YYYY:
 

try{

    let final = "03/10/2023"; 

    let d = new Date(final);
    let formattedDate = Utiities.formatDate(new Date(d.getTime), "American/Chicago", "DD/MM/YYYY");
    console.log(formattedDate); 

}catch(e){console.log(e)}

You may drop it into a function and pass the variable directly then,

return Utiities.formatDate(new Date(d.getTime), "American/Chicago", "DD/MM/YYYY");