Data Validation range from different spreadsheets

Question

Data Validation range from different spreadsheets

2.3k Views Asked by AudioBubble At 05 July 2020 at 09:37

I have in my main spreadsheet a dropdown with data validation of a range from another tab in the same spreadsheet with data imported from another spreadsheet or with IMPORTRANGE function or imported with a script.

In both cases the main spreadsheet is very slow cause I have a lot of tab with data imported with both methods.

There is a way to do the data validation of the dropdown in the main sheet taking the data I need directly from the other spreadsheets without import them previously in the main spreadsheet with the IMPORTRANGE function or with a script?

I have tried to write a draft script but not works:

function externalSheetDataValidation() {
    var cell = SpreadsheetApp.getActiveRange();
    var dataValidationSheet = SpreadsheetApp.openById("xxxxxxxxxx");
    var sheet = dataValidationSheet.getSheets()[0];
    var range  = sheet.getRange("B2:B5000");
    var rule = SpreadsheetApp.newDataValidation()
        .requireValueInRange(range, true)
        .setAllowInvalid(false)
        .build();
    cell.setDataValidation(rule);  
    Logger.log(dataValidationSheet.getName());
}

Original Q&A

There are 2 best solutions below

**Cooper** · Answer 1 · 2020-07-05T17:22:55.830000

Cached Dropdown Dialog

GS:

function getSelectOptions(){
  const cs=CacheService.getScriptCache();
  const v=JSON.parse(cs.get('cached'));
  if(v){return v;}
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Options');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var options=[];
  for(var i=0;i<vA.length;i++)
  {
    options.push(vA[i][0]);
  }
  cs.put('cached', JSON.stringify(vA), 300)
  return vA;
}

function showMyselectionDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah2'), 'Selections');
}

html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <script>
    google.script.run
  .withSuccessHandler(function(vA) {
    updateSelect(vA);
  })
  .getSelectOptions();

function updateSelect(vA,id){//the id allows me to use it for other elements
  var id=id || 'sel1';
  var select = document.getElementById(id);
  select.options.length = 0; 
  for(var i=0;i<vA.length;i++)
  {
    select.options[i] = new Option(vA[i],vA[i]);
  }
}
  </script>
  <body>
     <select id='sel1'></select>
  </body>
</html>

**Iamblichus** · Answer 2 · 2020-07-07T14:33:12.127000

You want to populate a dropdown based on the values of a range from a different spreadsheet.
You are currently importing those values to a sheet in your spreadsheet in order to use them via requireValueInRange.
You would like to skip the import process.

If that's the case, you can just do the following:

Create a function that returns a simple array with the values from the source range:

function importSheetA() { 
  return SpreadsheetApp.openById('xxxxx')
                       .getSheetByName('xxxxx')
                       .getRange('xxxxx')
                       .getValues()
                       .flat(); // This ensures a simple array is returned
}

Populate the dropdowns with requireValueInList instead of requireValueInRange, using the values returned by importSheetA:

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

Note:

You could update the populated options when the source range is edited if you install an onEdit trigger, and you could also specify what range of cells should be populated with dropdowns without them being necessarily selected, but I'm not sure that's what you want.

Update:

If your data has more than 500 items, value in list criteria is not an option. Your only other option would be to use List from a range instead, but as you said, this would require the source range to be on the same spreadsheet as the dropdown, which you wanted to avoid.

As a workaround, I'd suggest you to programmatically copy the data to a hidden sheet in the target spreadsheet, and use the data in this hidden sheet as your source range when creating the dropdown. For example, this:

function copyRange() {
  var cell = SpreadsheetApp.getActiveRange();
  var rangeNotation = "B2:B5000"; // Change according to your preferences
  var sourceData = SpreadsheetApp.openById(xxxxx)
                       .getSheetByName(xxxxx)
                       .getRange(rangeNotation)
                       .getValues();
  var targetSS = SpreadsheetApp.getActiveSpreadsheet();
  var hiddenSheetName = "Hidden source data"; // Change according to your preferences
  var hiddenSheet = targetSS.getSheetByName(hiddenSheetName);
  if (!hiddenSheet) hiddenSheet = targetSS.insertSheet(hiddenSheetName);
  var sourceRange = hiddenSheet.getRange(rangeNotation);
  sourceRange.setValues(sourceData);
  hiddenSheet.hideSheet();
  var rule = SpreadsheetApp.newDataValidation()
                           .requireValueInRange(sourceRange, true)
                           .setAllowInvalid(false)
                           .build();
  cell.setDataValidation(rule);
}

Reference:

requireValueInList(values, showDropdown)

Data Validation range from different spreadsheets

There are 2 best solutions below

Note:

Update:

Reference:

Related Questions in VALIDATION

Related Questions in GOOGLE-APPS-SCRIPT

Related Questions in DROPDOWN

Related Questions in IMPORTRANGE

Trending Questions

Popular # Hahtags

Popular Questions