Google Apps Script to merge all data from multiple sheets into a single sheet for reporting on Google Drive Sheets.

MergeAllSheets

/* MergeAllSheets
 *
 * Takes a list of sheet names and add their data to a single sheet
 * Uses leftmost col. for names of the orginiating sheet of ea. row
 * If no args specified, takes all sheets except current - reports count
 *
 * Always assumes header row (doesn't take row 1 of anything)
 *
 * Usage: 
 *   =MergeAllSheets("D.C.","Illinois","Los Angeles","Massachusetts","Ohio","Seattle")
 *   =MergeAllSheets()
 *   
 * Matthew Finnell 2014
 *
 * h/t to VMerge() by ahab facit 2010
 *
*/
function MergeAllSheets() {
  var al=arguments.length;
  var sheets = [];
  var data = [];
  var nRow = [];
  var nGrid = [];
  var sheet, lastCol, lastRow, grid;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //populate our sheets array
  if (al < 1) {
    sheets = ss.getSheets();
    sheets.pop(ss.getActiveSheet().getIndex());
  } else {
    for( i=0 ; i<al ; i++){
      sheet = ss.getSheetByName(arguments[i]);
      if (sheet != null) {
        sheets.push(sheet); 
      }
      else {
        throw "The argument '"+arguments[i]+"' is not a valid sheet name"; 
      }
    }
  }
  
  //take sheet array get data ranges and put in data array, add place names
  for (i=0; i<sheets.length; i++) {
    lastRow = sheets[i].getLastRow();
    lastCol = sheets[i].getLastColumn();
    grid = sheets[i].getRange(2,1,lastRow-1,lastCol).getValues();
    for (j=0; j<grid.length; j++) {
      for (k=0; k<grid[j].length; k++){
        if(k==0) {
          nRow.push(sheets[i].getName());
        }
        nRow.push(grid[j][k]);
      }
      data.push(nRow);
      nRow = [];
    }
  }
  
  return data;
}

Hey, I know this is probably an old script now, I'm sort of learning as I go. Is it possible to adapt this to just extract say 5 tabs out of a multitab sheet and archive it off into one sheet to store on a google drive folder?

I've figured out how to export out each one individually I just now need to combine them on export into one

Hmm - honestly, it's been such a long time since I looked at or used this, I'm not even sure that it's compliant with the current way that Google Scripts works, unfortunately. As I recall, and my comment makes wrong, it does take tabs and not sheets per se. Not sure about the folder would need some modification.