google-apps-scripttriggersgoogle-docsgoogle-forms

Autofill Google Doc From Form not working; GSheet also stops updating new form responses


I want to automatically copy incoming Google Form responses to a Google Doc. For this, I followed Jeffrey Everhart's tutorial step by step. I then tried submitting a new response, but no Google Doc was generated in the destination folder. More than that, the new response did not reflect in the linked Google Sheet; I can only view it in the Google Form responses page. This is the code I used, if it helps diagnosing the problem:

function autoFillGoogleDocFromForm(e) {
  var timestamp = e.values[0];
  var upMail = e.values[1];
  var agreement = e.values[2];
  var fullName = e.values[3];
  var livedName = e.values[4];
  var pronouns = e.values[5];
  var age = e.values[6];
  var birthday = e.values[7];
  var contactNumber = e.values[8];
  var altEmail = e.values[9];
  var degreeProgram = e.values[10];
  var yearLevel = e.values[11];
  var facebookLink = e.values[12];
  var voiceLesson = e.values[13];
  var singingExp = e.values[14];
  var others = e.values[15];
  var graphicDesign = e.values[16];
  var aof = e.values[17];
  var screeningDateA = e.values[18];
  var screeningDateB = e.values[19];

  var templateFile = DriveApp.getFileById("19AYo5zEij9iWAN5pBaV8HNgaCwqsaRGvpDYd9X_p1rg");
  var templateResponseFolder = DriveApp.getFolderById("1S1yBfh6W30YQD5Jnd72dD8wREVkQund-");

  var copy = templateFile.makeCopy(fullName,templateResponseFolder);
  
  var doc = DocumentApp.openById(copy.getId());

  var body = doc.getBody();

  body.replaceText("{{AOF}}", aof);
  body.replaceText("{{Name}}", fullName);
  body.replaceText("{{Pronouns}}", pronouns);
  body.replaceText("{{Year}}", yearLevel);
  body.replaceText("{{Course}}", degreeProgram);
  body.replaceText("{{LivedName}}", livedName);
  body.replaceText("{{Age}}", age);
  body.replaceText("{{Birthday}}", birthday);
  body.replaceText("{{ContactNumber}}", contactNumber);
  body.replaceText("{{AltMail}}", altEmail);
  body.replaceText("{{Facebook}}", facebookLink);
  body.replaceText("{{UPMail}}", upMail);
  body.replaceText("{{VoiceLesson}}", voiceLesson);
  body.replaceText("{{SingingExp}}", singingExp);
  body.replaceText("{{Others}}", others);
  body.replaceText("{{GraphicDesign}}", graphicDesign);
  
  doc.saveAndClose();
}

I tried resubmitting new responses and refreshing the GSheet as well as the destination folder for the Google Doc, to no avail. I am not very adept with coding as this is only my second time trying to work something out using coding.


Solution

  • I made a simpler version but did exactly the same thing and it worked the very first time.

    function onMyFormSubmit(e) {
      Logger.log(JSON.stringify(e));//check to make sure your getting the event object look in execution log
      var timestamp = e.values[0];
      var v1 = e.values[1];
      var v2 = e.values[2];
      var v3 = e.values[3];
      var v4 = e.values[4];
      var v5 = e.values[5];
      var testdoc = DriveApp.getFileById(gobj.globals.testdocid);//these are just global ids
      var folder1 = DriveApp.getFolderById(gobj.globals.folder1id);
      var copy = testdoc.makeCopy(v3,folder1);
      var doc = DocumentApp.openById(copy.getId());
      var body = doc.getBody();
      body.replaceText("{{COL1}}", v1);
      body.replaceText("{{COL2}}", v2);
      body.replaceText("{{COL3}}", v3);
      body.replaceText("{{COL4}}", v4);
      body.replaceText("{{COL5}}", v5);
      doc.saveAndClose();
    }
    

    Try using Logger.log to check where you are going wrong. And you might start with a simpler version of that code just to get it running.