Google-sheets – Pull unique values from other sheets as new sheets keep getting added

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-arrayformula

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.

Best Answer

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

Related Solutions

Google-sheets – Unique Values From a Matrix

Possible but may require some 'compromising':

=arrayformula(right(split(FILTER(B3:B, A3:A>0, LEN(B3:B)>0), ","),1))

in D1 to F10 does find the 2 (which should have been 'underneath' where you put your formula for the purposes of your example) - it also strips out Value, though this could be added back later if required.

in say G1 and copied down to suit:

=arrayformula(LARGE(value(D$1:F$10),row()))

then with all your values in a single column, in say H1 and copied down to suit (upto #NUM!):

=unique(G:G)

The results will be in reverse order from as shown by you (sort?) because of use of LARGE rather than SMALL because if positive values only all the 0 would have come first and there are quite a few of those.

Concatenate results with ="Value "&H1 etc if required.

Google-sheets – Getting values from other sheets with a custom function

Custom functions are re-run when their parameters change. For example, if you have =myfcn("sheetname", 1, 1, 1, 1) and change some of parameters here, the function will run again. But if you go and change the content of cell sheetname!A1, the function will not re-run, since there is no indication in the spreadsheet that its output depends on sheetname!A1.

Alternative

To get all formulas from a sheet into another sheet as text, use a script function like this:

function copyFormulas() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == 'Watched Sheet') {   
    var data = sheet.getDataRange();
    var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Formulas');
    target.getRange(1, 1, data.getHeight(), data.getWidth()).setValues(data.getFormulas());
  }   
}

The function takes formulas from 'Watched Sheet' and puts them into 'Formulas' sheet as text. This script can run periodically, or on every edit, or on every change (including adding rows, etc). See triggers.

Best Answer

Related Solutions

Google-sheets – Unique Values From a Matrix

Google-sheets – Getting values from other sheets with a custom function

Alternative

Related Topic