Extracting data from table and multiple sheets using conditional logic

Question

I am trying to create a custom template for writing a condensed description in google sheets. First, I want the information from both the "title" and "notes" column under "input" sheet to be denoted as the first and second statement respectively.

Then, given the material and condition information, I want to extract and add the information from the cross-reference table under "condition*_chart"

---

INPUT SHEET

---

CONDITION CHART

---

This is what I have so far.

function getDescription(title, notes, material, condition) {
  let description = """";
  if (title) {
    description += title + "". "";
  }
  if (notes) {
    description += notes + "". "";
  }
  if (material && condition) {
    if (material === ""CD"" && condition === ""M"") {
      description += var condition_chartSheet.getRange(2, 2).getValue();
    } else if (material === ""CD"" && condition === ""NM or M-"") {
      description += var condition_chartSheet.getRange(2, 3).getValue();
    } else if (material === ""CD"" && condition === ""VG+/EX"") {
      description += var condition_chartSheet.getRange(2, 4).getValue();
    } else if (material === ""CD"" && condition === ""VG"") {
      description += var condition_chartSheet.getRange(2, 5).getValue();
    } else if (material === ""CD"" && condition === ""G/G+"" || ""G+"" || ""G"") {
      description += var condition_chartSheet.getRange(2, 6).getValue(); 
    } else if (material === ""CD"" && condition === ""P"" || ""F"" || ""F/P"") {
      description += var condition_chartSheet.getRange(2, 7).getValue(); 
    } else if (material === ""Cassette"" && condition === ""M"") {
      description += var condition_chartSheet.getRange(3, 2).getValue();
    } else if (material === ""Cassette"" && condition === ""NM or M-"") {
      description += var condition_chartSheet.getRange(3, 3).getValue();
    } else if (material === ""Cassette"" && condition === ""VG+/EX"") {
      description += var condition_chartSheet.getRange(3, 4).getValue();
    } else if (material === ""Cassette"" && condition === ""VG"") {
      description += var condition_chartSheet.getRange(3, 5).getValue();
    } else if (material === ""Cassette"" && condition === ""G/G+"" || ""G+"" || ""G"") {
      description += var condition_chartSheet.getRange(3, 6).getValue(); 
    } else if (material === ""Cassette"" && condition === ""P"" || ""F"" || ""F/P"") {
      description += var condition_chartSheet.getRange(3, 7).getValue(); 
    } else if (material === ""Vinyl"" && condition === ""M"") {
      description += var condition_chartSheet.getRange(4, 2).getValue();
    } else if (material === ""Vinyl"" && condition === ""NM or M-"") {
      description += var condition_chartSheet.getRange(4, 3).getValue();
    } else if (material === ""Vinyl"" && condition === ""VG+/EX"") {
      description += var condition_chartSheet.getRange(4, 4).getValue();
    } else if (material === ""Vinyl"" && condition === ""VG"") {
      description += var condition_chartSheet.getRange(4, 5).getValue();
    } else if (material === ""Vinyl"" && condition === ""G/G+"" || ""G+"" || ""G"") {
      description += var condition_chartSheet.getRange(4, 6).getValue(); 
    } else if (material === ""Vinyl"" && condition === ""P"" || ""F"" || ""F/P"") {
      description += var condition_chartSheet.getRange(4, 7).getValue(); 
    } else {
      description += ""No description available."";
    }
  } else {
    description += ""No description available."";
  }
  return description;
}"

Please forgive for which I am fairly new to programming. Any help would be appreciated.

Answer 1

There are two solutions:

• by formula
• by script

One of the changes made in this answer was to assume that the "Materials" and "Conditions" values on the "Input Sheet" were defined by Data Validation based on data on the Conditions sheet. This ensures consistency between the sources. This is particularly important to eliminate problems with typos, and because the OP's data contains a specific inconsistency:

• Input sheet refers to VG+/EX
• Conditions sheet refers EX, VG+

Data Validation for "Materials" references Conditions sheet: A2:A10.

Data Validation for "Condition" references Conditions sheet: B2:G2.

---

FORMULA

Insert this formula in Cell E2:

=INDEX(IFNA(VLOOKUP(C2:C&"×"&D2:D, SPLIT(FLATTEN( IF(Conditions!B2:G="",,Conditions!A2:A&"×"&Conditions!B1:G1&"♦"&Conditions!B2:G)), "♦"), 2, 0)))

h/t: @player0 Two dimensional lookup?

---

SCRIPT

function getDescription() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var dataSheetName = "Sheet2"
  var dataSheet = ss.getSheetByName(dataSheetName)
  var conditionsSheetName = "Conditions"
  var conditionsSheet = ss.getSheetByName(conditionsSheetName)

  var data = dataSheet.getDataRange().getValues()
  var dataLR = dataSheet.getLastRow()
  var dataMaterials = data.map(function(e){return e[2]})
  var dataCondition = data.map(function(e){return e[3]})

  var conditions = conditionsSheet.getDataRange().getValues()
  var condMaterials = conditions.map(function(e){return e[0]})
  var condConditions = conditions[0]

  var colArray = []

  for (var i=1;i<dataLR;i++){ // skip the header row
    var matResult = condMaterials.indexOf(dataMaterials[i]) // zero-based result
    var condResult = condConditions.indexOf(dataCondition[i]) // zero-based result
    var desc = conditions[matResult][condResult]
    // Logger.log("DEBUG: i: "+i+", Title: "+data[i][0]+", Notes: "+data[i][1]+", Material: "+dataMaterials[i]+", Condition: "+dataCondition[i]+", Description: "+desc)
    var rowArray=[]
    rowArray.push(desc)
    colArray.push(rowArray)
  }
  
  // Logger.log(colArray) // DEBUG
  dataSheet.getRange(2,5,dataLR-1,1).setValues(colArray)
}

---

Sheet: Sheet1

---

Sheet: Conditions