How to pull the ID corresponding name from "Data Sheet" to "Selection Sheet"

[G Develop]

Hello,

I'm trying to get this sheets for our team but can't get the Corresponding name to populate in "Selection Sheet" when the ID is selected from the dropdown list,,,,

Thx to see screenshot attached

Below is my code:

//================================

function addName(){
   
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var selSheet = ss.getSheetByName("Selection");
    var datSheet = ss.getSheetByName("Contributions");
   
    var lastR = datSheet.getLastRow()+1;
   
    var id = selSheet.getRange('B24').getValue();
   
   
   
  for(var i = 2; i <= lastR; i++){
   
    if(id == datSheet.getRange(i, 1).getValue()){
   
      var name = datSheet.getRange(i, 2).getValue();
     
    }
 }
     
    datSheet.getRange(lastR,2).setValue(selSheet.getRange("B25").getValue());
   
}

//======================================

[Laurie Nason]

Hi,

I would use a simple Vlookup for the cell showing first/last name on the green tab (no code required) - but I would probably do the selection using the name and then lookup the ID field (it's usually easier to have someone select a name, rather than an ID number)

If you select the name the Vlookup would be something like this =VLOOKUP(B24,{Data!B:B,Data!A:A},2,FALSE)

B24 = the dropdown with the name,

Data!B:B is the column with the names

Data!A:A is the column with the ID numbers 

You can switch them if you want to be able to select by ID.

Laurie

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8948a502-4915-49ba-aa44-e0b4b4abc5e7n%40googlegroups.com.

[G Develop]

Thx Laurie; that's exactly what (vLookup) we are currently using and we want to hard code it because the file is handled by students and they keep deleting the vlookup formula...

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/v9BG3WLjz_c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BA7ZWJb2Cq0df5Ct5zL-mvRtuYg%3DuvDhom5j3vV1o-%3DOD4TXw%40mail.gmail.com.

[Laurie Nason]

Ah - well, the nice thing about Vlookup is that it works with Arrayformulas - and then you can lock the column/rest of the sheet from little fingers - if they are taking their own copy of the sheet - then this might be tricky.

The other question is - do you need the ID number in that sheet at all - would just a dropdown for name work and don't have an ID number there? 

One thing I have done in the past to have name and number available is to concatenate them on the first sheet so have e.g. Laurie Nason (123456) as the selection in the dropdown - then you can use a regexextract to pull out the ID number when you need to.

Good luck - if you want any more help/opinion :-) then let me know. I spend my life trying to stop people (admittedly, it's usually teachers rather than students) messing up my formulas in sheets!!

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAPuQ%3DX7fXu1zfY%3D064rxpU0K0MFs4VhsFQM0FA4%3D8Ad5TCqDGA%40mail.gmail.com.