Copying all the sheets in every workbook within a google drive folder

Question

I Want a google script that will loop through every workbook(spreadsheet) within my google drive folder, then copying every sheet within to one sheet of my own. I already have a script but its set to copy only the first sheet, could we make it loop to copy all sheets in the spreadsheet

I've tried to change the indexing but it'll only pull one sheet

function getDataToMaster() {
  var folder = DriveApp.getFolderById("******************"); //Define id of folder
  var contents = folder.getFiles();
  var file; 
  var data;
  var sheetMaster = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //first sheet of the file, change by getSheetByName("NAME") if you want
  while(contents.hasNext()){  
    file = contents.next();
    if (file.getMimeType() == "application/vnd.google-apps.spreadsheet") {
      var sheet = SpreadsheetApp.openById(file.getId()).getSheets()[0];//first sheet of the file, change by getSheetByName("NAME") if you want
      var startRow = 1; 
      var data = sheet.getDataRange().getValues();
      var colToCheck = 7;
      for(var j = 0; j < data.length;j++){
        if(data[j][colToCheck-1] != "copied"){
          sheetMaster.appendRow(data[j]);
          sheet.getRange((j+1), colToCheck).setValue("copied");
          SpreadsheetApp.flush();
        }
      }
    } 
  }
}

Answer 1

Spreadsheet.getSheets() returns an array of Sheets you can iterate over.

Suggestion: when you know you will iterate through each element in an array (i.e. you don't want to exit early through break or return to terminate a loop), then try Array.prototype.forEach, as it reduces the amount of variables you need to setup. The following is how I would rewrite your getDataToMaster function using forEach twice:

function getDataToMaster() { 
  var folder = DriveApp.getFolderById("******************");
  var contents = folder.getFiles();
  var sheetMaster = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  while (contents.hasNext()) {    
    var file = contents.next();
    if (file.getMimeType() !== "application/vnd.google-apps.spreadsheet") { 
     continue;
    } 
    SpreadsheetApp.openById(file.getId()).getSheets()
      .forEach(function (sheet) { 
        var colToCheck = 7;
        sheet.getDataRange().getValues()
          .forEach(function (row, index) { 
            if (row[colToCheck - 1] != "copied") { 
              sheetMaster.appendRow(row);
              sheet.getRange(index + 1, colToCheck).setValue("copied");
              SpreadsheetApp.flush();
            } 
          });
      });
  } 
}

Answer 2

It should work if you add a for loop underneath of your if statement like so

    if (file.getMimeType() == "application/vnd.google-apps.spreadsheet") {
      var sheetCount = SpreadsheetApp.openById(file.getId()).getNumSheets();
      for(var k = 0; k < sheetCount; k++){
        var sheet = SpreadsheetApp.openById(file.getId()).getSheets()[k];//iterates through each sheet in the file
        var startRow = 1; 
        var data = sheet.getDataRange().getValues();
        var colToCheck = 7;
        for(var j = 0; j < data.length;j++){
          if(data[j][colToCheck-1] != "copied"){
            sheetMaster.appendRow(data[j]);
            sheet.getRange((j+1), colToCheck).setValue("copied");
            SpreadsheetApp.flush();
          }
        }
      }
    }

How getSheets()[Integer] works is that it gets the sheet with the matching index number as supplied. The first sheet in the spreadsheet is index 0, the second is 1, third is 2, and so on. This loop just gets how many sheets there are and iterates through the indexes performing the actions you wrote for each individual sheet.