从Google表单下拉列表中删除选定的项目 [英] remove selected items from google form dropdown list

问题描述

几天来我一直在努力使它工作，我放弃了.

I have been trying to get this to work for a couple of days now and I give up.

我想创建一个Google表单，其中包含一个从电子表格填充的下拉列表.我没有什么可以和其他任何人一样选择的. (就像在碰碰运气的情况下一样)

I want to create a Google form with a drop down list populated form a spreadsheet. I don't what anyone to choose the same as any one else. (like in a potluck situation)

示例:

• 我要放弃:
  
  • 梳子
  • 刷子
  • 盛满糊糊的碗
  • I am giving away :
    
    • a comb
    • a brush
    • a bowl full of mush

    我告诉托马斯(Thomas)，理查德(Richard)和亨利(Henry)，他们每个人都可以拥有一个，并向他们发送指向我创建的Google表单的链接.汤姆很快，打开了表格1st.他输入自己的名字，然后从三项下拉列表中选择一个梳子. Dick打开了表单链接，在同一个下拉问题中，他从剩余的两个项目中选择了一个.他选择刷子.哈利p了一下，所以当他回到家时，他打开了我的链接，但是，he，他只能盛满一碗蘑菇.

    I tell Thomas, Richard and Henry that they can each have one and send them a link to a Google form I created. Tom is quick and opens the form 1st. He enters his name and chooses a comb out of a three item drop down list. Dick opens the form link and in the same drop down question he chooses out of the two remaining items. He chooses the brush. Harry is a bit of a slow poke, so when he gets home he opens my link, but alas, he can only have a bowl full of mush.

    如何完成此操作?
    到目前为止，根据我的研究，我将需要在响应电子表格中使用if函数，以查看是否有某个项目的订单(查看单元格是否空缺)，也许还有VLOOKUP，但我无法获得关于如何使它们全部协同工作的清晰图片.
    谢谢
    晚安

    How can I get this done?
    Based on my research so far I will be needing to use the if function on the responses spread sheet to see if there has been a take for an item (see if the cell is vacant) and maybe VLOOKUP, but I can't get a clear picture of how to make it all work together.
    Thank you,
    Good night

    基于gssi的回答，我想发布代码并描述我的操作方式.

    Based on gssi's answer, I wanted to post the code and describe the way I did it.

    function updateListChoices(item){
      var inventory = (SpreadsheetApp.openById(theIdOfTheResponceSpreadsheet)
                  .getSheetByName("inventory")
                  .getDataRange()
                  .getValues());
      var selected = (SpreadsheetApp.openById("0Al-3LXunCqgodHB5RGNpR0RyQ0pERmVnek1JeUJKS0E")
                  .getSheetByName("responses")
                  .getDataRange()
                  .getValues());
    
      var choices = [];
      var selectedReal = [];
      for (var i = 0; i< selected.length; i+=1){
     selectedReal.push(selected[i][2]) }
      for (var i = 1; i< inventory.length; i+=1){
        if(selectedReal.indexOf(inventory[i][0])=== -1){
          choices.push(item.createChoice(inventory[i][0]));}
      }
      item.setChoices(choices);
    }
    
    var LIST_DATA = [{title:"the title of the question", sheet:"inventory"}]
    function updateLists() {
      var form = FormApp.getActiveForm();
      var items = form.getItems();
      for (var i = 0; i < items.length; i += 1){
        for (var j = 0; j < LIST_DATA.length; j+=1) {
          var item = items[i]
          if (item.getIndex() === 1){
            updateListChoices(item.asListItem(), "inventory");
            break;
          }
        }
      }
    }
    

    在表单的构建中，单击tools菜单，然后单击script editor.将代码从此处(根据需要进行更改)复制到脚本编辑器，然后单击保存.单击Resources菜单，然后单击project triggers(第一个选项).单击Add trigger.选择updateLists from form发送一次，打开一次(您应该以2行结尾).

    In the building of the form, click the tools menu, then click script editor. Copy the code from here (with changes to fit your needs) to the script editor and hit save. Click the Resources menu and hit the project triggers (the 1st option). Click Add trigger. Choose updateLists from form do this once with when sending and once when opening (you should end up with 2 lines.)

    它不是很优雅，但这就是我所能做到的.祝你好运.

    It isn't very elegant, but this is what I am capable of. Good Luck.

    推荐答案

    我试图完成完全相同的操作(列出要选择的产品)，但是我无法使其与您的最终代码示例一起使用.这是我的，带有详细说明.仅适用于登陆此页面并正在寻找有效代码的任何人.

    I tried to accomplish exactly the same (list with products to select from), but I couldn't make it work with your final code example. Here's mine, with detailed instructions. Just for anybody who is landing on this page and is looking for a working code.

    (菜单名称可能与您的菜单名称有所不同，因为我使用的是非英语的Google表单，而我只是在这里猜测翻译的意思.)

    (Menu names might differ from yours, because I'm using a non-English Google Forms, and I'm just guessing the translations here.)

    1)创建一个新表单，并创建一个新的基于单选按钮的问题(多项选择)(在本示例中，我使用问题名称:选择产品").不要添加任何选项.保存.

    1) Create a new form, and create a new radio button based question (multiple choice) (In this example, I use the question name: "Select a product"). Don't add any options to it. Save it.

    2)打开要在其中存储响应的电子表格，并在其中添加新的工作表(名称:库存")

    2) Open the spreadsheet where the responses are going to be stored, and add a new sheet in it (name: "inventory")

    3)修复清单的第一行(A)，并在A1中输入选择产品"

    3) Fix the first row (A) of the inventory sheet, and put in A1: "Select a product"

    4)在A列中输入要显示在表单中的所有产品

    4) Put in column A all the products you want to appear in the form

    5)再次打开表单编辑器，然后转到工具>脚本编辑器

    5) Open the form editor again, and go to tools > script editor

    6)将此代码粘贴到编辑器中，放入表单和电子表格ID的(3x)并保存.

    6) Paste this code in the editor, put in your form and spreadsheet ID's (3x) and save it.

    var LIST_DATA = [{title:"Select a product", sheet:"inventory"}];
    
    function updateLists() {
      //var form = FormApp.getActiveForm();
      var form = FormApp.openById("paste_ID_of_your_FORM_here");
      var items = form.getItems();
      for (var i = 0; i < items.length; i += 1){
        for (var j = 0; j < LIST_DATA.length; j+=1) {
          var item = items[i];
    
          if (item.getTitle() === LIST_DATA[0].title){
            updateListChoices(item.asMultipleChoiceItem(), LIST_DATA[0].sheet);
            break;
        }
        }
      }
    }
    
    function updateListChoices(item, sheetName){
      var inventory = (SpreadsheetApp.openById("paste_ID_of_your_RESPONSE_SHEET_here")
                  .getSheetByName("inventory")
                  .getDataRange()
                  .getValues());
      var selected = (SpreadsheetApp.openById("paste_ID_of_your_RESPONSE_SHEET_here")
                  .getSheetByName("responses")
                  .getDataRange()
                  .getValues());
    
      var choices = [];
      var selectedReal = [];
      for (var i = 0; i< selected.length; i+=1){
         selectedReal.push(selected[i][1]) 
      }
      for (var i = 1; i< inventory.length; i+=1){
        if(selectedReal.indexOf(inventory[i][0])=== -1){
          choices.push(item.createChoice(inventory[i][0]));}
      }
      if (choices.length < 1) {
        var form = FormApp.getActiveForm();
        form.setAcceptingResponses(false); 
      } else {
      item.setChoices(choices); 
      }
    }
    

    7)在代码编辑器打开的情况下，转到资源">创建触发器"并创建这两个触发器.它们需要按以下顺序出现:

    7) With the code editor open, go to Resources > Create triggers and create these two triggers. They need to appear in this order:

    1. updateLists-从表单-发送
    2. updateLists-从表单-打开

    现在你很好.如果打开表单编辑器，则库存表中添加的产品将作为选项出现.

    Now you're good to go. If you open the form editor, the products added in the inventory sheet will appear as options.

    每次选择产品时，它都会从表格中消失.要重置所有选择的产品，请转到表单编辑器，然后选择响应">删除所有响应".您可能还需要手动从响应"表中删除所有响应(不知道为什么，但这发生在我身上).之后，您需要在代码编辑器中手动运行updateLists脚本.

    Every time a product is chosen, it will disappear from the form. To reset all the chosen products, go to the form editor, and choose Responses > Remove all responses. You might need to remove all responses from the responses sheet manually as well (Don't know why, but that happened to me). After that, you need to manually run the updateLists script in the code editor.

    这篇关于从Google表单下拉列表中删除选定的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！