Czy istnieje sposób na powiązanie pytania w stylu „Wybierz z listy” w Formularzu Google z listą danych (np. W arkuszu kalkulacyjnym)?

Czy istnieje sposób połączenia pytania w formie „Wybierz z listy” w formularzu z listą danych (np. W arkuszu kalkulacyjnym)?

Oznaczałoby to, że ponieważ nowe opcje zostały dodane do listy w arkuszu kalkulacyjnym (lub gdziekolwiek możliwe byłoby hostowanie listy), pojawią się one automatycznie jako opcja do wyboru w formularzu w przyszłości.

W moim przypadku oznaczałoby to, że respondenci wypełniający formularz będą mogli wybrać, którzy wolontariusze byli obecni na konkretnej sesji wolontariackiej, z pełnej listy wolontariuszy (zamiast wpisywać każde nazwisko indywidualnie), ale ponieważ wolontariusze dołączają się sporadycznie, byłoby bardzo pomocne, gdyby można było dodać je automatycznie do opcji na liście zamiast dodawać nową opcję ręcznie za każdym razem.

Nie mam pojęcia, czy jest to możliwe, ale byłbym bardzo wdzięczny, gdyby ktoś mógł zasugerować sposób na zrobienie tego.

Odpowiedź Toma Horwooda działa świetnie, ale tylko po skorygowaniu ważnego błędu w jego kodzie. Wszystkie odniesienia do LIST_DATAelementów powinny być dokonywane przy użyciu indeksu j(nie i). Nie mam wystarczającej karmy, aby komentować post Toma, więc oto w pełni monty:

Poniżej znajduje się poprawiony kod. Zmieniłem również nazwę LIST_DATAelementów, aby były nieco bardziej opisowe. Pokazuje także, jak Tom myślał naprzód (kudos!) I zapewnił możliwość aktualizacji więcej niż jednego elementu formularza zawartością więcej niż jednej listy arkuszy kalkulacyjnych.

var FORMID = "PUT_YOUR_FORM_ID_HERE";
var LIST_DATA = [{formFieldTitle:"Job Number", worksheetName:"JobNumbers"},
             {formFieldTitle:"Task 1 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 2 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 3 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 4 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 5 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 6 Category", worksheetName:"TaskCategories"},
            ];

function onOpen(e){
  var menuEntries = [];
  menuEntries.push({name: "Update Lists", functionName: "updateLists"});
  SpreadsheetApp.getActiveSpreadsheet().addMenu("List Updater", menuEntries)
};

function updateLists() {
  var form = FormApp.openById(FORMID);
  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[j].formFieldTitle){
        updateListChoices(item.asListItem(), LIST_DATA[j].worksheetName);
        break;
      }
    }
  }
}

function updateListChoices(item, sheetName){
  var data = (SpreadsheetApp.getActiveSpreadsheet()
              .getSheetByName(sheetName)
              .getDataRange()
              .getValues());
  var choices = [];
  for (var i = 0; i < data.length; i+=1){
    choices.push(item.createChoice(data[i][0]));
  }
  item.setChoices(choices);
}

Podsumowując, jak to zrobić (dla takich, jak ja, którzy używają Google Apps Script po raz pierwszy). Odniosłem się do nazw arkuszy i nazw pól formularza w powyższym fragmencie kodu, aby, mam nadzieję, uczynić go bardziej zrozumiałym:

1. W arkuszu kalkulacyjnym powiązanym z formularzem:

   • Utwórz nowy arkusz. Będzie zawierać listę elementów, które chcesz dodać do pola. Nadaj arkuszowi roboczemu odpowiednią nazwę (na przykład TaskCategories). Umieść tam swoją listę elementów w pierwszej kolumnie tego arkusza. Usuń wszystkie dodatkowe kolumny i wiersze z tego arkusza (może to być konieczne lub niepotrzebne - nie testowałem)
   • Z menu Narzędzia wybierz „Edytor skryptów”. Skopiuj i wklej powyższy fragment kodu do edytora skryptów. Musisz zmienić następujące części skryptu:
     • Wartość zmiennej FORMIDnależy zmienić na identyfikator formularza. Identyfikator to długi kod (między ukośnikami) w pasku adresu URL w przeglądarce dla żądanego formularza.
     • LIST_DATAZmienna będzie musiał zostać zmodyfikowany tak, aby dopasować swoją formę i potrzeb. Zauważysz, że każdy element LIST_DATAto krotka formFieldTitlei worksheetName. Pierwsza to nazwa pola formularza (musi to być pole typu „wybierz z listy”) - w edytorze formularzy nazywa się to Question Title. Ten ostatni to nazwa utworzonego wcześniej arkusza zawierającego listę elementów, które chcesz wypełnić w polu. Dodaj tyle krotek do listy, ile potrzebujesz.
     • Teraz zapisz skrypt (kliknij ikonę zapisu)
     • Przetestuj skrypt, wybierając „Uruchom” z menu edytora skryptów i wybierając „updateLists”. Gdy to zrobisz po raz pierwszy, poprosi o pozwolenie. Jeśli skrypt się powiedzie, możesz zauważyć, że w formularzu są teraz wypełnione określone pola, w przeciwnym razie na ekranie pojawi się komunikat o błędzie.

