Function to combined data from multiple tabs of Google Sheets

Question

I am writing a function to combine the data from multiple tabs (2 columns in each tab) in Google Sheets. I want to pull the header row from the 1st tab only, and skip for all the rest. I have written a function that is trying to evaluate if the tab is the 1st tab using if-then-else statement and then running a for-loop to write all the values to an array. This scripts fails to run. If I remove the if-then-else condition and run it, it works but then return the header value for each tab creating a false data. Code is as below:

function combinesheets() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=1 ; i<sheets.length ; i++) {
    var data = sheets[i].getDataRange().getValues();
    sname = sheets[i].getName();
    if (i=1){
      for (var j = 0; j < data.length; j++) {
        out.push( [ sname , data[j][0], data[j][1]])
      }
    }
    else{
      for (var j = 1; j < data.length; j++) {
        out.push( [ sname , data[j][0], data[j][1]])
      }
    }
  } 
  return out;
}

tab1:

Name	ID
ABC	123
DEF	456

tab2:

Name	Id
XYZ	789
PQR	654

Expected output:

Name	Id
ABC	123
DEF	456
XYZ	789
PQR	654

Answer 1

What you want to do is essentially just appending all tables from all sheets and write the result in the last sheet in the last sheet.

It will do no validation or anything just put all data in a sheet to the last sheet so that nothing is overridden but just appended. Crucial for this is the variable nextHeader which keeps track of the next row that can be written.

This will work for any number of sheets. The last sheet will always contain the result.

function appendAllSheets(){
  // get spreadsheet attached to this script
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // get all sheets in this spreadsheet
  const sheets = spreadsheet.getSheets();
  // returns last sheet and deletes it from the array
  const target = sheets.pop();
  
  // marks the row where the next header is supposed to be
  let nextHeader = 1;
  sheets.forEach(sheet => {
    // get range for table to read from
    const tableRng = sheet.getDataRange()
    // get range for target table
    // start at the row with index nextHeader
    const targetHeaderRng = target.getRange(nextHeader, 1, tableRng.getNumRows(), tableRng.getNumColumns());
    // now actually write the values
    targetHeaderRng.setValues(tableRng.getValues());
    // now we need to update table rows to make sure the next table is appended and does not override what we've done until now
    nextHeader += tableRng.getNumRows();
  })
}

Input:

Sheet 1

Sheet 2

All sheets

Expected output: