CODEWITHSUNDEEP
javascriptvalidationgoogle-apps-script
Eric Johnson
Eric Johnson

Reputation: 89

How to pull data validation from another sheet in Google App Scripts

I'm trying to build a data validation for a cell from a range in a different sheet. I've tried the below code, and I continue to get this error "". Any help would be greatly appreciated.

function dataValidations(){
  var builder = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Basic Info");
  var invMgmt = SpreadsheetApp.openById("1eGNsJ2iB_IOzcfJe7DyFV_0WhzdQhvW2Lqdzfta2eV4");
  var partners = invMgmt.getSheetByName("Partners and Locations").getRange(2,1,75,1).getValues();
  var partnerRule = SpreadsheetApp.newDataValidation().requireValueInRange(partners).build();
  
  var origin = builder.getRange("B4");
  
  origin.setDataValidation(partnerRule);

}

Upvotes: 1

Views: 650

Answers (1)

Tanaike
Tanaike

Reputation: 201418

In the case of requireValueInRange(range), range is the range object. In your script, the values retrieved by getValues() is 2 dimensional array. I think that this might be the reason of your issue. So in order to remove this issue, how about the following modification?

From:

var partners = invMgmt.getSheetByName("Partners and Locations").getRange(2,1,75,1).getValues();
var partnerRule = SpreadsheetApp.newDataValidation().requireValueInRange(partners).build();

To:

var partners = invMgmt.getSheetByName("Partners and Locations").getRange(2,1,75,1).getValues();
var partnerRule = SpreadsheetApp.newDataValidation().requireValueInList(partners.flat()).build();  // Modified

Note:

  • When SpreadsheetApp.openById("###").getSheetByName("###").getRange(###) is used for requireValueInRange, no error occurs and the retrieved values are the correct from other Spreadsheet. But when I confirmed the data validation in the cell, it is found that SpreadsheetApp.getActiveSpreadsheet() is used instead of SpreadsheetApp.openById("###"). So in this case, it seems that using requireValueInList is suitable.

References:

Upvotes: 3

Related Questions