Replace text with hyperlinked version onEdit

Question

I have a google sheet with a list of people's initials in range A4:A. These initials are each hyperlinked to a different part of my sheet.

Column MM contains many cells that all have a data validation dropdown list referencing the initials list in A4:A

I need a script that triggers on edit so that when a user selects initials in the dropdown list in column MM, the script replaces it with the hyperlinked version from column A

My scripting knowledge is rudimentary. Normally I'd do a replace something like the below but I'm not sure how to implement the search so it takes the value from column MM and searches for it in columnA (or even if this would take the hyperlink function along with it.

`function replaceText(){

var oldText = "AB"; var newText = "AB hyperlinked";

var sheet = SpreadsheetApp.getActive().getSheetByName('SHOTS'); sheet.getRange("MM1:MM" + sheet.getLastRow()).createTextFinder(oldText).replaceAllWith(newText); }`

Thanks in advance for any help given

Answer 1

If you have the chance of having an additional column, you can do a workaround without script with FILTER:

=FILTER(A2:A,A2:A=MM1)

Or if you want it as an array for many rows

=BYROW (MM1:MM,LAMBDA(each,IF(each="","",FILTER(A2:A,A2:A=each))))

Check in this image how when I chose value "b" it returns the hyperlinked value in the coloured column

OPTION B with SCRIPT:

Please check if MM is column 351, if not change that number. Run it once from the console (it will give an error, but it's just for approving the permissions), then go to your sheet and start trying with your dropdown:

function onEdit(e) {
 var ecolumn = e.range.getColumn()
 if (ecolumn == 351){
  var sheet = SpreadsheetApp.getActive()
  var range = sheet.getActiveRange();
  var value = range.getValue()
  var lookuprange = sheet.getRange("A:A").getValues().map(n => n[0])
  var index = lookuprange.indexOf(value)+1

  if (index != 0){ 
    var link = sheet.getRange("A"+index).getRichTextValue().getLinkUrl()
    var richValue = SpreadsheetApp.newRichTextValue()
      .setText(value)
      .setLinkUrl(link)
      .build();
    range.setRichTextValue(richValue);}}
}

REFERENCE: Apps Script: how to get hyperlink from a cell where there is no formula