Как отображать информацию с других листов, относящуюся к тексту в ячейках

Наша компания занимается ремонтом бытовой техники в различных объектах. Я хотел бы создать базу данных для поиска информации о каждом устройстве в определенных свойствах и в их конкретных квартирах / единицах. Я создал форму, чтобы начать этот процесс, но мне нужна помощь с некоторыми сложное кодирование.

Сначала я создал поле для свойства, затем я создал поле «Квартира/Единица». Идея заключается в том, что когда я выбираю свойство, единицы, привязанные к этому свойству, отображаются в раскрывающемся списке/типе с возможностью поиска в поле Apt/Unit.

Затем я создал поле «Тип устройства». Идея заключается в том, что когда выбрана «Квартира/Блок», будет отображаться раскрывающийся список/список типов с возможностью поиска устройств, привязанных к этой конкретной «Квартире/Блоку».

Затем я создал поля для информации об устройстве (марка, номер модели, серийный номер и цвет), это более понятно — после выбора типа устройства будет отображаться соответствующая информация для каждого поля для этого прибор.

Вот ссылка на таблицу Google: https://docs.google.com/spreadsheets/d/1JZhEYjk5xVN3uOc_Ucb8HFr6d96XQ2Q_ehAd-d_o0ME/edit?usp=sharing

Любая помощь приветствуется!

Что-то похожее здесь: stackoverflow.com/questions/30232146/…

Yuri Khristich 23.04.2022 11:34

Но на самом деле задача выглядит точно так же, как применение фильтров. Сначала вы фильтруете таблицу по первому столбцу (свойство), затем по следующему столбцу (Apt/Unit), затем по следующему столбцу и т. д. Пока у вас не будет только одна строка (или несколько строк). Вы пробовали фильтровать таблицу?

Yuri Khristich 23.04.2022 12:09
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
1
2
68
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Просто для удовольствия я сделал это. Но это перебор:

// global variables
var SS = SpreadsheetApp.getActiveSpreadsheet();
var SHEET_USERFACE = SS.getSheetByName('Userface');
var SHEET_DATA = SS.getSheetByName('Data');

function onLoad() { reset() }

function onEdit(e) {
  if (e.range.getSheet().getName() != 'Userface') return;
  if (e.range.columnStart != 3) return;
  if (![9,11,13,15,17,19,21].includes(e.range.rowStart)) return;
  e.source.toast('Please, wait...');
  set_filter(e.range.offset(0,-1).getValue(), e.value);
  set_all_menus();
  e.source.toast('The sheet has been updated');
}

function reset() {
  SS.toast('Please wait...');
  try { SHEET_DATA.getFilter().remove() } catch(e) {}
  SHEET_USERFACE.getRange('c9:c21').clearContent().clearDataValidations();
  set_all_menus();
  SS.toast('The sheet has been updated');
}

function set_all_menus() {
  var data = SHEET_DATA.getDataRange().getDisplayValues().filter((_,i) => !SHEET_DATA.isRowHiddenByFilter(i+1));

  set_menu(data, 'b9',  'c9');
  set_menu(data, 'b11', 'c11');
  set_menu(data, 'b13', 'c13');
  set_menu(data, 'b15', 'c15');
  set_menu(data, 'b17', 'c17');
  set_menu(data, 'b19', 'c19');
  set_menu(data, 'b21', 'c21');
}

function set_menu(data, title, cell) {
  var menu_title = SHEET_USERFACE.getRange(title).getValue();
  var menu_cell = SHEET_USERFACE.getRange(cell);
  var col_index = data[0].indexOf(menu_title);
  var menu_list = [...new Set([...data.map(e => e[col_index])])].slice(1);
  var menu_rule = SpreadsheetApp.newDataValidation().requireValueInList(menu_list).build();
  menu_cell.setDataValidation(menu_rule);
}

function set_filter(column_title, value) {

  // get all the data and col index
  var [header, ...data] = SHEET_DATA.getDataRange().getValues();
  var col_index = header.indexOf(column_title);

  // unhide all values of the given column
  var clear = SpreadsheetApp.newFilterCriteria().setHiddenValues([]).build();
  var range = SHEET_DATA.getDataRange();
  var filter = range.getFilter() || range.createFilter()
  filter.setColumnFilterCriteria(col_index+1, clear);

  // get the values to hide
  var col_data = data.map(e => e[col_index]);
  var filtered = col_data.filter( (e, i) => e != value && SHEET_DATA.isRowHiddenByFilter(i+1) );
  var to_hide = col_data.filter( e => e != value );
  var hidden = [...new Set([...filtered, ...to_hide])];
  
  // hide the values with the filter
  var criteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(hidden).build();
  var range = SHEET_DATA.getDataRange();
  var filter = range.getFilter() || range.createFilter()
  filter.setColumnFilterCriteria(col_index+1, criteria);
}

