Jak zrobić dynamiczne / zależne rozwijane w Arkuszach Google?
Jak uzyskać kolumnę podkategorii, aby wypełnić listę rozwijaną na podstawie wartości wybranej w głównej liście rozwijanej kategorii w Arkuszach google?
Wygooglowałem i nie mogłem znaleźć żadnych dobrych rozwiązań, dlatego chciałem podzielić się swoimi. Proszę zobaczyć moją odpowiedź poniżej.
5 answers
Możesz zacząć od arkusza google skonfigurowanego ze stroną główną i rozwijaną stroną źródłową, jak pokazano poniżej.
Możesz ustawić pierwszą kolumnę rozwijaną za pomocą monitów menu normalne dane > walidacje.
Strona Główna
Rozwijana Strona Źródłowa
Następnie należy skonfigurować skrypt o nazwie onEdit
. (Jeśli nie użyjesz tej nazwy, getActiveRange () zwróci tylko komórkę A1)
I użyj kodu podanego tutaj:
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var myRange = SpreadsheetApp.getActiveRange();
var dvSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Categories");
var option = new Array();
var startCol = 0;
if(sheet.getName() == "Front Page" && myRange.getColumn() == 1 && myRange.getRow() > 1){
if(myRange.getValue() == "Category 1"){
startCol = 1;
} else if(myRange.getValue() == "Category 2"){
startCol = 2;
} else if(myRange.getValue() == "Category 3"){
startCol = 3;
} else if(myRange.getValue() == "Category 4"){
startCol = 4;
} else {
startCol = 10
}
if(startCol > 0 && startCol < 10){
option = dvSheet.getSheetValues(3,startCol,10,1);
var dv = SpreadsheetApp.newDataValidation();
dv.setAllowInvalid(false);
//dv.setHelpText("Some help text here");
dv.requireValueInList(option, true);
sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build());
}
if(startCol == 10){
sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).clearDataValidations();
}
}
}
Następnie Ustaw WYZWALACZ na ekranie edytora skryptów, przechodząc do edytuj > wyzwalacze bieżącego projektu. Spowoduje to wyświetlenie okna, w którym można wybrać różne rozwijane spadki, aby ostatecznie zakończyć się na tym:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-18 00:15:04
Uwaga
Skrypty mają limit: obsługuje do 500 wartości na jednej rozwijanej liście.
Nowy Skrypt. 201801
Scenariusz został wydany w styczniu 2018 roku. Zobacz:- Strona główna z instrukcjami i demo, gdzie możesz zadać pytanie.
- Strona Githuba z instrukcjami i kodem źródłowym.
Ulepszenia:
- Speed up
- obsługuje wiele reguł w 1 arkusz
- Połącz inne arkusze jako dane źródłowe.
- Niestandardowa kolejność kolumn listy rozwijanej
Stary Skrypt.
Wersje skryptu
- v. 1 .
- v. 2. 2016-03. Ulepszono: działa z duplikatami w dowolnej kategorii. Na przykład, jeśli mamy list1 z modelami samochodów i list2 z kolorami. Kolor można powtórzyć w dowolnym modelu.
- v3. 2017-01. Poprawiono: brak błędu, gdy jedyną wartością jest wszedł.
- najnowsza wersja : 2018-02. Zobacz artykuł tutaj .
To rozwiązanie nie jest idealne, ale daje pewne korzyści:]}
- pozwala na tworzenie wielu list rozwijanych
- daje większą kontrolę
- dane źródłowe są umieszczane na jedynym arkuszu, więc łatwo je edytować
Po pierwsze, oto roboczy przykład , więc możesz go przetestować przed pójściem dalej.
Mój plan:
- Przygotuj Dane
- zrób pierwszą listę jak zwykle:
Data > Validation
- Dodaj skrypt, ustaw kilka zmiennych
- zrobione!
Przygotuj Dane
Dane wyglądają jak pojedyncza tabela ze wszystkimi możliwymi wariantami wewnątrz niej. Musi znajdować się na osobnym arkuszu, aby skrypt mógł z niego korzystać. Spójrz na ten przykład:
Tutaj mamy cztery poziomy, każda wartość się powtarza. Zwróć uwagę, że 2 kolumny po prawej stronie dane są zastrzeżone, więc nie wpisuj/wklej żadnych danych.
Pierwsza prosta Walidacja danych (DV)
Przygotuj listę unikalnych wartości. W naszym przykładzie jest to lista Planet . Znajdź wolne miejsce na arkuszu z danymi i wklej formułę: =unique(A:A)
W arkuszu głównym wybierz pierwszą kolumnę, od której zacznie się DV. Przejdź do DANE > Walidacja i wybierz zakres z unikalną listą.
Script
Wklej ten kod do Edytor skryptów:
function SmartDataValidation(event)
{
//--------------------------------------------------------------------------------------
// The event handler, adds data validation for the input parameters
//--------------------------------------------------------------------------------------
// Declare some variables:
//--------------------------------------------------------------------------------------
var TargetSheet = 'Main' // name of the sheet where you want to verify the data
var LogSheet = 'Data' // name of the sheet with information
var NumOfLevels = 4 // number of associated drop-down list levels
var lcol = 2; // number of the leftmost column, in which the changes are checked; A = 1, B = 2, etc.
var lrow = 2; // line number from which the rule will be valid
//--------------------------------------------------------------------------------------
// =================================== key variables =================================
//
// ss sheet we change (TargetSheet)
// br range to change
// scol number of column to edit
// srow number of row to edit
// CurrentLevel level of drop-down, which we change
// HeadLevel main level
// r current cell, which was changed by user
// X number of levels could be checked on the right
//
// ls Data sheet (LogSheet)
//
// ======================================================================================
// [ 01 ].Track sheet on which an event occurs
var ts = event.source.getActiveSheet();
var sname = ts.getName();
if (sname == TargetSheet)
{
// ss -- is the current book
var ss = SpreadsheetApp.getActiveSpreadsheet();
// [ 02 ]. If the sheet name is the same, you do business...
var ls = ss.getSheetByName(LogSheet); // data sheet
// [ 03 ]. Determine the level
//-------------- The changing sheet --------------------------------
var br = event.source.getActiveRange();
var scol = br.getColumn(); // the column number in which the change is made
var srow = br.getRow() // line number in which the change is made
// Test if column fits
if (scol >= lcol)
{
// Test if row fits
if (srow >= lrow)
{
var CurrentLevel = scol-lcol+2;
// adjust the level to size of
// range that was changed
var ColNum = br.getLastColumn() - scol + 1;
CurrentLevel = CurrentLevel + ColNum - 1;
// also need to adjust the range 'br'
if (ColNum > 1)
{
br = br.offset(0,ColNum-1);
} // wide range
var HeadLevel = CurrentLevel - 1; // main level
// split rows
var RowNum = br.getLastRow() - srow + 1;
var X = NumOfLevels - CurrentLevel + 1;
// the current level should not exceed the number of levels, or
// we go beyond the desired range
if (CurrentLevel <= NumOfLevels )
{
// determine columns on the sheet "Data"
var KudaCol = NumOfLevels + 2
var KudaNado = ls.getRange(1, KudaCol);
var lastRow = ls.getLastRow(); // get the address of the last cell
var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol);
// ============================================================================= > loop >
for (var j = 1; j <= RowNum; j++)
{
for (var k = 1; k <= X; k++)
{
HeadLevel = HeadLevel + k - 1; // adjust parent level
CurrentLevel = CurrentLevel + k - 1; // adjust current level
var r = br.getCell(j,1).offset(0,k-1,1);
var SearchText = r.getValue(); // searched text
// if anything is choosen!
if (SearchText != '')
{
//-------------------------------------------------------------------
// [ 04 ]. define variables to costumize data
// for future data validation
//--------------- Sheet with data --------------------------
// combine formula
// repetitive parts
var IndCodePart = 'INDIRECT("R1C' + HeadLevel + ':R' + lastRow + 'C';
IndCodePart = IndCodePart + HeadLevel + '",0)';
// the formula
var code = '=UNIQUE(INDIRECT("R" & MATCH("';
code = code + SearchText + '",';
code = code + IndCodePart;
code = code + ',0) & "C" & "' + CurrentLevel
code = code + '" & ":" & "R" & COUNTIF(';
code = code + IndCodePart;
code = code + ',"' + SearchText + '") + MATCH("';
code = code + SearchText + '";';
code = code + IndCodePart;
code = code + ',0) - 1';
code = code + '& "C" & "' ;
code = code + CurrentLevel + '",0))';
// Got it! Now we have to paste formula
KudaNado.setFormulaR1C1(code);
// get required array
var values = [];
for (var i = 1; i <= lastRow; i++)
{
var currentValue = ChtoNado.getCell(i,1).getValue();
if (currentValue != '')
{
values.push(currentValue);
}
else
{
var Variants = i-1; // number of possible values
i = lastRow; // exit loop
}
}
//-------------------------------------------------------------------
// [ 05 ]. Build daya validation rule
var cell = r.offset(0,1);
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(values, true)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
if (Variants == 1)
{
cell.setValue(KudaNado.getValue());
} // the only value
else
{
k = X+1;
} // stop the loop through columns
} // not blanc cell
else
{
// kill extra data validation if there were
// columns on the right
if (CurrentLevel <= NumOfLevels )
{
for (var i = 1; i <= NumOfLevels; i++)
{
var cell = r.offset(0,i);
// clean
cell.clear({contentsOnly: true});
// get rid of validation
cell.clear({validationsOnly: true});
}
} // correct level
} // empty row
} // loop by cols
} // loop by rows
// ============================================================================= < loop <
} // wrong level
} // rows
} // columns...
} // main sheet
}
function onEdit(event)
{
SmartDataValidation(event);
}
Oto zestaw zmiennych, które mają zostać zmienione, znajdziesz je w skrypcie:
var TargetSheet = 'Main' // name of the sheet where you want to verify the data
var LogSheet = 'Data' // name of the sheet with information
var NumOfLevels = 4 // number of associated drop-down list levels
var lcol = 2; // leftmost column, in which the changes are checked; A = 1, B = 2, etc.
var lrow = 2; // line number from which the rule will be valid
Proponuję wszystkim, którzy dobrze znają Skrypty, wysłać swoje edycje do tego kodu. Myślę, że jest prostszy sposób na znalezienie listy walidacji i szybsze uruchamianie skryptu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-16 10:57:18
Edit: odpowiedź poniżej może być zadowalająca, ale ma pewne wady:
Jest zauważalna Przerwa na uruchamianie skryptu. Mam opóźnienie 160 ms i to wystarczy, żeby być irytującym.
Działa poprzez budowanie nowego zakresu za każdym razem, gdy edytujesz dany wiersz. To daje "nieprawidłową zawartość" do poprzednich wpisów niektóre
Oto inny sposób, aby to zrobić, że oszczędza mnóstwo nazw zakresu:
Trzy arkusze w arkuszu roboczym: nazwij je Main, List i DRange (dla zakresu dynamicznego.) Na głównym arkuszu kolumna 1 zawiera znacznik czasu. Ten znacznik czasu jest modyfikowany onEdit.
Na liście Twoje Kategorie i podkategorie są ułożone jako prosta lista. Używam tego do inwentaryzacji roślin na mojej farmie drzew, więc moja lista wygląda tak:
Group | Genus | Bot_Name
Conifer | Abies | Abies balsamea
Conifer | Abies | Abies concolor
Conifer | Abies | Abies lasiocarpa var bifolia
Conifer | Pinus | Pinus ponderosa
Conifer | Pinus | Pinus sylvestris
Conifer | Pinus | Pinus banksiana
Conifer | Pinus | Pinus cembra
Conifer | Picea | Picea pungens
Conifer | Picea | Picea glauca
Deciduous | Acer | Acer ginnala
Deciduous | Acer | Acer negundo
Deciduous | Salix | Salix discolor
Deciduous | Salix | Salix fragilis
...
Gdzie / wskazuje podział na kolumny.
Dla wygody użyłem również nagłówków jako nazw dla nazwanych zakresów.
DRrange A1 ma wzór
=Max(Main!A2:A1000)
Zwraca najnowszy znacznik czasu.
A2 do A4 mają różne warianty:
=vlookup($A$1,Inventory!$A$1:$E$1000,2,False)
Z przyrostem 2 dla każdej komórki po prawej stronie.
Po uruchomieniu A2 do A4 będą miały aktualnie wybraną grupę, rodzaj i Gatunek.
Poniżej każdego z nich znajduje się polecenie filter coś w tym stylu:
=unique(filter(Bot_Name,REGEXMATCH (Bot_Name,C1)))
Filtry te wypełnią blok poniżej z pasującymi wpisami do zawartości górnej komórki.
Filtry można modyfikować do własnych potrzeb i do formatu listy.
Powrót do Main: walidacja danych w Main odbywa się przy użyciu zakresów z DRange.
Skrypt, którego używam:
function onEdit(event) {
//SETTINGS
var dynamicSheet='DRange'; //sheet where the dynamic range lives
var tsheet = 'Main'; //the sheet you are monitoring for edits
var lcol = 2; //left-most column number you are monitoring; A=1, B=2 etc
var rcol = 5; //right-most column number you are monitoring
var tcol = 1; //column number in which you wish to populate the timestamp
//
var s = event.source.getActiveSheet();
var sname = s.getName();
if (sname == tsheet) {
var r = event.source.getActiveRange();
var scol = r.getColumn(); //scol is the column number of the edited cell
if (scol >= lcol && scol <= rcol) {
s.getRange(r.getRow(), tcol).setValue(new Date());
for(var looper=scol+1; looper<=rcol; looper++) {
s.getRange(r.getRow(),looper).setValue(""); //After edit clear the entries to the right
}
}
}
}
Oryginalna prezentacja na Youtube, która dała mi większość komponentu znacznika czasu onEdit: https://www.youtube.com/watch?v=RDK8rjdE85Y
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-03 20:29:47
Tutaj masz inne rozwiązanie oparte na tym dostarczonym przez @ tarheel
function onEdit() {
var sheetWithNestedSelectsName = "Sitemap";
var columnWithNestedSelectsRoot = 1;
var sheetWithOptionPossibleValuesSuffix = "TabSections";
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = SpreadsheetApp.getActiveSheet();
// If we're not in the sheet with nested selects, exit!
if ( activeSheet.getName() != sheetWithNestedSelectsName ) {
return;
}
var activeCell = SpreadsheetApp.getActiveRange();
// If we're not in the root column or a content row, exit!
if ( activeCell.getColumn() != columnWithNestedSelectsRoot || activeCell.getRow() < 2 ) {
return;
}
var sheetWithActiveOptionPossibleValues = activeSpreadsheet.getSheetByName( activeCell.getValue() + sheetWithOptionPossibleValuesSuffix );
// Get all possible values
var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues.getSheetValues( 1, 1, -1, 1 );
var possibleValuesValidation = SpreadsheetApp.newDataValidation();
possibleValuesValidation.setAllowInvalid( false );
possibleValuesValidation.requireValueInList( activeOptionPossibleValues, true );
activeSheet.getRange( activeCell.getRow(), activeCell.getColumn() + 1 ).setDataValidation( possibleValuesValidation.build() );
}
Ma pewne korzyści w stosunku do innego podejścia:
- nie musisz edytować skryptu za każdym razem, gdy dodajesz opcję "root". Musisz tylko utworzyć nowy arkusz z zagnieżdżonymi opcjami tej opcji głównej.
- zrefakturowałem skrypt podający bardziej semantyczne nazwy zmiennych i tak dalej. Ponadto wyodrębniłem niektóre parametry do zmiennych, aby ułatwić dostosowanie do twojego konkretnego przypadku. Musisz tylko ustawić pierwsze 3 wartości.
- nie ma limitu zagnieżdżonych wartości opcji(użyłem metody getSheetValues z wartością -1).
Więc, jak go używać:
- Utwórz arkusz, w którym będziesz miał zagnieżdżone selektory
- Przejdź do "Narzędzia" > "Edytor skryptów..." i wybierz opcję "pusty projekt"
- Wklej kod dołączony do tej odpowiedzi
- zmodyfikuj pierwsze 3 zmienne skryptu ustawiając swoje wartości i save it
- Utwórz jeden arkusz w tym samym dokumencie dla każdej możliwej wartości "głównego selektora". Muszą być nazwane jako wartość + podany przyrostek.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-29 11:28:21
Kontynuując ewolucję tego rozwiązania, podniosłem ante, dodając obsługę wielu selekcji głównych i głębszych selekcji zagnieżdżonych. Jest to dalszy rozwój rozwiązania JavierCane ' a (który z kolei zbudował na tarheelu).
/**
* "on edit" event handler
*
* Based on JavierCane's answer in
*
* http://stackoverflow.com/questions/21744547/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets
*
* Each set of options has it own sheet named after the option. The
* values in this sheet are used to populate the drop-down.
*
* The top row is assumed to be a header.
*
* The sub-category column is assumed to be the next column to the right.
*
* If there are no sub-categories the next column along is cleared in
* case the previous selection did have options.
*/
function onEdit() {
var NESTED_SELECTS_SHEET_NAME = "Sitemap"
var NESTED_SELECTS_ROOT_COLUMN = 1
var SUB_CATEGORY_COLUMN = NESTED_SELECTS_ROOT_COLUMN + 1
var NUMBER_OF_ROOT_OPTION_CELLS = 3
var OPTION_POSSIBLE_VALUES_SHEET_SUFFIX = ""
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var activeSheet = SpreadsheetApp.getActiveSheet()
if (activeSheet.getName() !== NESTED_SELECTS_SHEET_NAME) {
// Not in the sheet with nested selects, exit!
return
}
var activeCell = SpreadsheetApp.getActiveRange()
// Top row is the header
if (activeCell.getColumn() > SUB_CATEGORY_COLUMN ||
activeCell.getRow() === 1 ||
activeCell.getRow() > NUMBER_OF_ROOT_OPTION_CELLS + 1) {
// Out of selection range, exit!
return
}
var sheetWithActiveOptionPossibleValues = activeSpreadsheet
.getSheetByName(activeCell.getValue() + OPTION_POSSIBLE_VALUES_SHEET_SUFFIX)
if (sheetWithActiveOptionPossibleValues === null) {
// There are no further options for this value, so clear out any old
// values
activeSheet
.getRange(activeCell.getRow(), activeCell.getColumn() + 1)
.clearDataValidations()
.clearContent()
return
}
// Get all possible values
var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues
.getSheetValues(1, 1, -1, 1)
var possibleValuesValidation = SpreadsheetApp.newDataValidation()
possibleValuesValidation.setAllowInvalid(false)
possibleValuesValidation.requireValueInList(activeOptionPossibleValues, true)
activeSheet
.getRange(activeCell.getRow(), activeCell.getColumn() + 1)
.setDataValidation(possibleValuesValidation.build())
} // onEdit()
Jak mówi Javier:
- Utwórz arkusz, w którym będziesz miał zagnieżdżone selektory
- przejdź do "Narzędzia" > "Edytor skryptów..." i wybierz " pusty projekt" opcja
- Wklej kod dołączony do tego odpowiedź
- zmodyfikuj stałe u góry skryptu, ustawiając swoje wartości i zapisz to
- Utwórz jeden arkusz w tym samym dokumencie dla każdej możliwej wartości "selektor root". Muszą być nazwane jako wartość + podany sufiks.
I jeśli chcesz zobaczyć go w akcji, stworzyłem arkusz demo i możesz zobaczyć kod, jeśli weźmiesz kopię.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-05-30 19:33:00