Search code examples
google-apps-script

Google App Script: Send email with attachment from google form response

Previously I've made a script to generate .docx file from google form responses (one file for each google form responses) and it's uploaded to a google drive folder, and now I want the generated files emailed to the respondent's email.

I've tried this script:

function sendEmail(e){
  var respondentName = e.values[2];
  var clientName = e.values[3];
  var email = e.values[24];
  var subject = "Order details " + clientName;
  var message = "Thank you " + respondentName + " for submitting your form. Order under the name " + clientName + " has been recorded.";
  var waiver = DriveApp.getFileById("");
  var liabilityWaiver = waiver.getAs(MimeType.PDF);

  GmailApp.sendEmail(email, subject, message, {attachments:[liabilityWaiver]});
}

Now my problem is because the file id for each .docx file generated by the other script is different I can't figure what script should I put instead of the getFileById("").

Here's the script that generates the .docx file

function autoFillGoogleDocsFromForm(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 templateFile = DriveApp.getFileById("templatefileid");
  var templateResponseFolder = DriveApp.getFolderById("folderdestinationid");

  var copy = templateFile.makeCopy('Order details ' + client name + ' - ' + a, templateResponseFolder);
  var doc = DocumentApp.openById(copy.getId());

  var body = doc.getBody();

  body.replaceText("{{ActivityDate}}", activityDate);
  body.replaceText("{{ClientName}", clientName);
  body.replaceText("{{ClientAddress}}", clientAddress);
  body.replaceText("{{ClinetPhoneNumber}}", 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);
  body.replaceText("{{RespondentName}}", respondentName);

  doc.saveAndClose();
  
}

The data fields are (from left to right):

Timestamp, Activity Date, Respondent's Name, Client's Name, Client's Address, Client's Phone Number, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, Respondent's Email Address

and for the form submissions are just a text answer.

Solution

  • You have a Google Form that branch/stores use to lodge orders. For each form submission, you want to:

    • create an order form by merging the submission with a Google Docs template,
    • send a confirmation email to the branch/store attaching a PDF copy of the merged Google Doc

    You have two scripts (sendEmail and autoFillGoogleDocsFromForm) but you are unable to combine the two.

    The following answer takes the OP scripts and combines them. There are several minor changes but the key aspects are:

    • The installable onFormSubmit trigger is sendEmail
    • sendEmail
      • var docId = autoFillGoogleDocsFromForm(e)
        • sendEmail begins by calling autoFillGoogleDocsFromForm (to create the merged document)
        • note that Event Objects are passed on
      • var waiver = DriveApp.getFileById(docId)
        • the document ID of the merged Google Doc is returned and used to create the PDF attachment
    • autoFillGoogleDocsFromForm
      • insert the File ID for the Google Doc Template
      • insert the Folder ID for the destination folder
      • return docId
        • having created the merged document, the function ends by returning the document Id to sendEmail
    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

}