TypeScript Code

link - learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-data-validation

This script creates a drop-down selection list for a cell. It uses the existing values of the selected range as the choices for the list.

function main(workbook: ExcelScript.Workbook) { 
  let selectedRange = workbook.getSelectedRange(); 
  let rangeValues = selectedRange.getValues(); 

  // Convert the values into a comma-delimited string. 
  let dataValidationListString = ""; 
  rangeValues.forEach((rangeValueRow) => { 
    rangeValueRow.forEach((value) => { 
      dataValidationListString += value + ","; 
    }); 
  }); 

  // Clear the old range. 
  selectedRange.clear(ExcelScript.ClearApplyTo.contents); 

  // Apply the data validation to the first cell in the selected range. 
  let targetCell = selectedRange.getCell(0,0); 
  let dataValidation = targetCell.getDataValidation(); 

  // Set the content of the drop-down list. 
  dataValidation.setRule({ 
      list: { 
        inCellDropDown: true, 
        source: dataValidationListString 
      } 
    }); 
}