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

Question

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?

Have you tried to put your list in a series of contiguous cells and use the range like B1:B500? — TheWizEd, Mar 23 '22 at 20:01
I think if you make the ranges large enough you should not have to keep messing with the datavalidations you can just edit the values because it will not utilize empties at the bottom. I think that's true. — Cooper, Mar 23 '22 at 20:01
Thanks both. The data isn't actually in cells, it's returned from a Google Big Query lookup. This is fine when using `requireValueInList(list)` so long as the number of items is less than 500, but when it's over 500 app script blows up and says I have to use `the ‘List from a range’ criteria instead`. I've interpreted that to mean `requireValueInRange(list)` but I can't get the shape of the data correct — Stuart Brown, Mar 23 '22 at 20:11

score 2 · Accepted Answer · answered Mar 23 '22 at 22:33

2

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

answered Mar 23 '22 at 22:33

TheWizEd

7,517
2
11
19

Many thanks for this. I was really hoping I would just be able to pass the data from BigQuery direct to `requireValueInRange` like I do for https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder#requirevalueinlistvalues, but it seems I do have to point it a range of cells – Stuart Brown Mar 24 '22 at 09:54
I realized you are going to have a drop down with more than 500 entries. I pity the end user. – TheWizEd Mar 24 '22 at 12:21
1

if they had to scroll, I would too. But they have auto-complete :) – Stuart Brown Mar 24 '22 at 13:49

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

1 Answers1