2. Jeśli wrócisz do arkusza kalkulacyjnego, zobaczysz nowy element menu zatytułowany List Updater. Zawiera jeden element, Update Listsktóry musisz uruchamiać za każdym razem, gdy zmieniasz arkusz (arkusze) listy - odpowiednio zaktualizuje formularz.

Zwracam również uwagę, z korzyścią dla czytelników, że FormRanger nie działa z nowymi Arkuszami kalkulacyjnymi Google. Programiści FormRanger mówią tyle samo na swojej stronie internetowej. Być może / mam nadzieję, że to się zmieni w przyszłości, ale w chwili opublikowania powyższego kodu działa dla mnie i używam go w formie wdrożonej dla klienta.

Możesz to zrobić za pomocą zwykłego formularza i skryptu, używając Form.getItems (), znajdź element w pętli, a następnie Item.asListItem (). SetChoices (...)

Oto przykładowy kod, którego użyłem w tym przykładowym arkuszu i formularzu

var FORMID = "YOUR FORM ID HERE";
var LIST_DATA = [{title:"Who's your tutor", sheet:"TutorName"}]

function onOpen(e){
  var menuEntries = [];
  menuEntries.push({name: "Update Lists", functionName: "updateLists"});
  SpreadsheetApp.getActiveSpreadsheet().addMenu("List Updater", menuEntries)
}

function updateLists() {
  var form = FormApp.openById(FORMID);
  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[j].title){
        updateListChoices(item.asListItem(), LIST_DATA[j].sheet);
        break;
      }
    }
  }
}

function updateListChoices(item, sheetName){
  var data = (SpreadsheetApp.getActiveSpreadsheet()
              .getSheetByName(sheetName)
              .getDataRange()
              .getValues());
  var choices = [];
  // If your sheets have headers, change i = 0 to i = 1
  for (var i = 0; i < data.length; i+=1){
    choices.push(item.createChoice(data[i][0]));
  }
  item.setChoices(choices);
}

Istnieje dodatek do Formularzy Google o nazwie Form Values, który przynajmniej oferuje półautomatyczny sposób: za każdym razem, gdy zmieniasz swoje odpowiedzi w arkuszu kalkulacyjnym, musisz ręcznie ponownie wypełnić pola wyboru w odpowiedzi, ale przynajmniej jest to dość proste .

Nie możesz tego zrobić za pomocą zwykłych Formularzy Google.

Możesz jednak utworzyć własny interfejs GUI za pomocą Google Apps Script , a zwłaszcza UIService . Pamiętaj jednak, że będziesz musiał ponownie utworzyć cały GUI - nie możesz używać części z istniejącego formularza.

Alternatywnie możesz użyć Konstruktora GUI . Jest to edytor WYSIWYG dla GUI Google Apps Script.

Którykolwiek wybierzesz, powinieneś być przygotowany na programowanie, aby osiągnąć to, czego chcesz.

Jest na to skrypt o nazwie FormRanger: https://docs.google.com/document/d/1YvgrxeZJNb2CBpSra1j59Mh1bVpk4vBQYLOoyVjby7A/edit?hl=pl&forcehl=1

Istnieje sposób na zautomatyzowanie aktualizacji listy poprzez ustawienie Trigger [ 1 ] [ 2 ] dla Update Lists.

Ścieżka to Edytor skryptów -> Zasoby -> Wyzwalacze bieżącego projektu

Potem add a new trigger. Dla podanych przykładów możesz ustawić updatesListuruchamianie from spreadsheeti On form submission. W ten sposób funkcja onOpennie będzie potrzebna.

Tak, można to zrobić, spędziłem sporą część dnia na znalezieniu odpowiedzi, a następnie na jej opracowaniu dla moich celów, ale można to zrobić.

Potrzebowałem formularza rejestracyjnego dla wolontariuszy, aby ludzie mogli zaznaczyć wiele pól z listy obszarów, w których chcieliby zostać wolontariuszami. Dlatego jedno pytanie z wieloma polami, problem polega na tym, że wynikowy arkusz kalkulacyjny Google wyświetlał wszystko, co sprawdził w jednej komórce. Było 21 opcji dla wolontariuszy, więc najwyraźniej nie byłby to przydatny format dla tych informacji. Chciałem zrobić to, co zrozumiałem, że powiedziałeś, że chcesz, chciałem mieć kolumnę z każdą z 21 opcji we własnej kolumnie. Następnie obok znacznika czasu (wygenerowanego formularza), nazwiska, adresu e-mail i telefonu znajduje się 21 kolumn z „tak” lub pozostawionych pustych. Nagłówek kolumny to każda opcja, którą mogą wybrać. Niektórzy mogą się zastanawiać, dlaczego nie mieć tylko 21 pytań z „tak” lub „nie”.

