Append multiple Google Sheets files into one Google Sheet using App Script having same sheet name but in different order of sheets

Question

I have 100 Google Sheet files in one folder of Google Drive. Each Google sheet file has 10 sheets (A,B,C,D,E,F,G,H,I,J). I wanted to append all 100 Google Sheet files into one Google Sheet appending data from "B" sheets of all the 100 files. But the sheets with name "B" will not be in 2nd place always. All the sheets has same columns. I have the below code which appends the sheets from multiple files only if those sheets are in 2nd place in the order in each sheet. 1) It has to pick the sheet name instead of the 2nd sheet 2) This code is including even the blank rows if any in the sheets. it has to skip the blank rows from the sheets.

function myFunction() {

  var myFolder = DriveApp.getFolderById("1ZtxfMNDn3uFhCcdcp5unfmTwUIJ_3fZK");
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  
  var master_files = DriveApp.getFilesByName("MergedNew")
      
  if (master_files.hasNext()){
     var master_file=master_files.next();
     var newSpreadSheet = SpreadsheetApp.openById(master_file.getId());
     }
  else { 
    var newSpreadSheet = SpreadsheetApp.create("MergedNew");
    newSpreadSheet.getSheets()[0].setName('B data');
  }
  
  var bSheet = newSpreadSheet.getSheetByName('B data');
  while(spreadSheets.hasNext()) 
  {
    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    var sh = spreadSheet.getSheets()[1];
    var data = sh.getRange(2,1,sh.getMaxRows(),sh.getMaxColumns()).getValues();
    
    if (bSheet.getLastRow() == 0){
      var headers = sh.getRange(1,1,1,sh.getMaxColumns()).getValues();
      bSheet.getRange(1,1,1,sh.getMaxColumns()).setValues(headers);
    }
    
    bSheet.getRange(bSheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);
  }      
}

Answer 1

Explanation:

But the sheets with name "B" will not be in 2nd place always

Then replace this:

var sh = spreadSheet.getSheets()[1];

with:

var sh = spreadSheet.getSheetByName('B');

or:

var sh = spreadSheet.getSheetByName('B data');

depending upon how you named the B sheets.

---

It has to skip the blank rows from the sheets

Then you can filter out the blank rows as follows:

var filtered_data = data.filter(function (row) {
    return row[0] != ""; //
  }); 

---

Solution:

function myFunction() {

  var myFolder = DriveApp.getFolderById("1ZtxfMNDn3uFhCcdcp5unfmTwUIJ_3fZK");
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  
  var master_files = DriveApp.getFilesByName("MergedNew")
      
  if (master_files.hasNext()){
     var master_file=master_files.next();
     var newSpreadSheet = SpreadsheetApp.openById(master_file.getId());
     }
  else { 
    var newSpreadSheet = SpreadsheetApp.create("MergedNew");
    newSpreadSheet.getSheets()[0].setName('B data');
  }
  
  var bSheet = newSpreadSheet.getSheetByName('B data');
  while(spreadSheets.hasNext()) 
  {
    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    var sh = spreadSheet.getSheetByName('B');
    var data = sh.getRange(2,1,sh.getMaxRows(),sh.getMaxColumns()).getValues();
    
    var filtered_data = data.filter(function (row) {
    return row[0] != ""; //
  }); 
    
    if (bSheet.getLastRow() == 0){
      var headers = sh.getRange(1,1,1,sh.getMaxColumns()).getValues();
      bSheet.getRange(1,1,1,sh.getMaxColumns()).setValues(headers);
    }
    
    bSheet.getRange(bSheet.getLastRow()+1,1,filtered_data.length,filtered_data[0].length).setValues(filtered_data);
  }      
}

Answer 2

Pick sheet B, not second sheet:

In order to retrieve a sheet with a certain name, and not with a certain index, you need to use getSheetByName(name) instead of getSheets(). Replace this:

var sh = spreadSheet.getSheets()[1];

With this:

var sh = spreadSheet.getSheetByName("{your_sheet_name}");

Remove blank rows:

You don't want to copy the blank rows to the new sheet, so you should filter them out from your source data. You can use filter and some (or every) for that. Add this line after retrieving data from the source sheet:

data = data.filter(row => row.some(value => value != ""));