I have 10 to 15 Files in the Drive Folder All files have more than 1 Google sheets in it with some sort of Conditional Formatting.

I want to copy the Specific Sheet Named "MasterSheet" from each File and Combine the data into the Active Sheet where from script is bieng run.

Copied data should keep conditional formatting cell color. I will appreciate the help. Thanks

function myFunction() 
{
  var myFolder = DriveApp.getFolderById("FolderID");
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  var ActiveSpreadSheet = SpreadsheetApp.getActive().getSheetByName(Sheet1);
  while(spreadSheets.hasNext()) 
  {
    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    for(var y in spreadSheet.getSheets()) 
    {
      spreadSheet.getSheets()[y].copyTo(ActiveSpreadSheet); 
     }
  }      
}

CodePudding user response：

An addendum to the answer by @Martin. This takes the data on the various "Master Sheets" and copies it to a single sheet.

The range of data on each sheet is established dynamically, and is stacked down the page.

function copyMasters(){

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var target = ss.getSheetByName('Sheet1')
  
  // idetify the starting row
  var activeStartRow = 1

  // get an array of all the sheets with names like "MasterSheet"
  var sheetList = new Array
  var sheets = ss.getSheets()
  //var sourcesheet = ss.getSheets().find(sheet => sheet.getName().includes("MasterSheet"))
  var mastersheets = sheets.filter(sheet => sheet.getName().includes("MasterSheet"))
  
  //loop through the Master sheets
  for (var i=0;i<mastersheets.length;i  ){

    var mastername = mastersheets[i].getName()
    var master = ss.getSheetByName(mastername)
    var masterLC = master.getLastColumn()
    var masterLR = master.getLastRow()

    // calculate the start row for the target sheet
    if (activeStartRow==1){
        var startRow =1        
    }else{
      var startRow=startRow masterLR
    }
    activeStartRow = 0

    var rangeToCopy = master.getRange(1, 1, masterLR,masterLC)
    //Logger.log("DEBUG: range to copy:" rangeToCopy.getA1Notation())
    var targetRange = target.getRange(startRow, 1, masterLR,masterLC)
    //Logger.log("DEBUG: target range:" targetRange.getA1Notation())

    rangeToCopy.copyTo(targetRange)
    // Logger.log("DEBUG: copied the data for " mastername)
  }
}

SAMPLE

CodePudding user response：

UPDATE to check if there's a sheet called that way:

function copywithLog() 
{
  var destinsheet = SpreadsheetApp.getActiveSpreadsheet()
  var myFolder = DriveApp.getFolderById("16CxTO70ipcUt8U_beokid3vFOOLGTnPh");
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  var activeSpreadSheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var i = 1
  while(spreadSheets.hasNext()) 
  {

    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    var sheets = spreadSheet.getSheets()
    var filtered = sheets.filter(n => ["DATA"].lastIndexOf(n.getSheetName()) == 0)
    if (filtered.length==1){
    var newsheet = spreadSheet.getSheetByName('DATA').copyTo(destinsheet)
    newsheet.getDataRange().copyTo(activeSpreadSheet.getRange(activeSpreadSheet.getLastRow() 2,1))
    destinsheet.deleteSheet(newsheet)
    Logger.log("Process successfully finished with the workbook named '" spreadSheet.getName() "'")}
    else Logger.log("The worbook named '" spreadSheet.getName()  "' does not have a sheet called DATA")
    

  }      
}

It returns something like this:

UPDATE:

function copyinsamesheet() 
{
  var destinsheet = SpreadsheetApp.getActiveSpreadsheet()
  var myFolder = DriveApp.getFolderById("FolderID");
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  var activeSpreadSheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var i = 1
  while(spreadSheets.hasNext()) 
  {
    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    var newsheet = spreadSheet.getSheetByName('MasterSheet').copyTo(destinsheet)
    newsheet.getDataRange().copyTo(activeSpreadSheet.getRange(activeSpreadSheet.getLastRow() 2,1))
    destinsheet.deleteSheet(newsheet)
    

  }      
}

If it has always the same name, you can try with this:

function copyMasterSheets() 
{
  var destinsheet = SpreadsheetApp.getActiveSpreadsheet()
  var myFolder = DriveApp.getFolderById("FolderID");
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  var ActiveSpreadSheet = SpreadsheetApp.getActive().getSheetByName(Sheet1);
  var i = 1
  while(spreadSheets.hasNext()) 
  {

    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    spreadSheet.getSheetByName('MasterSheet').copyTo(destinsheet).setName('MasterSheet ' i)
    i  

  }      
}