Pull unique values from other sheets as new sheets keep getting added

Question

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master sheet, that will be another new sheet that I have to grab values from. Here's a quick example (very simplified) of the general layout I'll have:

https://docs.google.com/spreadsheets/d/1yHDQXzOXtXbEQ7Jb3JtF93ycKq6RZ1nLRXZhsSySaKE/edit#gid=55113996

So the idea is that a new copy of a 'List' template will be created a user with new data that needs to be brought into the master sheet, and the name of that new sheet will be added as a column in Sheet1; I don't want to have to update the formula that populated Column A whenever a new list is added as a new column (which is done after a sheet of the same name is created). At that point, the unique values from that new sheet should be added to Column A of Sheet1. Not only must the values be unique in the new sheet, but they have to be unique across all sheets. In the example sheet I provided above, the value 456 appears in List 1 and List 2, but it should only appear once in Sheet1.

I'm familiar with how to reference cells/ranges from other sheets, as well as how to reference another sheet based on a value from another cell, but I don't know how I would necessarily be able to iterate through all the sheets indicated by the column names provided in Sheet1.

Answer 1

• paste this script into your spreadsheet:

  function SNAME(option) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()
  var thisSheet = sheet.getName(); 
  if(option === 0){                  // ACTIVE SHEET NAME =SNAME(0)
  return thisSheet;
  }else if(option === 1){            // ALL SHEET NAMES   =SNAME(1)
  var sheetList = [];
  ss.getSheets().forEach(function(val){
     sheetList.push(val.getName())
  });
  return sheetList;
  }else if(option === 2){            // SPREADSHEET NAME  =SNAME(2)
  return ss.getName();
  
  }else{
  return "#N/A";                     // ERROR MESSAGE
  };
  };

• paste this formula in Sheet1!B1 cell:

  =TRANSPOSE(FILTER(SNAME(1), SNAME(1)<>SNAME(0)))

  

• paste this formula in Sheet1!B2 cell:

  =IF(B1<>"", INDIRECT(B1&"!A:A"), )

  

• paste this formula in Sheet1!A2 cell:

  =ARRAYFORMULA(SORT(UNIQUE(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
  IF(B2:Z<>"", "♦"&B2:Z, )),,999^99)),,999^99), "♦")))))

  

• spreadsheet demo

---

note: you can add onEdit trigger in order to recalculate the script