Using Google Apps Script to make Google Forms that have scripts already attached

Question

I am looking for a solution to a novel problem I have encountered in applying google apps scripting to, specifically, the google form product.

Context

The company I work for currently performs Quality Assurance(QA) on software we create for our clients by sending feedback through email.

This software is composed of "Parents" and their "Children". I was asked to look into using Google Forms as a method of creating QA feedback for each piece of software created.

I was able to get very far along in this process leveraging the Google Apps Script documentation. However, I have hit a knowledge barrier when it comes to implementing this in the wild.

Problem

I have one script attached to a very basic form that asks for the Name of the Tool(how we track our QA requests), Name of the Parents, and the name of the children for this software. (Currently I am asking for email as well for ease, but will soon replace with the automatic email grabbing function google apps script has).

This script takes in the responses to this first form and creates a new one using the responses. Now, for building purposes, I have created a second google apps script in the script editor of a form that was created by a submission of the first form. This script takes in the responses of to this second form and creates a third (I know, "formception" right?).

After building all this out and being fairly satisfied with my results I realized a massive error in my thinking. Outside of testing purposes users will be making many new forms from that first one. Each of these new forms will not have the google apps script, that I created for the second form, associated with them and as such will not generate the needed third form.

I am know seeking help identifying a method that will let the code I have written for the second form be automatically added to each new form the first creates. If this is not possible, I am seeking any alternatives. I have considered methods of containing the second google apps script within the first's codebase but I could not find a way to trigger that function on submission of the second form from within the first's script. Any ideas or approaches to consider would be very much appreciated.

Code:

As a note; I do realize this code is a bit messy and very redundant. This was hacked together as a brief proof of concept. I plan to clean it up and modularize it if I can find a solution to the issue above. Before wasting time on that though, I want to determine if what I am trying to do is possible within the limits of Google Apps Script.

First Script

