importing range from another book using data validation in google sheets-CodePudding

I want a drop-down list here :

But the range is in another book called : sheet with range

I've tried to use data validation with "IMPORTRANGE" without success , making the following:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1A8v4TEgibkesX729YcjEDn8RkcogutT_8pgIkggaBfI/edit#gid=0", "Hoja 1!A1:A5)

I get the following :

CodePudding user response：

this is not possible. you need to import your list in some column first (you can hide it in 3 ways) and only then you can create your dropdown

CodePudding user response：

Adding to @player0 answer, first you need to import the data in the same sheet where you want the data validation, then set the data validation to the imported list.

To do this directly in the data validation criteria, you can request this feature to be available through Google Sheets by going to a Google Sheet file, click on ‘Help’ > ‘Help Sheets improve’ and give a description of your request.

If you don't want to import data from another sheet to the current one, a workaround would be to use Apps Script. You can use the following script that @Iamblichus answered in this similar question.

function importSheetA() {
  return SpreadsheetApp.openById('xxx') //ID for the other book where you have the data
                       .getSheetByName('xxx') //Sheet name where you have the data
                       .getRange('xxx') //Range where the data is, lets say for example: A1:A20
                       .getValues()
                       .flat(); // This ensures a simple array is returned
}

function populateDropdown() {
  var values = importSheetA();
  var rule = SpreadsheetApp.newDataValidation()
                           .requireValueInList(values, true)
                           .setAllowInvalid(false)
                           .build();
  var range = SpreadsheetApp.getActiveRange();
  range.setDataValidation(rule);  
}

After you specify the information in the script, go to the sheet, click the cell where you want the drop-down, return to Apps Script and run the populateDropdown() function.