Как вы получаете столбец подкатегории, чтобы заполнить раскрывающийся список в зависимости от значения, выбранного в основной категории, в списках Google?
Я googled вокруг и не мог найти никаких хороших решений, поэтому я хотел поделиться своим собственным. См. Мой ответ ниже.
Вы можете начать с листа Google, настроенного с главной страницей, и выпадающего источника, как показано ниже.
Вы можете настроить раскрытие первого столбца в обычных меню "Данные" > "Проверка".
Главная страница
Выпадающая страница источника
После этого вам нужно настроить script с именем onEdit. (Если вы не используете это имя, getActiveRange() ничего не сделает, кроме как вернуть ячейку A1)
И используйте приведенный здесь код:
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();
}
}
}
После этого настройте триггер на экране редактора script, перейдя в меню "Редактирование" > "Текущие триггеры проекта". Это приведет к появлению окна, чтобы вы могли выбрать различные выпадающие списки, чтобы в итоге получилось:
Вы должны быть добрыми после этого!
Заметка
Сценарии имеют ограничение: он обрабатывает до 500 значений в одном раскрывающемся списке.
Сценарий был выпущен в январе 2018 года. Пожалуйста, смотрите:
Улучшения:
Версии скрипта
Это решение не идеально, но оно дает некоторые преимущества:
Прежде всего, здесь рабочий пример, поэтому вы можете проверить его, прежде чем идти дальше.
Мой план:
Data > ValidationПодготовить данные
Данные выглядят как единая таблица со всеми возможными вариантами. Он должен быть расположен на отдельном листе, чтобы его мог использовать скрипт. Посмотрите на этот пример:
Здесь у нас есть четыре уровня, каждое значение повторяется. Обратите внимание, что 2 столбца справа от данных зарезервированы, поэтому не вводите и не вставляйте туда никаких данных.
Первая простая проверка данных (DV)
Подготовьте список уникальных значений. В нашем примере это список планет. Найдите свободное место на листе с данными и вставьте формулу: =unique(A:A) На вашем основном листе выберите первый столбец, с которого начнется DV. Перейдите в Данные> Проверка и выберите диапазон с уникальным списком.
скрипт
Вставьте этот код в редактор скриптов:
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);
}Изменить: ответ ниже может быть удовлетворительным, но он имеет некоторые недостатки:
Существует заметная пауза для запуска script. Я нахожусь на задержке в 160 мс, и этого достаточно, чтобы раздражать.
Он работает, создавая новый диапазон каждый раз, когда вы редактируете данную строку. Это приводит к недопустимому содержимому предыдущих записей часть времени
Я надеюсь, что другие могут немного почистить это.
Здесь другой способ сделать это, что экономит вам тонну именования диапазонов:
Три листа на листе: назовите их Main, List и DRange (для динамического диапазона). На главном листе столбец 1 содержит метку времени. Эта метка времени изменяется наEdit.
В списке ваши категории и подкатегории упорядочены как простой список. Я использую это для инвентаря растений на моей деревенской ферме, поэтому мой список выглядит следующим образом:
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
...
Где | указывает на разделение на столбцы.
Для удобства я также использовал заголовки в качестве имен для именованных диапазонов.
Уравнение A1 имеет формулу
=Max(Main!A2:A1000)
Это возвращает самую последнюю временную метку.
A2 - A4 имеют вариации на:
=vlookup($A$1,Inventory!$A$1:$E$1000,2,False)
при этом 2 увеличивается для каждой ячейки справа.
При запуске A2-A4 будут отобраны выбранные группы, роды и виды.
Ниже каждого из них есть команда filter что-то вроде этого:
= уникальный (фильтр (Bot_Name, REGEXMATCH (Bot_Name, С1)))
Эти фильтры заполнят блок ниже соответствующими совпадениями с содержимым верхней ячейки.
Фильтры могут быть изменены в соответствии с вашими потребностями и в формате вашего списка.
Back to Main: проверка данных в Main выполняется с использованием диапазонов от DRange.
script Я использую:
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
}
}
}
}
Оригинальная презентация Youtube, которая предоставила мне большую часть компонента timestamp onEdit: https://www.youtube.com/watch?v=RDK8rjdE85Y
Здесь у вас есть другое решение, основанное на том, которое предоставляется @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() );
}
Он имеет некоторые преимущества по сравнению с другим подходом:
Итак, как его использовать:
Наслаждайтесь!
Продолжая эволюцию этого решения, я увеличил ставку, добавив поддержку нескольких вариантов корневого каталога и более глубоких вложенных выборов. Это дальнейшее развитие решения JavierCane (которое, в свою очередь, построено на основе tarheel).
/**
* "on edit" event handler
*
* Based on JavierCane answer in
*
* http://stackoverflow.com/info/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()