Data Validation range from different spreadsheets with onEdit trigger

Question

I have in my source spreadsheet a dropdown with data validation of a range from another tab in the same spreadsheet with data imported from another spreadsheet with the following script.

The main spreadsheet is very slow cause I have a lot of tab with data imported with the script, so how can I create an onEdit trigger on my source spreadsheet which would update the data validation when the other spreadsheet are edited?

The script:

function importSheetA() {
var values1 = SpreadsheetApp.openById('xxxxx').
  getSheetByName('xxxxx').getRange('A1:EO2000').getValues(); 
SpreadsheetApp.getActive().getSheetByName('masterop').
  getRange(1,1,values1.length,values1[0].length).setValues(values1);  
}

Update:

With this script I can show the dropdown on 1 sheet ("id1"). How to do this for many spreadsheets ID?

function installTrigger() {
  ScriptApp.newTrigger('changeValidationRule').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

function changeValidationRule(e) {
  let values = e.source.getRange("A1:A3").getValues().flatMap(value => value); //flatting the row structures into a single dimension array
  let sourceSheet= SpreadsheetApp.openById("id1").getSheets()[0];
  let rule = SpreadsheetApp.newDataValidation().requireValueInList(values, true); // The boolean stands for `show dropdown`
  sourceSheet.getRange(1, 1).setDataValidation(rule);
}

Answer 1

Approach

You can achieve this behavior using Apps Script onEdit() installable Trigger.

The trigger has to be installed because it will need authorization to access the source Spreadsheet.

The trigger will be placed in the validation sheet, and will trigger when this is manually updated. The trigger will run a function that will update the validation rule based on the values that you want to take in account.

For simplicity, I assumed that the values are in a column in the validation sheet in the range A1:A3.

I will fetch these values and I will build a Validation rule on the desired range on the source spreadsheet.

Code

function installTrigger() {
  ScriptApp.newTrigger('changeValidationRule').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

function changeValidationRule(e) {
  let values = e.source.getRange("A1:A3").getValues().flatMap(value => value); //flatting the row structures into a single dimension array
  // Initialize the variables
  let ids = ['id1','id2','id3']; // Add here the ids of your source sheets
  let rule = SpreadsheetApp.newDataValidation().requireValueInList(values, true); // The boolean stands for `show dropdown`
  // Loop through the ids and update the rule
  ids.map(id => {
      let sourceSheet= SpreadsheetApp.openById(id).getSheets()[0];
      sourceSheet.getRange(1, 1).setDataValidation(rule);
  });
  
}

Disclaimer

In your example you import those values in the source spreadsheet. You can actually reference a Range in your source spreadsheet, but I consider using a values list easier, since we are working directly on the validation spreadsheet. I will put a reference to this method below.

Edit

There is a limit on value items you can use in a validation rule. This limit is 500 items. If you need to validate more than 500 items there is no other way than to import all the values in a Validation Sheet in your Source Spreadsheet. You will be able to reference this range in your Data Validation Rule and you can build a script that updates this range whenever the Validation Spreadsheet changes. Here is an idea:

function installTrigger() {
  ScriptApp.newTrigger('updateValidationRange').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

function updateValidationRange(e) {
  let values = e.source.getRange("a-big-range-in-validation-spreadsheet").getValues();
  // Initialize the variables
  let ids = ['id1','id2','id3'];
  // Loop through the ids and update the rule
  ids.map(id => {
      let sourceSheet= SpreadsheetApp.openById(id).getSheetByName("Validation Sheet"); // Be sure to change this name in order to reflect your validation sheet inside the source Spreadsheet
      sourceSheet.getRange("the-copy-of-the-big-range-in-the-source-spreadsheet").setValues(values); // It's crucial this range is the same size than the one in the validation spreadsheet
  });
  
}

References

requireValueInList

requireValueInRange

installable Triggers