enter image description here

Вот лист.

Работает довольно медленно. Вместо этого я бы предложил использовать собственные фильтры. По сути, скрипт включает и выключает фильтры и изменяет проверку данных для раскрывающихся меню соответственно.


Обновлять

Вот еще вариант скрипта. Он работает намного быстрее, но использует «вспомогательный лист» для хранения временных данных (отфильтрованная таблица). Вы можете скрыть «вспомогательный лист», если хотите.

// global variables
var SS = SpreadsheetApp.getActiveSpreadsheet();
var SHEET_USERFACE = SS.getSheetByName('Userface');
var SHEET_DATA = SS.getSheetByName('Data');
var SHEET_HELPER = SS.getSheetByName('Helper'); // the hidden sheet with temp data
var PROPERTY_LIST = [...new Set(SHEET_DATA.getRange('a2:a').getValues().flat())]; // 'Property' list
var DATA_OBJ = {}; 

function onLoad() { reset() }

function onEdit(e) {
  var {range, source, value} = e;

  if (range.getSheet().getName() != 'Userface') return;
  if (range.columnStart != 3) return;
  if (![9,11,13,15,17,19,21].includes(range.rowStart)) return;

  source.toast('Please, wait...');

  // reset whenever the first menu is changing
  if (range.rowStart == 9) {
    reset();
    source.getRange('c9').setValue(value);
  }

  var col_header = range.offset(0,-1).getValue();

  update_sheet_helper(col_header, value);
  update_all_dropdown_menus();
  
  source.toast('The sheet has been updated');
}

function reset() {
  SS.toast('Please wait...');

  // copy data from SHEET_DATA to SHEET_HELPER
  SHEET_USERFACE.getRange('c9:c21').clearContent().clearDataValidations();
  SHEET_DATA.getDataRange().copyTo(SHEET_HELPER.clearContents().getRange(1,1));

  update_data_obj();
  update_all_dropdown_menus();

  SS.toast('The sheet has been updated');
}

// make DATA_OBJECT from SHEET_HELPER
function update_data_obj() {
  DATA_OBJ = {};
  var [header, ...data] = SHEET_HELPER.getDataRange().getValues();
  for (let i in header) DATA_OBJ[header[i]] = data.map(e => e[i]);
  DATA_OBJ['Property'] = PROPERTY_LIST; // let 'Property' list will be full always
}

// remove from SHEET_DATA_HELPER all the rows
// that have no given value in column with given title
function update_sheet_helper(col_title, value) {
  var [header, ...data] = SHEET_HELPER.getDataRange().getValues();
  var col_index = header.indexOf(col_title);
  data = data.filter(k => k[col_index] == value);
  var table = [header, ...data];
  SHEET_HELPER.clearContents().getRange(1,1,table.length, table[0].length).setValues(table);
  update_data_obj();
}

function update_all_dropdown_menus() {
  SHEET_USERFACE.getRange('b9:c21').getValues().forEach((row,i) => {
      if (row[0] != '') set_data_validation(DATA_OBJ[row[0]], 'c' + (i+9));
    });

  function set_data_validation(data, cell_address) {
    var menu_list = [...new Set([...data])]; // remove duplicates from the array
    var menu_rule = SpreadsheetApp.newDataValidation().requireValueInList(menu_list).build();
    var cell_range = SHEET_USERFACE.getRange(cell_address)
    cell_range.setDataValidation(menu_rule);
    if (menu_list.length == 1) cell_range.setValue(menu_list[0]);
  }
}

Лист здесь.

Это идеально, единственное, что я заметил, это когда я выбрал свойство и попытался выбрать другое свойство - кажется, что оно удалило другое, поэтому я не могу ничего выбрать.

ADOGG4155 23.04.2022 21:25

Необходимо нажать круглую кнопку «Очистить», если вы хотите выбрать другой элемент в том же списке. Фильтры так работают.

Yuri Khristich 23.04.2022 22:20

Я просто придумал, как это можно сделать по-другому. Он должен работать намного быстрее, я надеюсь. Я постараюсь переписать код в ближайшее время.

Yuri Khristich 23.04.2022 22:46

