Spreadsheet Script Validation: The Data Validation Rule Argument "=employees!b1:b1000" Is Invalid

I'm trying to validate employees based on another spreadsheet with the following code: function validation() { var globals = SpreadsheetApp.openByUrl('https://docs.google.com/myu

Solution 1:

Apps Script and Google Sheets don't allow you to use data from other spreadsheets to define data validations. The error you are getting is the result of Apps Script looking in the current spreadsheet for the 'Employees' sheet and not finding it.

Instead of attempting to use the data in the other spreadsheet directly, you can have an Apps Script function copy that data to the current spreadsheet (perhaps in another sheet or hidden column if you want to hide it), and then set up the data validation from the local copy.

As noted, the range notation format 'A1:A' is valid and can be used here without issue.