Po pierwsze zauważ, że gdy ktoś wypełnia formularz Google, powstały arkusz kalkulacyjny wstawia wiersz zamiast dodawać dane do następnego pustego wiersza. Oznacza to, że wszelkie formuły, które były w wierszu, zostały po prostu zderzone, a nowe dane z formuły nie zawierają żadnych formuł w następnych dwóch, które działałyby z danymi wejściowymi. Aby wyjść poza to, dodałem arkusz do arkusza kalkulacyjnego (skoroszytu). Wykorzystałem dane wejściowe z brettathds na stronie http://productforums.google.com/forum/#!topic/docs/dlW6U6cHuKw W związku z tym wstawiłem następującą formułę do komórki A1 tego drugiego arkusza: = ARRAYFORMULA (arkusz 1! A1: A). Zrobiłem podobnie dla każdej kolumny w kolumnie E. Kolumna E ma pytanie z 21 polami wyboru.

Nadal na drugim arkuszu, wiersz 1 kolumn FY miał nagłówki identyczne z tekstem na każdej opcji pola wyboru z listy kontrolnej obszarów, które chcą zgłosić na ochotnika. Następnie kolumna Z była kolejnym i ostatnim pytaniem w formularzu, było to krótkie pytanie tekstowe zatytułowane „Dodatkowe komentarze”. W tej kolumnie użyłem = ARRAYFORMULA (Arkusz1! F1: F) Zrobiłem to jako osobne pytanie, zamiast korzystać z opcji „inne” na końcu pytań w polu wyboru, ponieważ wydaje się, że nie jest to dobry sposób na wyciągnięcie tego dane do arkusza kalkulacyjnego.

Teraz do części, która odpowiada, jak uzyskać informacje z komórki w kolumnie E (która może mieć do 21 pozycji na liście), aby oddzielić kolumny znakiem „tak” lub po prostu puste. Formuła w F2 na drugim arkuszu to = iferror (if (szukaj (F $ 1, $ E2)> 0, „Tak”,)) Ta formuła sprawdza, czy znaleziony jest dokładny tekst w nagłówku kolumny (F1) w E2, jeśli tak, to zwraca „Tak”, jeśli nie, pozostawia puste.

Ta formuła, do której odwołuje się F2, została napisana (przy użyciu „$” za pomocą klawisza F4), dzięki czemu F2 można skopiować / wkleić do każdej komórki w wierszu 2 z GY (Arkusz kalkulacyjny Google nie obsługuje kopiowania formuł przez przeciąganie / kopiowanie, jak możesz) w programie Excel, ale można to zrobić, wybierając F2, następnie CTRL + C, a następnie wybierz G2: Y2, a następnie CTRL + V). Następnie korzystam z tej samej kopii metodologii wklejonej F2: Y2 do F3: Y100.

Następnie ukryłem kolumnę E na drugim arkuszu, aby wynikowy arkusz kalkulacyjny był bardziej zwarty (unikając zawijania z długich odpowiedzi).

Teraz jestem gotowy na przyjęcie 99 wolontariuszy za pośrednictwem szybkiego i łatwego formularza, który wypełnią. Wykorzystanie opcji filtrowania u góry kolumn pomogło również sprawdzić, kto był dostępny dla jakich obszarów.

Ostatnia frustracja, której nie rozwiązałem. Wypełniłem kilka fałszywych formularzy, aby przetestować moje formuły i projekt, a następnie usunąłem te wiersze z połączonego arkusza kalkulacyjnego. Miałem też kilka osób, które wypełniły zduplikowane formularze, usunąłem również ich wiersze. Godne uwagi na forach produktów, nie jestem jedynym sfrustrowanym, gdy dowiedziałem się, że w jakiś sposób Google nadal ma dane testowe / fałszywe / zduplikowane podłączone do „Podsumowania odpowiedzi” (znajdującego się w zakładce „Formularz” arkusza kalkulacyjnego). Dlatego podsumowanie odpowiedzi jest bezużyteczne dla dokładności, jeśli usunięto wiersze w celu usunięcia danych z formularzy testowych / fałszywych / zduplikowanych.

Ale zostało to osiągnięte i zapisuję te notatki na następny raz, gdy będę potrzebować podobnych, jestem pewien, że można to łatwo zrobić w mniej niż 30 minut następnym razem.

Mam nadzieję, że to pomoże, daj mi znać, jeśli jakieś pytania.