Большой! Спасибо. Я действительно ценю это.

ADOGG4155 23.04.2022 22:58

Я обновил свой ответ. Он работает быстрее, так как не использует фильтры. Он использует дополнительный лист («Помощник») для хранения временных данных.

Yuri Khristich 24.04.2022 01:39

Я только что запросил доступ, чтобы увидеть форму, поэтому я также могу использовать кнопку!

ADOGG4155 24.04.2022 01:44

Это идеальный Юрий, именно то, чего я хотел добиться. Для «Марка, модель, серийный номер, цвет» можно ли не иметь раскрывающихся стрелок в этих полях? Только потому, что после выбора устройства в этих полях автоматически отображается информация, связанная с этим устройством, поэтому стрелки не нужны.

ADOGG4155 24.04.2022 01:54

Хорошо. Я удалил проверку данных из последних четырех ячеек.

Yuri Khristich 24.04.2022 02:07

Спасибо! Теперь единственная другая ошибка, которую я заметил, заключалась в том, что вы не можете, например, выбрать другой прибор в том же блоке — если я выбираю холодильник, и он генерирует информацию, я не могу попытаться выбрать другой прибор в том же блоке, это отображает только тот, который я уже выбрал, есть ли способ это исправить?

ADOGG4155 24.04.2022 02:28

Это не ошибка. Так работают фильтры в этой реализации. Вероятно, это можно сделать по-другому. Я подумаю завтра. У меня уже довольно поздно.

Yuri Khristich 24.04.2022 03:03

Ах понял. Очень ценю помощь! Спокойной ночи!

ADOGG4155 24.04.2022 03:18

Привет, я только что добавил еще один ответ, так как есть код, основанный на всем другом подходе. Он менее универсален, но работает лучше.

Yuri Khristich 24.04.2022 12:26
Ответ принят как подходящий

Вот третий вариант скрипта:

// global variables
var SS = SpreadsheetApp.getActiveSpreadsheet();
var SHEET_USERFACE = SS.getSheetByName('Userface');
var SHEET_DATA = SS.getSheetByName('Data');

function onLoad() { reset() }

function reset() {
  SS.toast('Please wait...');

  SHEET_USERFACE.getRange('c9:c21').clearContent();
  SHEET_USERFACE.getRange('c9:c13').clearDataValidations();

  var obj = make_obj_from_data();
  update_menu_prop(obj);
  update_menu_unit(obj);
  update_menu_type(obj);

  SS.toast('The sheet has been reset');
}

function onEdit(e) {
  if (e.range.getSheet().getName() != 'Userface') return;
  if (e.range.columnStart != 3) return;

  // Property menu
  if (e.range.rowStart == 9) {
    e.source.toast('Please, wait...');
    SHEET_USERFACE.getRange('c11:c21').clearContent();
    SHEET_USERFACE.getRange('c11:c13').clearDataValidations();
    var obj = make_obj_from_data();
    update_menu_unit(obj);
    update_menu_type(obj);
    e.source.toast('The sheet has been updated');
  }

  // Apt/Unit menu
  if (e.range.rowStart == 11) {
    e.source.toast('Please, wait...');
    SHEET_USERFACE.getRange('c13:c21').clearContent();
    SHEET_USERFACE.getRange('c13').clearDataValidations();
    var obj = make_obj_from_data();
    update_menu_type(obj);
    e.source.toast('The sheet has been updated');
  }

  // Applicance type menu
  if (e.range.rowStart == 13) {
    e.source.toast('Please, wait...');
    SHEET_USERFACE.getRange('c15:c21').clearContent();
    var obj = make_obj_from_data();
    update_brand_model_serial_color(obj);
    e.source.toast('The sheet has been updated');
  }
  
}

function make_obj_from_data() {
  var data = SHEET_DATA.getDataRange().getValues().slice(1);
  var obj = {};

  for (let row of data) {
    var [prop, unit, type, ...etc] = row;
    try { 
      obj[prop][unit][type] = etc;
    }
    catch(e) { 
      try {
        obj[prop][unit] = {}; obj[prop][unit][type] = etc;
      }
      catch(e) {
        obj[prop] = {}; obj[prop][unit] = {}; obj[prop][unit][type] = etc;
      }
    }
  }

  return obj;
}

function update_menu_prop(obj) {
  var cell = SHEET_USERFACE.getRange('c9');
  try {
    var list = Object.keys(obj);
    set_data_validation(cell, list);
  } catch(e) {
    console.log('update_menu_prop(obj)');
    console.log(e);
  } 
}

