How can I merge multiple tabs in a Google Spreadsheet using Google App Script?

Question

How can I merge multiple tabs in a Google Spreadsheet using Google App Script ? Data in all the tabs keep changing.

For example, I have 'Sheet1', 'Sheet2' and 'Sheet3' in a Google spreadsheet. Data in all these sheets have 3 columns - Name and email ID & Region. Now, I want to merge/combine the data present in these 3 sheets/tabs into a 4th Sheet i.e. 'Sheet 4' having same Columns (name, email Id & Region). The 4th sheet should have data as - data of Sheet1 followed by Sheet2 and then Sheet3. The number of rows in all the 3 sheets keeps changing.

Answer 1

=query({Sheet1!A1:C; Sheet2!A1:C; Sheet3!A1:C}, "where Col1 is not null", 0)

I wouldn't use a script for this; the worksheet formulas are much faster, at least most of the time. Make sure you use semicolons to separate the ranges. Semicolons are the End_Of_Row operator for array literals.

If you really want to use a script...

function combineSheets() {
  var sApp = SpreadsheetApp.getActiveSpreadsheet();
  var s1= sApp.getSheetByName("Sheet1");
  var s2= sApp.getSheetByName("Sheet2");
  var s3= sApp.getSheetByName("Sheet3");
  var s4= sApp.getSheetByName("Sheet4");
  //  If Sheet4 doesn't exist you'll need to create it here.
  
  var s1values = s1.getRange(1,1,s1.getLastRow(),3).getValues();
  var s2values = s2.getRange(1,1,s2.getLastRow(),3).getValues();
  var s3values = s3.getRange(1,1,s3.getLastRow(),3).getValues();
  
  //  Now, we can put out all together and stuff it in Sheet4
  var s4values = [];
  s4values =  s1values.concat(s2values,s3values);
  s4.getRange(1,1,s4values.length,3).setValues(s4values);
}

Answer 2

You don't need a script for this. In the fourth sheet, enter the following formula in A2:

={filter(Sheet1!A2:C, len(Sheet1!A2:A)); filter(Sheet2!A2:C, len(Sheet2!A2:A)); filter(Sheet3!A2:C, len(Sheet3!A2:A))}

It returns the contents of columns A-C where the entry in column A is nonempty, and stacks them in one array.

Answer 3

You can use Google Apps Scripts for this.

var ss = SpreadsheetApp.getActiveSpreadsheet();
function TotalsSheet() {
  var totaldata = [];
  var sheets = ss.getSheets();
  var totalSheets = 2;

  for (var i=0; i < totalSheets; i++) {
    var sheet = sheets[i];
    var range = sheet.getDataRange();
    var values = range.getValues();

    for (var row in values) {
      totaldata.push(values[row]);
    }
  }
  return totaldata; 
}

function Start() {
  var All = ss.insertSheet("All-Values");
  var totaldata = TotalsSheet();
  for (var i = 0; i < totaldata.length; i++) {
    All.appendRow(totaldata[i]);
  } 
}

Answer 4

//This Script is must faster

function consolidateData(){

  // defined all variables
  var sheetNames = [];
  var dataSheet = [];
  var dataValues = [];
  var conso=[];
  var header = [["Name","email ID","Region"]];
  var ws = SpreadsheetApp.getActiveSpreadsheet();

  // get all sheets
  var allsheets = ws.getSheets();

  for(var s in allsheets){
    var sheet = allsheets[s];    
    sheetNames[s] = sheet.getName();
    dataSheet[s] = ws.getSheetByName(sheetNames[s]);

    // adding all sheet's data to a single variable
    conso = conso.concat(dataSheet[s].getRange("A2:C"+dataSheet[s].getLastRow()).getValues());  
  }

  // writing data into new sheet
  var newSheet = ws.insertSheet().setName("Consolidated_Data");
      newSheet.getRange("A1:C1").setValues(header);
      newSheet.getRange("A2:C"+(conso.length+1)).setValues(conso);  




}

Answer 5

I made one that will do nine tabs' consolidation, with more rows and columns.

=filter({{Sheet1!A1:Q500};{Sheet2!A1:Q500};{Sheet3!A1:Q500};{Sheet4!A1:Q500};{Sheet5!A1:Q500};{Sheet6!A1:Q500};{Sheet7!A1:Q500};{Sheet8!A1:Q500};{Sheet9!A1:Q500}},{{Sheet1!A1:A500};{Sheet2!A1:A500};{Sheet3!A1:A500};{Sheet4!A1:A500};{Sheet5!A1:A500};{Sheet6!A1:A500};{Sheet7!A1:A500};{Sheet8!A1:A500};{Sheet9!A1:A500}}<>"")

I'm wondering if it is a way to generalize the initial filter formula so that you can cut and paste in more sheet numbers, so for instance if there were 20 or 30 tabs with 25 columns, could you consolidate that easily into one page?