0

I have a google sheet where each row is a new lead. Currently my script can take each row and turn it into a PDF through an 'offer sheet' i pre-made. if i select a row, thats the row the script runs on. My challenge is the date formatting that gets brought over.

In google sheets the date is just MM/DD/YY but when it gets pulled to the PDF it changes to this formatting Tue Nov 23 2021 00:00:00 GMT-0700 (Mountain Standard Time)

note* the date im getting from google sheets needs to be put into the PDF, i cant just create a new date, its from when the lead was recieved.

the delimiter im using for the script to recognize the fields in the PDF to update is by surrounding the sheets column titles with a % symbol.

here is my script

function onOpen (){

SpreadsheetApp.getUi().createMenu('Create PDF').addItem('Create PDF','createPDF').addToUi()


}

function createPDF(){

  let copyFile = DriveApp.getFileById('[*google doc unique link removed*]').makeCopy(),
      copyID = copyFile.getId(),
      copyDoc = DocumentApp.openById(copyID),
      copyBody = copyDoc.getBody()

  let activeSheet = SpreadsheetApp.getActiveSheet(),
      numOfCol = activeSheet.getLastColumn(),
      activeRowIndex = activeSheet.getActiveRange().getRowIndex(),
      activeRow = activeSheet.getRange(activeRowIndex, 1,1,numOfCol).getValues(),
      headerRow = activeSheet.getRange(1,1,1,numOfCol).getValues(),
      columnIndex = 0

    for (; columnIndex < headerRow[0].length; columnIndex++){

      copyBody.replaceText('%' + headerRow[0][columnIndex] + '%',activeRow[0][columnIndex])

    }

    copyDoc.saveAndClose()

    let newFile = DriveApp.createFile(copyDoc.getAs('Application/PDF'))

    newFile.setName(activeRow[0][2]+ ', ' + activeRow[0][3] + ', ' + activeRow[0][4] +' (Client Offer Page)')

    copyFile.setTrashed(true)

    SpreadsheetApp.getUi().alert('PDF Generated in the Drive')
Jon Oneill
  • 37
  • 8
  • 1
    Does this answer your question? [Format JavaScript date as yyyy-mm-dd](https://stackoverflow.com/questions/23593052/format-javascript-date-as-yyyy-mm-dd) – CMB Nov 24 '21 at 19:41

1 Answers1

0

Use getDisplayValues instead on your activeRow. Or format the date before writing.

See link under writing a custom date format

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • Suggestion: You can improve your answer by providing a specific technical solution directly in the answer, instead of (or as well as) providing a link to an external location. See also [Your answer is in another castle: when is an answer not an answer?](https://meta.stackexchange.com/q/225370/671985) for more background. – andrewJames Nov 25 '21 at 00:54