Is there a way to loop in all folders and subfolders, get a specific sheet from all workbooks and copy to my master sheet in GAS?

Question

So, I have a folder called "Evaluation", and that folder has 2 subfolders called "N1" and "N2". Each of these has 5 subfolders called "A", "B", "C", "D", and "E".

Now, in folders A,B,C,D,E, I have several google sheets: all of them with the same structure. In every workbook, there is a sheet called "Summary".

I want to copy from this Summary sheet the A1:C32 range to a master sheet, but I don't know how. I found a code here, but it works if I change the specific route of the folder (I linked to a cell); it doesnt work in subfolders so I have to change it every time I want to pull up information.

Is there a way that my code can be updated for loop from all folders and subfolders without me to interact with the code? This is what I have so far.

function getdata() {
  //múltiples variables
   var destinationSpreadsheet,destinationSS_ID,destsheet,destrange,file,files,
      sourcerange,sourceSS,sourcesheet,srcSheetName,sourcevalues;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var FolderDestino = ss.getSheetByName("SetUp").getRange('C1').getValue();
  srcSheetName = "Resumen";
  destinationSS_ID = "1v2Ga6SLgDBVzC4rxTt0dO6P3FGMPDM9MVf6-1r4Z4XQ";
  files = DriveApp.getFolderById(FolderDestino).getFiles(); //t01
  //files = DriveApp.getFolderById("11nqc2K_J4zKjBBIFYj2qvwrCIxf1o7aJ").getFiles(); //t02
  //files = DriveApp.getFolderById("1UaQkdZeYj_OQkiBPlgjZOnIGqWb7xk3r").getFiles(); //t03
  //files = DriveApp.getFolderById("1E6uW66LVr6nNM3fT77iuIquuEW5UyIxj").getFiles(); //t04
  //files = DriveApp.getFolderById("1nEaKpb7YkCWG6LhYdjn3lQDPyQM0ILtu").getFiles(); //t05
  
  destinationSpreadsheet = SpreadsheetApp.openById(destinationSS_ID);
  destsheet = destinationSpreadsheet.getSheetByName('Base');  

  while (files.hasNext()) {
    file = files.next();
    if (file.getMimeType() !== "application/vnd.google-apps.spreadsheet") {
      continue;
    };
    sourceSS = SpreadsheetApp.openById(file.getId());
    sourcesheet = sourceSS.getSheetByName(srcSheetName);
    //sourcesheet.getRange(start row, start column, numRows, number of Columns)
    sourcerange = sourcesheet.getRange(8,1,sourcesheet.getLastRow()-1,3);
    sourcevalues = sourcerange.getValues();

    //Write all the new data to the end of this data
    destrange = destinationSpreadsheet.getSheetByName("Base")
        .getRange(destsheet.getLastRow()+1,1,sourcevalues.length,sourcevalues[0].length);

    destrange.setValues(sourcevalues);         
  };
};

Any help would be appreciated.

Answer 1

This is a basic example of recursion doing something I hope is similar to what you require.

One of the things that's required is a way for the function that's traversing the tree to access an external static memory so that we can save the data when we have to exit or recurse to another level and I typically use either cacheService or PropertiesService. In the early days of microprocessors I use to develop my own cache memory around the cpu to accommodate this need and if you have this kind of memory access, recursive loops can run much faster than most any other kind of structured programming. Although, they can be rather difficult for those that are not familiar with them to debug. Which I have to admit I consider a huge plus.

This first function gets the ball rolling by find the starting folder and calling the function that will then traverse the tree.

function getFileIds() {
  const evalFolder = DriveApp.getFolderById(gobj.globals.testfolderid);
  let fid = [];
  PropertiesService.getScriptProperties().setProperty("fid",JSON.stringify(fid));
  getff(evalFolder);
  fid = JSON.parse(PropertiesService.getScriptProperties().getProperty("fid"));
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.clearContents();
  let o = fid.map(id => [id]);
  o.unshift(['IDs']);
  sh.getRange(1,1,o.length,o[0].length).setValues(o);//now you have a list of all of these spreadsheet id's in your folders and you can now easily access them sequentially for building your report.
}

function getff(folder) {
  let fid = JSON.parse(PropertiesService.getScriptProperties().getProperty("fid"));
  let files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while(files.hasNext()) {
    let file = files.next();
    let ss = SpreadsheetApp.openById(file.getId());
    if(ss.getSheets().filter(sh => sh.getName() == "Summary").length == 1) {
      fid.push(file.getId());
      PropertiesService.getScriptProperties().setProperty("fid",JSON.stringify(fid));
    }
  }
  let subfolders = folder.getFolders();
  while(subfolders.hasNext()) {
    let subfolder = subfolders.next();
    getff(subfolder);
  }
}

Copy Paste all the data into master

function copyPaste() {
  const ss = SpreadsheetApp.getActive();
  const msh = ss.getSheetByName("Master");
  const idsh = ss.getSheetByName("Sheet0");
  const ids = idsh.getRange(2,1,idsh.getLastRow() - 1).getValues().flat();
  ids.forEach(id => {
    let ss = SpreadsheetApp.openById(id);
    let sh = ss.getSheetByName("Summary");
    let vs = sh.getRange(A1:C32).getValues()
    msh.getRange(msh.getLastRow() + 1, 1,vs.length,vs[0].length).setValues(vs);
  })
}