从谷歌表单下拉列表中删除所选项目 [英] 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)

示例:

• 我要送人了:
  
  • 梳子
  • 刷子
  • 一碗糊状物

  我告诉 Thomas、Richard 和 Henry，他们每个人都可以拥有一个，并向他们发送指向我创建的 Google 表单的链接.汤姆很快就打开了第一张表格.他输入他的名字并从三项下拉列表中选择一个梳子.迪克打开表单链接，并在同一个下拉问题中从剩余的两个项目中选择.他选择画笔.哈利有点慢热，所以他回家后打开了我的链接，可惜他只能吃一碗糊糊.

  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(第一个选项).点击添加触发器.选择 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) 打开要存储响应的电子表格，并在其中添加一个新工作表(名称:inventory")

  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.

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