Google Sheets Mastery - Combining Data From Multiple Sheets

Having data spread over many, many sheets is a pain to combine. You have to type in each sheet name, remember the proper ";"s and try not to mess up. If it is 5+, there is no really nice way to do it manually.

Here is a quick script that will create a query that will bring all the data together to one sheet.

A few notes:

1. This assumes you want all your data into the sheet "All_Data"
2. If you do not want to include a sheet in the data, put a "_" in the name
3. The combined data will start in "A2" and the data in each sheet is in A3:L100
4. Insert this script into "Extensions - App Script". Just remember to delete the default script.

function CombineData() {

 const ss = SpreadsheetApp.getActiveSpreadsheet();

//specifies that the script is to run in this spreadsheet

 const AllDataSheet = ss.getSheetByName("All_Data");

//get the "All_Data" sheet

 var ListOfAllSheets = ss.getSheets();

//get all the sheets in the spreadsheet

 var QueryBuild = '=query({';

 for (i in ListOfAllSheets) {

  if (ListOfAllSheets[i].getName().indexOf("_") < 0) {

   QueryBuild = QueryBuild + '\'' + ListOfAllSheets[i].getName() + '\'' + '!A3:L100;';

  }

 }

//loop through all the sheets, if it doesn't have an "_" in its name, collect the data found from A3 to L100

 QueryBuild = QueryBuild.slice(0,-1);

//remove the extra ";"

 QueryBuild = QueryBuild + '},"select * where Col2 is not null order by Col2")';

//finish building the query with it removing any data that has column 2 empty and then put it in order by column 2

 AllDataSheet.getRange(2,1).setFormula(QueryBuild);

//write the formula to the "All_Data" sheet and start at row 2 and column 1 or "A2"

}