0
function onOpen() { 
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('TRIGGER');
menu.addItem('create new doc' , 'createNewGoogleDocs');
menu.addToUi();

} 

function createNewGoogleDocs() {
const googleDocTemplate = DriveApp.getFileById('1l6GsQsIZ5XhfhWFi_RcX5ea-8Zh8DvGd28QugNxCeZo');
const destintionFolder = DriveApp.getFolderById('1Jzbk9pPAQTXRuvC9_5rFYoRIV0kbsf4c');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CompletedForms');
const rows = sheet.getDataRange().getValues();


const copy = googleDocTemplate.makeCopy('${rows[0]}, ${rows[1]}',destintionFolder);
const doc = DocumentApp.openById(copy.getId())
const body = doc.getBody();

body.replaceText('{{Clinician}}',rows[1][0]);
body.replaceText('{{Date}}',rows[1][1]);
body.replaceText('{{Name}}',rows[1][2]);
body.replaceText('{{DOB}}',rows[1][3]);
body.replaceText('{{Address}}',rows[1][4]);
body.replaceText('{{EpisodeID}}',rows[1][5]);
body.replaceText('{{PresentingProblem}}',rows[1][6]);
body.replaceText('{{PhysicalExam}}',rows[1][7]);
body.replaceText('{{USSReport}}',rows[1][8]);
body.replaceText('{{Image1}}',rows[1][9]);
body.replaceText('{{Image2}}',rows[1][10]);
body.replaceText('{{Image3}}',rows[1][11]);
body.replaceText('{{Image4}}',rows[1][12]);
body.replaceText('{{Image5}}',rows[1][13]);
body.replaceText('{{Image6}}',rows[1][14]);
body.replaceText('{{Image7}}',rows[1][15]);
body.replaceText('{{Image8}}',rows[1][16]);
body.replaceText('{{Diagnosis}}',rows[1][17]);
body.replaceText('{{Injection}}',rows[1][18]);
body.replaceText('{{Drug1}}',rows[1][19]);
body.replaceText('{{Drug2}}',rows[1][20]);
body.replaceText('{{Drug3}}',rows[1][21]);
body.replaceText('{{Drug4}}',rows[1][22]);
body.replaceText('{{Reaction}}',rows[1][23]);
body.replaceText('{{Plan}}',rows[1][24]);

doc.saveAndClose();

}

Please guide, What I am missing as this is my first ever code.

1

1 Answer 1

1

Use Utilities.formatDate(), like this:

  // ...
  const timezone = sheet.getParent().getSpreadsheetTimeZone();
  const toDateString_ = (date) => Utilities.formatDate(date, timezone, 'dd-MM-yyyy');
  // ...
  body.replaceText('{{Date}}', toDateString_(rows[1][1]));
  // ...

Alternatively, in the event the data in the spreadsheet is already in the format you want to see in the text document, just replace sheet.getDataRange().getValues() with sheet.getDataRange().getDisplayValues().

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.