Convert Excel VBA to Google Appscript: How To Create Drop Down List But Show Different Values In Google Sheet

Question

I am a newbie and desperately need to convert this excel VBA code(macro) to Google app script for my application. The script and logic can be found here https://www.extendoffice.com/documents/excel/4130-excel-drop-down-list-show-different-value.html . I have no access and cannot install the google extension Macro Converter add-on to convert the script due to system-admin restrictions on Chrome extension (not allowed) of my office issued laptop.

Any help is appreciated. Thank You.

Here is the code

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    selectedNa = Target.Value
    If Target.Column = 5 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

Answer 1

I am not familiar with how to translate it fully however the link provides enough insight on what it is needed, it might be a good start this sample script.

function onEdit(e) {
    var ss = e.source;
    var cell = e.range;

    //Get your display value and id 
    var ids = ss.getRange('DV!A2:B13').getDisplayValues();

    SpreadsheetApp.getActive().getSheetByName("DV").getRange('C1').setValue(JSON.stringify(ids))

    //Check if modified cell is within the drop-down list range example Sheet1!B2:B
    //Check if modified cell is in a specific sheet and the value is not null
    // if(ss.getActiveSheet().getName() =='Sheet1' && cell.getRow() > 1 && cell.getColumn() == 2 && e.value != null){

      SpreadsheetApp.getActive().getSheetByName("DV").getRange('D1').setValue(JSON.stringify(e.value))

      //Search for the the id based on the modified cell's value
      var matchedId = ids.find(id=>{
        SpreadsheetApp.getActive().getSheetByName("DV").getRange('E1').setValue(JSON.stringify(id[0]))
        return id[0] == cell.getDisplayValue();
      })

      //Update the cell value
      cell.setValue(matchedId[1]);
    // }

This app script can be found if you click on Extensions > App Script from this Sheet example that you can review

I also found this excellent thread on how functions are strutured and how they can be converted and how the "end sub" and "end if" can be edit:

function Worksheet_Change(ByVal) {
  Statements Here;
};