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.