function update_menu_unit(obj) {
  var prop = SHEET_USERFACE.getRange('c9').getValue();
  var cell = SHEET_USERFACE.getRange('c11');
  try {
    var list = Object.keys(obj[prop]);
    set_data_validation(cell, list);
  } catch(e) {
    console.log('update_menu_unit(obj)');
    console.log(e);
  }
}

function update_menu_type(obj) {
  var prop = SHEET_USERFACE.getRange('c9').getValue();
  var unit = SHEET_USERFACE.getRange('c11').getValue();
  var cell = SHEET_USERFACE.getRange('c13');
  try {
    var list = Object.keys(obj[prop][unit]);
    set_data_validation(cell, list);
    if (list.length == 1) update_brand_model_serial_color(obj)
  } catch(e) {
    console.log('update_menu_type(obj)');
    console.log(e);
  }
}

function update_brand_model_serial_color(obj) {
  var [prop,,unit,,type] = SHEET_USERFACE.getRange('c9:c13').getValues();
  try {
    var [brand, model, serial, color] = obj[prop][unit][type];
    var arr = [[brand],[''],[model],[''],[serial],[''],[color]];
    SHEET_USERFACE.getRange('c15:c21').setValues(arr);
  } catch(e) {
    console.log('update_brand_model_serial_color(obj)');
    console.log(e);
  }
}

function set_data_validation(cell, list) {
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
  cell.setDataValidation(rule);
  // put the value in the cell if there is just one element in the list
  if (list.length == 1) cell.setValue(list[0]);
}

Вот мой лист.

Он работает примерно так же, как и любой подобный интерфейс. Вы выбираете первое меню, и оно изменяет проверку данных для второго меню и очищает третье меню. Затем вы выбираете второе меню, и оно меняет третье. Как только вы изменяете третье меню, оно заполняет остальные поля.

Поскольку вы используете только три меню, и они должны меняться шаг за шагом, я решил «жестко запрограммировать» их. Это не лучшая практика, и могут возникнуть проблемы, если/когда вы решите изменить функциональность. Но для этого конкретного случая я думаю, что «жесткое кодирование» простительно. Он работает относительно быстро, а код относительно читабелен.

Эй, Юрий! Просто попросил разрешения на этот лист Google!

ADOGG4155 24.04.2022 12:31

Это именно то, что я хотел. Большое спасибо. Если у вас есть PayPal или что-то подобное, пожалуйста, поделитесь им.

ADOGG4155 24.04.2022 12:36

Спасибо. Мне было приятно. Я не хотел получать какие-либо платежи. И, к сожалению, сейчас я живу в фашистском воинственном государстве, в котором больше нет ни PayPal, ни какой-либо другой приличной платежной системы. Немного удивительно, что у нас все еще есть доступ к Stackoverflow. Не уверен, что это продлится долго. :(

Yuri Khristich 24.04.2022 12:49

Что ж, когда вы это сделаете, пожалуйста, свяжитесь со мной, могу ли я получить вашу электронную почту для дальнейшего использования?

ADOGG4155 24.04.2022 12:57

Я только что ответил вам по электронной почте, если вы не возражаете. Давай останемся на связи.

Yuri Khristich 24.04.2022 13:04

решение без сценария:

=IFERROR({INDEX(IFERROR(Data!A1:G1/0)); Data!A1:G1; QUERY({Data!A2:G}, "where 1=1 "&
 IF(C10="",,"and lower(Col1) contains '"&LOWER(C10)&"'")&
 IF(C12="",,"and Col2 = "&C12)&
 IF(C14="",,"and lower(Col3) contains '"&LOWER(C14)&"'")&
 IF(C16="",,"and lower(Col4) contains '"&LOWER(C16)&"'")&
 IF(C18="",,"and lower(Col5) contains '"&LOWER(C18)&"'")&
 IF(C20="",,"and lower(Col6) contains '"&LOWER(C20)&"'")&
 IF(C22="",,"and lower(Col7) contains '"&LOWER(C22)&"'"), 0)}, {"";"no data"})

enter image description here

демонстрационный лист

Ну, я подозреваю, что это лучшее решение для реальной жизни. Нативные функции работают намного быстрее любого серверного скрипта. И на самом деле я думаю, что может быть еще одно решение (вероятно, лучшее) через боковую панель HTML и скрипт на стороне клиента.

Yuri Khristich 24.04.2022 13:01

Другие вопросы по теме