javascript - 使用 Google Apps 脚本制作已附加脚本的 Google 表单

Question

我正在寻找解决方案来解决我在将 Google Apps 脚本应用于(特别是 Google 表单产品)时遇到的新问题。

上下文

我工作的公司目前通过电子邮件发送反馈，对我们为客户创建的软件执行质量保证 (QA)。

这个软件是由“ parent ”和他们的“ child ”组成的。我被要求考虑使用 Google 表单作为为每个创建的软件创建 QA 反馈的方法。

利用 Google Apps 脚本文档，我在这个过程中取得了很大进展。然而，在实际实现时，我遇到了知识障碍。

问题

我有一个脚本附加到一个非常基本的表单上，该表单要求提供该软件的工具名称(我们如何跟踪 QA 请求)、家长姓名以及 child 的姓名。 (目前，为了方便起见，我也要求提供电子邮件，但很快将替换为 google apps 脚本具有的自动电子邮件抓取功能)。

此脚本接收对第一个表单的响应，并使用响应创建一个新表单。现在，出于构建目的，我在通过提交第一个表单创建的表单的脚本编辑器中创建了第二个 google apps 脚本。该脚本接受对第二种形式的响应并创建第三种形式(我知道，“形式感知”，对吗？)。

在构建完所有这些并对我的结果相当满意之后，我意识到我的想法存在巨大错误。除了测试目的之外，用户将从第一个表单中创建许多新表单。这些新表单中的每一个都不会具有我为第二个表单创建的与它们关联的 google apps 脚本，因此不会生成所需的第三个表单。

我知道正在寻求帮助来确定一种方法，该方法可以让我为第二个表单编写的代码自动添加到第一个表单创建的每个新表单中。如果这是不可能的，我正在寻找任何替代方案。我已经考虑过在第一个代码库中包含第二个谷歌应用程序脚本的方法，但我找不到在第一个脚本中提交第二个表单时触发该函数的方法。任何需要考虑的想法或方法将非常感激。

代码:

作为注释；我确实意识到这段代码有点困惑而且非常多余。这是作为一个简短的概念证明而拼凑在一起的。如果我能找到上述问题的解决方案，我计划将其清理并模块化。不过，在浪费时间之前，我想确定我想要做的事情在 Google Apps 脚本的限制内是否可行。

第一个脚本

//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("\n");
  
//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("\n");

//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});

第二形式脚本

//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});
}

提前致谢!

*出于公司隐私原因进行编辑。

Answer 1

我一直在做一些非常类似的事情并且大部分都取得了成功。通过创建一个空白模板表单并附加必要的脚本，我能够确保代码转移到新创建的表单中。当此脚本需要新表单时，我会创建模板文档的副本，然后用必要的内容填充该副本。

我遇到的唯一问题是无法轻松设置触发器以在这些新表单中提交表单时运行代码。我通过提示用户打开新创建的表单并单击我添加到“初始化权限”的菜单项解决了这个问题。