Spreadsheet with conditional dropdown based on options from another sheet

Question

I have a destination sheet where in Col "B" there are a list of "Companies", in Col "K" I would to choose, with a dropdown, the "Address" available (of that specific "Company") taken from another sheet, located in col B and J.

How to do this with a script?

https://docs.google.com/spreadsheets/d/15g_3TMmVufKZogCbO3SUWBUp3iwc21nQgPBw6_GWXQQ/edit

Answer 1

I believe your goal as follows.

• You want to put the data validation rules to the column "K2:K" in the sheet destination.
• You want to create the rules by retrieving the values from the columns "B" and "J" in the sheet source.

For this, how about this answer?

Flow:

1. Retrieve values from the both sheets.
2. Create an object for creating the rules.
3. Create the rules.
4. Put the rules to the column "K" in the sheet destination.

Sample script:

function myFunction() {
  // 1. Retrieve values from the both sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const src = ss.getSheetByName("source");
  const dst = ss.getSheetByName("destination");
  const srcValues = src.getRange("A2:J" + src.getLastRow()).getValues();
  const dstValues = dst.getRange("B2:B" + dst.getLastRow()).getValues();

  // 2. Create an object for creating the rules.
  const obj = srcValues.reduce((o, [a,b,,,,,,,,j]) => Object.assign(o, {[a]: [b, j]}), {});

  // 3. Create the rules.
  const rules = dstValues.map(([e]) => ([SpreadsheetApp.newDataValidation().requireValueInList(obj[e]).build()]));

  // 4. Put the rules to the column "K" in the sheet `destination`.
  dst.getRange("K2:K" + dst.getLastRow()).setDataValidations(rules);
}

References:

• newDataValidation()
• setDataValidations()

Added 1:

For your 3 additional questions in your replying, I answer as follows.

• Q1: If in future I would to retrieve the values from further columns in the sheet source. How to edit the script?
  • A1: In this case, please modify [a,b,,,,,,,,j]. Now, a, b and j are Company, Address options 1 and Address options 2, respectively.
• Q2: To have the sheets update with the script, you suggest me to activate a trigger?
  • A2. I cannot understand what you want to do.

• Q3: If I put a value in the col "B" (destination) that is not present in col "A" (source), the script not works. I need this.

  • A3: Please modify above script as follows.

  • From

    - const rules = dstValues.map(([e]) => ([SpreadsheetApp.newDataValidation().requireValueInList(obj[e]).build()]));
    

  • To

    const rules = dstValues.map(([e]) => ([obj[e] ? SpreadsheetApp.newDataValidation().requireValueInList(obj[e]).build() : null]));
    

Added 2:

The question 2, I explain. Naturally when I run the script the dropdown are populated, but if I add a new company, I must to run again the script and so on... to avoid this, how can I do?

For above your additional 2nd question, the sample script is as follows. In this case, when the column "B" of the sheet of destination is edited, the script is run. In this case, the simple trigger can be used.

Sample script:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (range.getColumn() != 2 || sheet.getSheetName() != "destination") return;

  // 1. Retrieve values from the both sheets.
  const ss = e.source;
  const src = ss.getSheetByName("source");
  const dst = ss.getSheetByName("destination");
  const srcValues = src.getRange("A2:J" + src.getLastRow()).getValues();
  const dstValues = dst.getRange("B2:B" + dst.getLastRow()).getValues();

  // 2. Create an object for creating the rules.
  const obj = srcValues.reduce((o, [a,b,,,,,,,,j]) => Object.assign(o, {[a]: [b, j]}), {});

  // 3. Create the rules.
  const rules = dstValues.map(([e]) => ([obj[e] ? SpreadsheetApp.newDataValidation().requireValueInList(obj[e]).build() : null]));

  // 4. Put the rules to the column "K" in the sheet `destination`.
  dst.getRange("K2:K" + dst.getLastRow()).setDataValidations(rules);
}