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.
There are two solutions:
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:
VG+/EX
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
Sheet: Sheet1
Sheet: Conditions