# Creating a Dynamic Dropdown List in Google SpreadSheets via Script

Goto sheets.google.com - I saved this spreasheet as Temp.

Now click on the Extensions > App Script in the top-level menu as shown here :

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1677400548649/015cc1c2-e256-476c-8fe0-fe6ea9c9855d.png align="center")

Now, you'll see an editor like this below :

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1677400595721/54e22063-db82-4c12-877a-37def44d99e1.png align="center")

Now enter this code as is :

```javascript
function onOpen()
{
    dynamic_dropdown();
}

function dynamic_dropdown()
{
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getSheetByName('Sheet1');

    sheet.getRange('B2').setValue('Mumbai');
    sheet.getRange('B3').setValue('New Delhi');
    sheet.getRange('B4').setValue('Chennai');
    sheet.getRange('B5').setValue('Bengaluru');
    sheet.getRange('B6').setValue('Kolkatta');
    sheet.getRange('B7').setValue('Kochi');
    sheet.getRange('B8').setValue('Ahmedabad');
    sheet.getRange('B9').setValue('Hyderabad');

    // Set the data validation for cell D2 to require value from B2:B9, with dropdown menu.
    var cell = sheet.getRange('D2');    
    
    var firstrow = 2;    

    // var range = SpreadsheetApp.getActive().getRange('B5:B9');    
    // https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)
    var range = sheet.getRange(firstrow, 2, sheet.getLastRow() - firstrow + 1, 1); // 2,2,9,1
    
    var data = range.getValues(); console.log(data);
    
    var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range, true).build();
    cell.setDataValidation(rule);  
}
```

Now when you reload the sheet OR if you just hit Run in the App Script, this code will run and you'll see a pre-filled column and a drop-down next to it like this :

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1677400732282/93cca3da-4080-411e-ad99-4f0af59387d0.png align="center")

References :

[https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#newDataValidation()](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#newDataValidation())

[https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer))