How to display info from other sheets, specific to text in cells

Question

Our company works at different properties repairing appliances, I would like to build a database to search up the information on each appliance at specific properties and in their specific apt/units, I created a form to start this process, but I need help with some complex coding.

I first created a box for the property, then I created an "Apt/Unit" box. The idea is when I select a property, the units tied to that property are shown in dropdown/type searchable list in the Apt/Unit box.

I then created an "Appliance type" box. The idea is when the "Apt/Unit" is selected, it will display the dropdown/type searchable list of the appliances tied to that specific "Apt/Unit".

Then I created boxes for the info for the appliance (Brand, Model #, Serial #, & Color), this is a bit more self-explanatory - once the appliance type is selected, it will display the respective information for each box for that appliance.

Here's the link to the Google sheet: https://docs.google.com/spreadsheets/d/1JZhEYjk5xVN3uOc_Ucb8HFr6d96XQ2Q_ehAd-d_o0ME/edit?usp=sharing

Any help is appreciated!

Answer 1

Here is third variant of the script:

// 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]);
}

Here is my sheet.

It works about that way as it does any similar interface. You select the first menu and it changes data validation for the second menu and cleans the third menu. Then you select the second menu and it changes the third one. As soon as you change the third menu it fills the rest fields.

Since you're using just the three menus and they supposed to be changed step by step I decided to 'hardcode' them. It's not the best practice and there can be problems if/when you decide to change the functionality. But for this particular case I think the 'hardcoding' is forgivable. It works relatively fast and the code is relatively readable.

Answer 2

non-scripted solution:

=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"})

demo sheet

Answer 3

Just for fun I've made it. But this is overkill:

// 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);
}

Here is the sheet.

It works quite slow. I'd propose to use the native filters instead. Basically the script turns on and off the filters an changes data validation for the dropdown menus respectively.

---

Update

Here another version of the script. It works much faster but it uses the 'helper sheet' to store temporary data (the filtered table). You can hide the 'helper sheet' if you want.

// 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]);
  }
}

The sheet is here.