Data Validation range from different spreadsheets

0
votes

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());
}
2
validationgoogle-apps-scriptdropdownimportrange
Ans so how can I solve for you in order to increase the speed of the main spreadsheet? – user13509354
And so for you there in another solution to speed up the main spreadsheet? – user13509354
I use datavalidation cause in the main tab of the main sheet I need of all the data from another sheet togheter to other data of the main sheet. – user13509354
Datavalidation is located in col A with a VLookup from the tab with the importrage. Then togheter with other data inserted in the following columns, I must to generate some documents, and so I need of this combination of data. – user13509354
Yeah, and this huge quantity of cells is the reason why this gets slow. But this way you could avoid importing the data to another sheet. What I would do in your case is create an onEdit trigger on your source spreadsheet which would update the data validation when the source range is edited. What do you think of that? – Iamblichus

2 Answers

0
votes

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>
0
votes
  • 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: