Google sheets data validation drop-down menu show values but paste formula?

Question

I've got a list of hyperlinks stored in a range and I would like to have the data validation in a different column allow you to choose from the link "text" and then paste the full cell formula. The way it works at the moment the data validation cell only contains the cell value.

Link to example sheet

Some sheet function code:

C2: =HYPERLINK("#rangeid=901904080","Cell E2")
C3: =HYPERLINK("#rangeid=951806747","Cell E3")
C4: =HYPERLINK("#rangeid=743771733","Cell E4")

Auto recorded script code:

function DV() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C2').activate();
  spreadsheet.getRange('A2:A').setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange(spreadsheet.getRange('Sheet2!$C$2:$C'), true)
  .build());
};

Is there something I can do to the script code to make it paste the formula in stead of the value? Or is the best option to try and trigger on when a new value is chosen and then do something like:

Att! Pseudo code

onEdit(e){
  //pseudo code
  if e.range in column A{
    var sourceRange = sheet.getRange("C2:C")
    var index = sourceRange.indexOf(e.range.getValue())
    e.range.setFormula(sourceRange[index].getFormula())
  }
}

Answer 1

It's a bit tricky, but I think I found the solution using the DataValidationBuilder. It's similar to yours but using getFormulas() along with the validation property requireValueInList() which asks for an array of Strings.

The main issue is when you select the formula, the "invalid rule" notification jumps in, as the formula changes the cell value to "Cell C3", for example. So in order to work around this paradox you need set setAllowInvalid(boolean) to false:

function DV(){

  var spreadsheet = SpreadsheetApp.getActive();
  var cells = spreadsheet.getRange('A2:A');
  var rule = SpreadsheetApp.newDataValidation()
             .requireValueInList(spreadsheet.getRange('C2:C').getFormulas())
             .setAllowInvalid(false)
             .build();
  cells.setDataValidation(rule);


}

Answer 2

As the 500 items limit isn't acceptable in my case I have solved the problem by checking for a data validation rule on an edited cell and if one exists, swap the value for the data.

MWE:

function onEdit(e){
  if (e.range.getDataValidation() != null){
    swapValueForFormula(e.range);
  }

function swapValueForFormula(cell){
  var val = cell.getValue();
  var rule = cell.getDataValidation();
  var source = rule.getCriteriaValues()[0];
  var vals = source.getValues();
  var index = vals.join().split(",").indexOf(val);
  if (index != -1){
    var formula = source.getCell(index+1, 1).getFormula()
    cell.setFormula(formula)
  }
}

Thanks for all your comments and suggestions that lead me to this answer.