//A function to run this unweildy Formception beast
//Its set to be run on a submission event of the original "First QA Form" which resides in ********'s Drive -> QA -> Dynamic Google Form Project Folder
function onSubmit() {
 var form = FormApp.getActiveForm();
 var formResponses = form.getResponses();

//this whole loop just puts the responses into nested arrays
 for (var i = 0; i < formResponses.length; i++) {
   var formResponse = formResponses[i];
   var itemResponses = formResponse.getItemResponses();
   for (var j = 0; j < itemResponses.length; j++) {
     var itemResponse = itemResponses[j];
     Logger.log('Response #%s to the question "%s" was "%s"',
         (i + 1).toString(),
         itemResponse.getItem().getTitle(),
         itemResponse.getResponse());
   }
 }

//here we make another Form
  var nextForm = FormApp.create('itemResponses[0].getResponse()');
  
//here we make a section for the questions that apply to the Tool as a whole
  var generalSection = nextForm.addSectionHeaderItem();
  generalSection.setTitle(itemResponses[0].getResponse());

//here we give the general section a checkbox item
  var checkbox = nextForm.addCheckboxItem();
  checkbox.setTitle('Which platforms did you test?');
  checkbox.setChoices([
    checkbox.createChoice('Chrome'),
    checkbox.createChoice('FF'),
    checkbox.createChoice('Safari'),
    checkbox.createChoice('Vivaldi (Jokes)')
  ])
  checkbox.setRequired(true);
  
//here we give the general section a multiple choice question and make it required
  var generalLooks = nextForm.addMultipleChoiceItem()
    .setTitle('Does this campaign look good in general?')
    .setChoiceValues(['Yes','No'])
    .setRequired(true);

//here we give the general section a place for comments
  var generalComment = nextForm.addParagraphTextItem()
    .setTitle('General comments:')
    .setHelpText('Separate each comment with a return.')
    .setRequired(false);
  
//here we give the general section a place for images to be submitted
  var generalImg = nextForm.addParagraphTextItem()
    .setTitle('General comment reference image links:')
    .setHelpText('Separate each image link with a return.')
    .setRequired(false);
  
//here we create a new section to conatin all the parents
  var parentPage = nextForm.addPageBreakItem();
  parentPage.setTitle(itemResponses[0].getResponse() + '| Parent(s)');

//here we create an array to conatain all the parent names
  var parents = [{}];
//we populate this array with the responses to the second question of the "First QA Form" which asked for ther Parent names seperated by returns
  parents = itemResponses[1].getResponse().split("
");
  
//this for loop creates a section and series of questions related to each parent
  for (var p = 0; p < parents.length; p++) {
    
//adds a section for each parent
    var parentSection = nextForm.addSectionHeaderItem().setTitle(parents[p]);
    
//adds a yes or no question for each parent
    var parentLooks = nextForm.addMultipleChoiceItem()
    
    //sets the name of the question dynamically using the current parent
        .setTitle('Does ' + parents[p] + ' look good in general?')
        .setChoiceValues(['Yes','No'])
        .setRequired(true);
    
//adds a comment section for each
    var parentComment = nextForm.addParagraphTextItem()
        .setTitle(parents[p] + ' comments:')
        .setHelpText('Separate each comment with a return.')
        .setRequired(false);
    
    
//adds an img section for each (there is potential to get into regex here and verify links)(there is also potential to replace with apps script UI stuff for direct upload)
    var parentImg = nextForm.addParagraphTextItem()
        .setTitle(parents[p] + ' image links:')
        .setHelpText('Separate each image link with a return.')
        .setRequired(false);
  }
//end for loop

//makes a new page for the children
  var childPage = nextForm.addPageBreakItem();
  childPage.setTitle(itemResponses[0].getResponse() + '| Children');
  
  var children = [{}];
  children = itemResponses[2].getResponse().split("
");

//this for loop creates a section and series of questions related to each child
  for (var c = 0; c < children.length; c++) {
    var childSection = nextForm.addSectionHeaderItem().setTitle(children[c]);
    var parentSelect = nextForm.addListItem().setRequired(true);
    parentSelect.setTitle('Which parent does this child belong to?');
    parentSelect.setChoiceValues(parents);
    
//adds a yes or no question for each parent
    var childrenLooks = nextForm.addMultipleChoiceItem()
        .setTitle('Does ' + children[c] + ' look good in general?')
        .setChoiceValues(['Yes','No'])
        .setRequired(true);
    
//adds a comment section for each
    var childrenComment = nextForm.addParagraphTextItem()
        .setTitle(children[c] + ' comments:')
        .setHelpText('Separate each comment with a return.')
        .setRequired(false);
    
//adds an img section for each (there is potential to get into regex here and verify links)(there is also potential to replace with apps script UI stuff for direct upload)
    var childImg = nextForm.addParagraphTextItem()
        .setTitle(children[c] + ' image links:')
        .setHelpText('Separate each image link with a return.')
        .setRequired(false);
  }
//end for loop
  
//we need the email of the account manager we want this to go to after we fill it out
  var finalStep = nextForm.addSectionHeaderItem();
  finalStep.setTitle('Final Step');

//this is a response field that grabs the email of the account manager, it is required.
  var accountEmail = nextForm.addTextItem();
  accountEmail.setTitle('What is the email of this account manager?').setRequired(true);
  
//grabs the form we just made's ID
  var id = nextForm.getId();

//create the link that will be sent to the QAer to respond with content and images
  var emailBody = 'https://docs.google.com/a/***********.com/forms/d/' + id + '/viewform';
//set the email of the QAer
  var email = itemResponses[3].getResponse();
//set the subject of the email to the name of the Tool
  var emailSubject = itemResponses[0].getResponse();
  
//send the email of the link to the new form to the QAer
  MailApp.sendEmail({
    to: email,
    subject: emailSubject,
    htmlBody: emailBody});

Second Form Script

//set to be run on a submission event of the second form "Next QA Form" which resides in ********'s Drive
function onLastSubmit() {
 var form = FormApp.getActiveForm();
 var formResponses = form.getResponses();

//loop just puts the current responses into nested arrays
 for (var i = 0; i < formResponses.length; i++) {
   var formResponse = formResponses[i];
   var itemResponses = formResponse.getItemResponses();
   for (var j = 0; j < itemResponses.length; j++) {
     var itemResponse = itemResponses[j];
//     Logger.log('Response #%s to the question "%s" was "%s"',
//         (i + 1).toString(),
//         itemResponse.getItem().getTitle(),
//         itemResponse.getResponse());
   }
 }
  
//create a Form instance of our last(old) form. It will be usefull in accessing data like parent and child names
  var previousForm = FormApp.openById('***********************');
  var oldFormResponses = previousForm.getResponses();
  
//loop just puts the old responses into nested arrays
  for (var i = 0; i < oldFormResponses.length; i++) {
    var oldFormResponse = oldFormResponses[i];
    var oldItemResponses = oldFormResponse.getItemResponses();
    for (var j = 0; j < oldItemResponses.length; j++) {
      var oldItemResponse = oldItemResponses[j];
//      Logger.log('Response #%s to the question "%s" was "%s"',
//          (i + 1).toString(),
//          oldItemResponse.getItem().getTitle(),
//          oldItemResponse.getResponse());
    }
  }

//some debugging and such
  Logger.log(oldItemResponses[0].getResponse());
  Logger.log(itemResponses[4].getResponse());
  
//oldItemResponses[0] = Name of Tool
  var toolName = oldItemResponses[0].getResponse();
  Logger.log(toolName);
//oldItemResponses[1] = parent names
  var parentNames = oldItemResponses[1].getResponse();
  Logger.log(parentNames);
//oldItemResponses[2] = child names
  var childNames = oldItemResponses[2].getResponse();
  Logger.log(childNames);
//oldItemResponses[3] = email of the QAer
  var qaEmail = oldItemResponses[3].getResponse();
//newItemResponse[0] = tested platforms
  var testedPlatforms = itemResponses[0].getResponse();
  
//make the last form
  var lastForm = FormApp.create('Account Manager Response | ' + toolName);
  
//make a section for the general content
  var generalSection = lastForm.addSectionHeaderItem();
  generalSection.setTitle(toolName + ' | General Section');
  
//make a checkbox item for the CD to approve each of the platforms that the QAer says were tested
  var testedCheckbox = lastForm.addCheckboxItem();
  testedCheckbox.setTitle('If you agree a platform was accurately tested please check it off below.');
//use the array from the first response of the previous form (platforms that were tested) to generate a list of the tested platforms for the CD to approve
  if ( Array.isArray(testedPlatforms)) {
    testedCheckbox.setChoiceValues(testedPlatforms);
  } else {
    testedCheckbox.createChoice(testedPlatforms);
  }
  
//set general section response variables
  var genYesNo = itemResponses[1].getResponse();
  var genComments = itemResponses[2].getResponse();
  var genImgs = itemResponses[3].getResponse();
  
  
//if statement either says the general section looks good or makes a bunch of fields with the content the QAer left
  if ( genYesNo == 'Yes') {
    generalSection.setHelpText('Looks Good!')
  } else {
    
  //make a checkbox item for the CD to approve or not approve the general section QA feedback
    if ( genComments != '') {
      var generalCheckbox = lastForm.addCheckboxItem();
      generalCheckbox.setTitle(toolName + ' | General Information and Comments');
      generalCheckbox.setHelpText('Please check the boxes that you have fixed. Feel free to leave a note about any in the following section.');
      
      if ( Array.isArray(genComments)) {
        generalCheckbox.setChoiceValues(genComments);
      } else {
        generalCheckbox.createChoice(genComments);
      }
    }
    
  //create a for loop to display image items for any linked images that were included by the QAer in the general section
    if ( genImgs != '') {
      if ( Array.isArray(genImgs)){
        for (var gI = 0; gI < genImgs.length; gI++) {
          var generalImg = lastForm.addImageItem();
          generalImg.setTitle('General Section | Image ' + (gI + 1));
          var genImg = UrlFetchApp.fetch(genImgs[gI]);
          generalImg.setImage(genImg);
        }
      } else {
        var generalImg = lastForm.addImageItem();
        generalImg.setTitle('General Section | Image 1');
        var genImg = UrlFetchApp.fetch(genImgs);
        generalImg.setImage(genImg);
      }
    }
  }

//make a paragraphTextItem for the CD to leave notes about this section if they would like
  var generalNotes = lastForm.addParagraphTextItem()
    .setTitle('Notes about the general section:')
    .setHelpText('Leave notes here about any items you have not fixed and other things you would like the QAer to know.');
  
//make a new page for the parent content
  var parentPage = lastForm.addPageBreakItem();
  parentPage.setTitle(toolName + ' | Parent(s)');
  
//a variable that we can increment by 2 to account for there being 3 items in each parent
  var incParent = 0;

//a loop that creates items for each parent including: new section, checkbox to approve content and image displays
  for (var i = 0; i < parentNames.length; i++) {
    var parYesNo = itemResponses[(i + incParent) + 5].getResponse();
    var parComments = itemResponses[(i + incParent) + 5].getResponse();
    var parImgs = itemResponses[(i + incParent) + 5].getResponse();
    
  //create the new section for each parent
    var parentSection = lastForm.addSectionHeaderItem();
  //and name it
    parentSection.setTitle(parentNames[i]);
    
  //if statement to ensure we dont show any content if the QAer checked 'Yes' for looks good
  //using incOne to ensure 
    if (parYesNo == 'Yes') {
      parentSection.setHelpText('Looks Good!');
    } else { 
    
    //create a checkbox list for all the comments the QAer listed if they clicked 'No' for looks good
      if (parComments != '') {
        var parentCheckbox = lastForm.addCheckboxItem();
        parentCheckbox.setTitle(parentNames[i] + ' | QA Comments');
        parentCheckbox.setHelpText('Please check the boxes that you have fixed. Feel free to leave a note about any in the following section.');
        
        if (Array.isArray(parComments)) {
          parentCheckbox.setChoiceValues(parComments);
        } else {
          parentCheckbox.createChoice(parComments)
        }
      }
    }
    
  //create the images the QAer listed if they clicked 'No' for looks good
    if (parImgs != '') {
      if (Array.isArray(parImgs)) {
        for (var pI = 0; gI < parImgs.length; pI++) {
          var parentImg = lastForm.addImageItem();
          parentImg.setTitle(parentNames[i] + ' | Image ' + (pI + 1));
          var parImg = UrlFetchApp.fetch(parImgs[pI]);
          parentImg.setImage(parImg);
        }
      } else {
        var parentImg = lastForm.addImageItem();
        parentImg.setTitle(parentNames[i] + ' | Image ');
        var parImg = UrlFetchApp.fetch(parImgs[pI]);
        parentImg.setImage(parImg)
      }
    }
    
  //increment to account for the other items in each parent
    incParent += 2;
  }
  //end for loop
  
//make a new page for the children content
  var childPage = lastForm.addPageBreakItem();
  childPage.setTitle(toolName + ' | Children');
  
//determine how many parents there are and count three items for each
//also account for the items from the general section (4 items)
  var parentItems = parentNames.length * 3;
  var nonChildItems = parentItems + 4;
  
//a variable that we can increment by 4(the number of items in each child)
  var incChild = 0;
  
  //creates items for each parent including: checkbox to approve content and image displays
  for (var j = 0; j < childNames.length; j++) {
    var chiYesNo = itemResponses[nonChildItems + (j + incChild)].getResponse();
    var chiComments = itemResponses[(j + incChild) + nonChildItems].getResponse();
    var chiImgs = itemResponses[(j + incChild) + nonChildItems].getResponse();
    
    //create sections for each child
    var childSection = lastForm.addSectionHeaderItem();
    childSection.setTitle(childNames[j] + ' | ' + itemResponses[nonChildItems + (j + incChild + 1)].getResponse());
    
    if (chiYesNo == 'Yes') {
      childSection.setHelpText('Looks Good!');
    } else {
      
    //create a checkbox list for all the comments the QAer listed if they clicked 'No' for looks good
      if (chiComments != '') {
        
        var childCheckbox = lastForm.addCheckboxItem();
        childCheckbox.setTitle(childNames[j] + ' | QA Comments');
        childCheckbox.setHelpText('Please check the boxes that you have fixed. Feel free to leave a note about any in the following section.');
        
        if (Array.isArray(chiComments)) {
          childCheckbox.setChoiceValues(chiComments);
        } else {
          childCheckbox.createChoice(chiComments);
        }
      }
    }
    
  //create the images the QAer listed if they clicked 'No' for looks good
    if (chiImgs != '') {
      if (Array.isArray(chiImgs)) {
        for (var cI = 0; cI < chiImgs.length; cI++) {
          var childImg = lastForm.addImageItem();
          childImg.setTitle(childNames[j] + ' | Image ' + (cI + 1));
          var chiImg = UrlFetchApp.fetch(chiImgs[cI]);
          childImg.setImage(chiImg);
        }
      } else {
        var childImg = lastForm.addImageItem();
        childImg.setTitle(childNames[j] + ' | Image ');
        var chiImg = UrlFetchApp.fetch(chiImgs[cI]);
        childImg.setImage(chiImg);
      }
    }
    
  //increment to account for the other items in each child
    incChild += 3;
  }
//end for loop
  
//grabs the form we just made's ID
  var id = lastForm.getId();

//create the link that will be sent to the QAer to respond with content and images
  var emailBody = 'https://docs.google.com/a/**************.com/forms/d/' + id + '/viewform';
//set the email of the QAer
  var email = qaEmail;
//set the subject of the email to the name of the Tool
  var emailSubject = toolName + ' | CD Response';
  
//send the email of the link to the new form to the CD
  MailApp.sendEmail({
    to: email,
    subject: emailSubject,
    htmlBody: emailBody});
}

Thanks in advance!

*edit for company privacy reasons.

phollingdrake · Accepted Answer

I've been doing something very similar and have mostly been successful. I was able to ensure code is moved over to newly created forms by creating a blank template form, with the necessary script attached. When when a new form is needed with this script, I create a copy of the template document and then populate this with the necessary contents.

The only problem I have run into with this is being unable to easily set up triggers for code to run on form submission in these new forms. I have solved this by prompting the user to open the newly created form and click on a menu item I have added to 'initialise permissions'.

Using Google Apps Script to make Google Forms that have scripts already attached

Context

Problem

Code:

Answers (2)

Related Questions