create drop down list using data from another spreadsheet

Question

How to implement a drop-down list with values ​​from another table? There are 3 tables: Report; Projects; Tasks. In the report you need to import projects and tasks to them

Answer 1

Here's the way to create the drop down with data from another sheet.

  function validate() {
  var ss1=SpreadsheetApp.getActiveSpreadsheet();
  var sht1=ss1.getSheetByName('Val1');
  var ss2=SpreadsheetApp.openById('SpreadSheetID');
  var sht2=ss2.getSheetByName('Dat1');
  var rng1=sht1.getRange('A2:A');
  var rng2=sht2.getRange('A2:A7'); 
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(rng2).build();
  rng1.setDataValidation(rule);
}

Here's an image of the Final Drop Down in Dat1:

Here's an image of the other sheet's data in Val1:

Answer 2

function getProjects() {
  var projectSheet = SpreadsheetApp.openById('id');
  var projectsList = projectSheet.getSheets()[0];
  var range = projectsList.getRange('A2:A400').getValues();
  Logger.log(range);

  var reportSheet = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = reportSheet.getActiveSheet();
  var cell = currentSheet.getActiveCell();  

  var projects = [];
for (var i = 0; i < range.length; i++) {
 if (range[i] != ''){
   projects.push(range[i]);
    Logger.log(projects[i]);
 } else {
   break;
  }
} 
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(projects);
  cell.setDataValidation(rule);  
}