如何使用Google表单或Google表格上的GAS限制基于两个ID的Google表单提交 [英] How to restrict google forms submission based on two ID using GAS on Google Form or Google Sheets

问题描述

我正在研究Google表格查询，该表格收集参与者对某个问题的投票.我想根据ID号限制参与者.我在想三种方法:

I'm working on a query Google Form which collects participants' votes to a certain question. I want to restrict participants based on the ID number. I was thinking of three approaches:

1. 如果输入的ID不在给定列表中，则防止提交表单. (我更喜欢这种方法，但到目前为止找不到任何有用的代码)

1. Prevent form submission if the entered ID is not on the given list. (I prefer this approach but couldn't find any useful code for it so far)

删除链接的响应电子表格中的行.这是我的无效代码:

Delete rows in the linked responses spreadsheet after form submission using GAS on Google Form through onFormSubmit trigger. Here is my code which is not working:

function onFormSubmit(e) {
 // Grab the session data again so that we can match it to the user's choices.
var response = [];
var values = SpreadsheetApp.openById('1rT9tKAi6ZSvZzBaXNSPMJAt4RKnW- 
 9lqiE9zvZV').getDataRange().getValues();

for (var i = 1; i < values.length; i++) {
  var indiv = values[I];
  var Fname = indiv[0];
  var Lname = indiv[1];
  var ID1 = indiv[2];
  var ID2 = indiv[3];

  // For every selection in the response, find the matching ID1 and title
  // in the spreadsheet and add the session data to the response array.
  if (e.namedValues[ID1] == ID1) {
    response.push(indiv);
  } else {
  Browser.msgBox('Your ID number does not matches the list');
  }
}

提交表单后，通过onChange触发器在Google表格上使用 GAS

删除链接的响应电子表格中的行.这是我的最大努力:

Delete rows in the linked responses spreadsheet after form submission using GAS on Google Sheets through onChange trigger. Here is my best effort:

function onChange(e) {  
 var refvalues = 
 SpreadsheetApp.getActive().getSheetByName('members_sheet').getDataRange().getValues();
 var  sheet = SpreadsheetApp.getActive().getSheetByName('Form Responses 1');
 var  values = sheet.getDataRange().getValues();

 var indiv = values[values.length];
 var ID1 = indiv[2];
 var flag = 0;
  for (var i = 1; i < refvalues.length; i++) {
      var refindiv = refvalues[i];
      var refID1 = refindiv[2];
    if (ID1 == refID1) {
      flag = 1;
    }
 } 

 if (flag == 0) {
   sheet.deleteRow(values.length); 
 }
 };

我对Javascript编码完全陌生，因此不胜感激.

I'm totally new in Javascript coding so any help would be appreciated.

//--------------------------------------------- --------------------------------//

//-----------------------------------------------------------------------------//

感谢ziganotschka的回答，我将代码更新为:

Thanks to the ziganotschka answer I update my code to this:

function makeMultiForm() {
  var form = FormApp.create('Nazar Sanji')
                .setConfirmationMessage('Thank you! Your Vote have been 
    recorded');
  form.setTitle("Query");

  var ss = SpreadsheetApp.openById('1rT9tKAi6ZSvZzBaXNSPMJAt4RKnW- 
   9lqiE9zvZV5JJk');
  var ID1List = 
    ss.getSheetByName('members_sheet').getRange('C2:C4').getValues();//Ex [123 ; 555]
  var ID2List = 
    ss.getSheetByName('members_sheet').getRange('D2:D4').getValues();//Ex [aa ; bb]

  // Ex passwords: asd, 123, asd123
  const condition1 = ID1List.map(element => `${element}`).join('|')

  var IDarray =[];
  //Add items to IDarray Ex [123aa ; 555bb]
    for(var i=0; i<ID1List.length; i++){
        IDarray[i] = [ID1List[i][0]+ID2List[i][0]];
    }
  const condition2 = IDarray.map(element => `${element}`).join('|')

  // Start by laying out the bare-bones structure.  This defines the different
  // sections, and the bare widgets in each section.
  // Note that you can't add any flow-routing details at this point, because
  // the destinations most likely haven't been defined yet

  var itemFName = form.addTextItem().setTitle('First Name').setRequired(true);
  var itemLName = form.addTextItem().setTitle('Last Name').setRequired(true);
  var itemID1   = form.addTextItem().setTitle('First ID').setRequired(true);

  // Create valid ation for this question matching the ID1(ID Melli) that we got from the sheet
  var ID1Validation = FormApp.createTextValidation()
.setHelpText('Enter a Valid First ID')
.requireTextMatchesPattern(condition1)
.build();
  itemID1.setValidation(ID1Validation);

  //var sectID2 = form.addPageBreakItem().setTitle("Second ID");
  var itemID2 = form.addTextItem().setTitle('Second ID').setRequired(true);

  // Create valid ation for this question matching the ID2(ID Shenasnameh) that we got from the sheet
  var ID2Validation = FormApp.createTextValidation()
.setHelpText('Second ID does not match the First ID')
.requireTextMatchesPattern(condition2)
.build();
  itemID2.setValidation(ID2Validation);


  var sectVote = form.addPageBreakItem().setTitle("Final Vote");
  var VoteOptions = form.addMultipleChoiceItem().setTitle("Which Competition");
  VoteOptions.setChoices([
  VoteOptions.createChoice("Option 1"),
  VoteOptions.createChoice("Option 2")]);

}

最近的问题是有关ID2validation的.由于 condition2 是两个ID号的串联，因此参与者必须在Google表单的最后一个文本项中输入他/她的合并ID(密码)，这是不正确的. (例如"123aa")

The recent issue is on ID2validation. Since condition2 is the concatenation of two ID numbers, the participant has to enter his/her merged ID's (passwords) in the last text item in the Google Form which is not correct. (Ex. '123aa')

我该如何解决?

推荐答案

如果输入的ID不在给定列表中，则防止提交表单

• 最简单的方法是合并文本验证，您甚至不需要为此进行编码
• 在构建/编辑ID1问题Regular expression，matches时只需选择并指定所有允许使用|作为分隔符提交表单的ID

Prevent form submission if the entered ID is not on the given list

• The easiest way would be to incorporate text validation, you do not even need to code for it
• Just chose when building / editing the ID1 question Regular expression, matches and specify all IDs that shall be allowed to submit the form using | as separator

更多信息

• 如果您有动力以编程方式合并文本验证，请查看 此处 和此处
• 如果您希望使用已有的代码来删除行，则可以将脚本附加到表单上，也可以将脚本附加到表单上 电子表格，在两种情况下，您都可以并且应该使用触发器 onFormSubmit(不是onChange！)
• 从表单提交表中删除行是行不通的，它们会在下一次提交表单时返回
• 将onFormSubmit具有正确ID的行复制到辅助工作表上是可行的，但比使用文本验证更为复杂

• If you feel motivated to incorporated the text validation programmatically, have a look here and here
• If you prefer to work on your already existing code to delete rows - it does not matter either you attach the script to the form or the spreadsheet, in both cases you can and should use the trigger onFormSubmit (not onChange!)
• Deleting rows from the form submission sheet will not work - they come back at the next form submission
• Copying onFormSubmit the rows with the right ID to a secondary sheet can work, but it more complicated than using text validation

这篇关于如何使用Google表单或Google表格上的GAS限制基于两个ID的Google表单提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！