requireValueInRange error - The parameters (number[]) don't match the method signature-CodePudding

I have a google app script which (amongst other things) provides a series of drop downs in cells based on the values chosen in the previous cell. Essentially, when a cell value is selected, that value is used in a Google Big Query lookup, and the next cell is populated with values from Big Query.

I enforce the validation of the options available in the dropdown via the function below

function applyValidationToCell(list, cell) {
 
 //create a valdation rule, essentially that the available values must be in the list passed ot the function
 var rule = SpreadsheetApp.newDataValidation()
   //.requireValueInList(list)
   .requireValueInRange(list)
   .setAllowInvalid(false)
   .build();
 
 cell.setDataValidation(rule);
}

This all worked well until I came across a case where there are over 500 options in the select list and I hit the error The data validation rule has more items than the limit of 500. Use the ‘List from a range’ criteria instead.

I’ve looked at the docs at https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder#requireValueInRange(Range) and also read this SO questions Need answer to “The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues” error and then @TheMaster's helpful answers at How do you resolve a "The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues" error and What does the range method getValues() return and setValues() accept?

When I was using .requireValueInList(list) the shape of the data passed to the function was ["Donruss","Leaf","Panini","Topps"]

To deal with requireValueInRange I’ve tried modifying the structure to 2D arrays as suggested in the answers above e.g. [["Donruss"],["Leaf"],["Panini"],["Topps"]] and [["Donruss","Leaf","Panini","Topps"]]

However, I always get the error Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.DataValidationBuilder.requireValueInRange.

Can anyone please advise on the correct way to pass values to requireValueInRange?

CodePudding user response：

Description

Here is a simple example of how to use a range for a data validation rule.

In your case save the Big Query results to a sheet using setValues() then flush() then add the data validation rule for the cell your interested in.

Script

function runTest() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let cell = sheet.getRange("A6");
    let range = sheet.getRange("A1:A4");
    let rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
    cell.setDataValidation(rule);
  }
  catch(err) {
    console.log(err);
  }
}

Reference

https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder