Include filename of source spreadsheet

Question

I have 2 spreadsheets for 2 employees and I combine them into 1.

From employee 1:

From employee 2:

I use importrange to combine the sheets. I also know now how to get the spreadsheet file name by using this google-apps-script code.

/**
 * Gets the Sheet Name of a selected Sheet.
 *
 * @param {number} option 0 - Current Sheet, 1  All Sheets, 2 Spreadsheet filename
 * @return The input multiplied by 2.
 * @customfunction
 */

function SHEETNAME(option) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()
  var thisSheet = sheet.getName();

  //Current option Sheet Name
  if(option === 0){
    return thisSheet;

  //All Sheet Names in Spreadsheet
  }else if(option === 1){
    var sheetList = [];
    ss.getSheets().forEach(function(val){
       sheetList.push(val.getName())
    });
    return sheetList;

  //The Spreadsheet File Name
  }else if(option === 2){
    return ss.getName();

  //Error  
  }else{
    return "#N/A";
  };
};

I want to automatically fill the column QA NAME with the source spreadsheet name:

Answer 1

The code you describe gets the Sheet name, but it is entirely out-of-context in your scenario - you have no linkage between the name of the employee and the relevant Sheet. Instead, I suggest that you create a Helper Sheet containing a two column table:

Column A      Column B
Emp Name      QA Name
Jame Doe      Employee#1
John Doe      Employee#2

Then Insert this formula in the QA NAME cell and copy down as many rows as required:

=vlookup(C2,'wa_134804'!$A:$B,2,false)

where:
C2 = "Designer",
wa_134804'!$A:$B = the helper table,
2 = select the value on Column 2 of the Helper (The QA Name), and
false = exact match only.