How to combine multiple spreadsheet into one sheet using ID with Google App Script-CodePudding

I just want to ask about how to combine data from multiple spreadsheets into one sheet by using their ID. So, I made 2 sheets in the Master spreadsheet (1 for Data Master and 1 for ID list). I want to take the ID from ID List Sheet to get the file and a sheet that I would like to combine. I have using the DriveApp.getFileById but it's still not working. My current Script is using the file name list to access the data on sheet2 only and still not working. Do you have any solution to change or repair my script? It'll be very helpful. Thank you!

Note: The Source spreadsheets data can always be updated

This is my current Script (Inspired by https://codewithcurt.com/combine-multiple-spreadsheets-into-one-google-sheet/):

function myFunction() {
  var sheetIDurl = 'xxxxxxxx';
  
  var source = SpreadsheetApp.openByUrl(sheetIDurl).getSheetByName('ID').getDataRange().getValues();
  
  var folder = DriveApp.getFolderById('xxxxxxxxxx');
  
  var ssa = SpreadsheetApp.getActiveSpreadsheet();
  
  var copySheet = ssa.getSheetByName('Master');
  copySheet.getRange('A2:Z').clear();
  var search = [];
  for (var i = 1; i < source.length; i  ) {
    
    search = source[i][0].toString();

    var file = folder.getFilesByName(search);

    while (file.hasNext()) {
      var getFile = file.next();
      break;
    }
    Logger.log(getFile);
    var ss = SpreadsheetApp.open(getFile);
    SpreadsheetApp.setActiveSpreadsheet(ss);
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');

    for (var i = 0; i < sheets.length; i  ) {
      var nameSheet = ss.getSheetByName(sheets[i].getName());
      var nameRange = nameSheet.getDataRange();
      var nameValues = nameRange.getValues();

      for (var y = 1; y < nameValues.length; y  ) {
        copySheet.appendRow(nameValues[y]);
      }
    }
  }
}

CodePudding user response：

Try Below Sample Script after changing sheet names :-

function data_Merger()
{
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mergeSheet = ss.getSheetByName('Master Sheet Name, where data should be pasted'); //Change sheet name
  const id_Sheet = ss.getSheetByName('ID SheetName'); //Change sheet name
  const IDs = id_Sheet.getRange('A2:A').getValues().flat() // Change to your column range which contains IDs
  var mergedData = []
  for(var i = 0 ; i < IDs.length ; i  )
  { 
       var ns = SpreadsheetApp.openById(IDs[i]).getSheetByName('Sheet2')
       var MRange = ns.getRange(2, 1, ns.getLastRow()-1, ns.getLastColumn()).getValues()   // Excluding Header Row
       for(var j = 0 ; j < MRange.length ; j  )
       {
         mergedData.push(MRange[j])
       }     
  }
  
  mergeSheet.getRange(2, 1, mergedData.length, mergedData[0].length).setValues(mergedData) // Pasting data in Master Sheet  
}

Structure of all the sheets should be exactly same.