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

From employee 1:

Image 2 1

From employee 2:

Image 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 = [];
    return sheetList;

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

    return "#N/A";

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

enter image description here


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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.