Create dynamic dropdown list in the Google apps script [closed]

Question

I would like to dynamically change the value of the candidate list in the cell validation feature of a Google Spreadsheet using the Google Apps Script. I need to take values from other spreadsheet.

Answer 1

This is something I've grappled with, if I understand you correctly. Normally I use importRange to "localise" the remote list but this is not yet working in the NEW Google Spreadsheet ... so you might try a more direct approach like the following:

function setDropdown(){   
   var sourceSS = SpreadsheetApp.openById('yourSpreadsheetID');  // replace yourSpreadsheetID with the value in the sheet URL
   var ss = SpreadsheetApp.getActive();
   // get the range where the dynamic dropdown list is kept in the source spreadsheet
   var dynamicList = sourceSS.getSheetByName('Dropdown List Sheet').getRange('A2:A');   // set to your sheet and range
   // define the dropdown/validation rules
   var rangeRule = SpreadsheetApp.newDataValidation().requireValueInRange(dynamicList).build();
   // set the dropdown validation for the row
   ss.getRange('sheet 1!A5:A10').setDataValidation(rangeRule); // set range to your range
}

I suspect that the above does not work yet in new Google Spreadsheet. The following modification converts the range to a list of values instead. This was tested on a short list, and does the job...

function setDropdown(){   
   var sourceSS = SpreadsheetApp.openById('yourSpreadsheetID');  // replace yourSpreadsheetID with the value in the sheet URL
   var ss = SpreadsheetApp.getActive();
   // get the range where the dynamic dropdown list is kept in the source spreadsheet
   var dynamicList = sourceSS.getSheetByName('Dropdown List Sheet').getRange('A2:A10');   // set to your sheet and range
   var arrayValues = dynamicList.getValues();
   // define the dropdown/validation rules
   var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(arrayValues);
   // set the dropdown validation for the row
   ss.getRange('sheet 1!A5:A10').setDataValidation(rangeRule); // set range to your range
}