Search code examples
google-apps-scriptgoogle-docs

Google Apps Script: send autogenerated Google Docs file to respondent's email as .docx file


Previously I got help to make a script to autogenerate Google Docs from Google Form response, and then send it to the respondent's email as a PDF file. But now I want the attachment file to be a Microsoft Word file (.docx) instead of PDF.

Here's the script:

function sendEmail(e){

  // get the doc ID for merged Google Doc
  var docId = autoFillGoogleDocsFromForm(e)
  // Logger.log("DEBUG: Autofill returned doc id = "+docId)

  var respondentName = e.values[2];
  var clientName = e.values[3];
  var email = e.values[24];
  // Logger.log("DEBUG: Respondent name = "+respondentName+", Client name = "+clientName+", Email address= "+email)
  var subject = "Order details " + clientName;
  // Logger.log("DEBUG: Subject: "+subject)
  var message = "Thank you " + respondentName + " for submitting your form. Order under the name " + clientName + " has been recorded.";
  // Logger.log("DEBUG: Message: "+message)
  var waiver = DriveApp.getFileById(docId);

  var liabilityWaiver = waiver.getAs(MimeType.PDF);

  GmailApp.sendEmail(email, subject, message, {attachments:[liabilityWaiver]});
  // Logger.log("DEBUG: mail sent")
}

function autoFillGoogleDocsFromForm(e) {

  // this function is called by sendEmail(e)

  var activityDate = e.values[1];
  var respondentName = e.values[2]
  var clientName = e.values[3];
  var clientAddress = e.values[4];
  var clientPhoneNumber = e.values[5];
  var a = e.values[6];
  var b = e.values[7];
  var c = e.values[8];
  var d = e.values[9];
  var e = e.values[10];
  var f = e.values[11];
  var g = e.values[12];
  var h = e.values[13];
  var i = e.values[14];
  var j = e.values[15];
  var k = e.values[16];
  var l = e.values[17];
  var m = e.values[18];
  var n = e.values[19];
  var o = e.values[20];
  var p = e.values[21];
  var q = e.values[22];
  var r = e.values[23];


  var templatefileid = "<<insert Google Doc Template: file ID >>"
  var folderdestinationid = "<<insert Folder ID of destination folder>>"
  var templateFile = DriveApp.getFileById(templatefileid);
  var templateResponseFolder = DriveApp.getFolderById(folderdestinationid);

  var copy = templateFile.makeCopy('Order details ' + clientName + ' - ' + a, templateResponseFolder);
  var docId = copy.getId()
  var doc = DocumentApp.openById(docId);
  var body = doc.getBody();

  body.replaceText("{{ActivityDate}}", activityDate);
  body.replaceText("{{RespondentName}}", respondentName);
  body.replaceText("{{ClientName}}", clientName);
  body.replaceText("{{ClientAddress}}", clientAddress);
  body.replaceText("{{ClientPhoneNumber}}", clientPhoneNumber);
  body.replaceText("{{A}}", a);
  body.replaceText("{{B}}", b);
  body.replaceText("{{C}}", c);
  body.replaceText("{{D}}", d);
  body.replaceText("{{E}}", e);
  body.replaceText("{{F}}", f);
  body.replaceText("{{G}}", g);
  body.replaceText("{{H}}", h);
  body.replaceText("{{I}}", i);
  body.replaceText("{{J}}", j);
  body.replaceText("{{K}}", k);
  body.replaceText("{{L}}", l);
  body.replaceText("{{M}}", m);
  body.replaceText("{{N}}", n);
  body.replaceText("{{O}}", o);
  body.replaceText("{{P}}", p);
  body.replaceText("{{Q}}", q);
  body.replaceText("{{R}}", r);

  doc.saveAndClose();
  // Logger.log("DEBUG: Done")
  return docId

}

Here's the link to my previous question: My Previous Question

Then the person who helped me with the previous script, link me this as a reference for my current problem: Reference

But until now I still can't figure out which part exactly should I copy and which part should I replace from my current script.

I'm new in this scripting world and still learning.


Solution

  • In your script, how about modifying the function `sendEmail as follows?

    From:

    var waiver = DriveApp.getFileById(docId);
    
    var liabilityWaiver = waiver.getAs(MimeType.PDF);
    

    To:

    var waiver = DriveApp.getFileById(docId);
    var url = `https://docs.google.com/feeds/download/documents/export/Export?exportFormat=docx&id=${docId}`;
    var liabilityWaiver = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob().setName(`${waiver.getName()}.docx`);
    
    • By this modification, the Google Document is converted to DOCX format. And, the blob is retrieved as liabilityWaiver.

    • In this modification, the output filename is ${waiver.getName()}.docx. If you want to change this, please modify this.

    • If an error is related to Drive API, please enable Drive API at Advanced Google services.