Putting info from multiple tabs into a master tab no matter the quantity or name of the contributing tab(s) (Google Sheets)

Question

I have seven columns (CD, CE, CF, CG, CH, Ci, and CJ). This same format reoccurrs across multiple tabs within my sheet. Right now there are only three or four of these identically formatted tabs, but over time more will be created, each having the same type of data in the same seven columns, in the same place.

I want to create a "master" tab, in which all the data from all of the other tabs is aggregated into one master sheet. These columns are A,B,C,D,E,F, and G.

For example if tab1 has 3 entries, tab2 has 2, and tab3 has 5, the master tab should have 10 entries listed.

Please note that I can't just specify a specific range from which to source the data, since I don't know how many of these identical source tabs will be created over time or what they will be named. It needs to seek out those specific columns (CD-CJ) across all tabs that exist in the sheet, and put them together in the mater no matter the tab. Note that CD-CJ will only contain data that should participate in being aggregated into the master.

As far as I can tell, this might require a google apps script.

RESOLVED: Please see both Century Tuna's and my answer!

Answer 1

Solution:

Try this sample implementation, but this needs to be changed based on the current structure of your sheet:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var mastersheet = ss.getSheetByName("Master"); //Indicate the master sheet name here
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); 
  var alldata = []; 

  sheets.forEach(x => {

    if(x.getSheetName() != "Master"){ //Make sure that the master sheet name is included here to exclude from the iteration

    var data = x.getRange(2,3, x.getLastRow() - 1, 7).getValues();
    alldata.push(data); 
    
    }
  })

  var finaldata = alldata.flat(); 
  mastersheet.getRange(2,1, finaldata.length, 7).setValues(finaldata);

}

Take note that var data = x.getRange(2,3, x.getLastRow() - 1, 7) getRange values is referenced by row number and column number as indicated in this documentation, so in your case get the numerical position of CD as the starting column and 7 as the number of columns.

x.getLastRow() is used as numRows in order to fetch the last row on each sheet.

Sample sheets (All data used are just samples):

Sheet2 Sheet3 Sheet4

Master sheet when the script is run: