I am trying a script to scrape data form a given Sheetname and a given range into 1 main sheet. the problem in this case is that the Spreadsheets (with multiple sheets) are archived in different folders. so I have 1 main folder and in that folder I have archive folders for every year (2012-2022).

I have made script that is going through these folders and get the folder Id's that part is working.

I also have made a script to get the data from the sheet ("Rekenblad") and the range "A11:AY136". I think that also should work.

The part where I connect those 2 function is going wrong. What I would recieve is:

A main file (The script is inside the main file) with:

1. A sheet (blad1) with a list of all the information of the file
2. A sheet (blad2) with all data ranges combined. Every time I run this script the data needs to be cleared so I dont have doubles.

The most important is point 2. point 1 is just for me to check if all files are imported correctly and a link so I can easily check those files.

Script looks like this:

    function listFolders(folder) {

   var sheet = SpreadsheetApp.getActiveSheet().clearContents();
   sheet.appendRow(["Name", "URL","ssId", "Type"]);
   var folder = DriveApp.getFolderById("FOLDER_ID_PARENT_FOLDER");

  var subfolders = folder.getFolders();
  
  while (subfolders.hasNext()) {
       //Logger.log(folder);
    var name = subfolders.next();
    while (subfolders.hasNext()) {
       //Logger.log(folder);
    var name = subfolders.next();

    var files = name.getFiles();

    var cnt = 0;
    var file;
    var ssID
    var fileType
    var combinedData = []

    while (files.hasNext()) {
        var file = files.next();
        cnt++;

        Logger.log(file);
        Logger.log(cnt);

            data = [
                file.getName(),
                file.getUrl(),
                ssID = file.getId(),
                fileType = file.getMimeType(),
            ];

            sheet.appendRow(data);  
            if (fileType ==="application/vnd.google-apps.spreadsheet"){
              ssID = files.getId();
              data = GetData(ssID)
              data = data.map(function(r){return r.concat([file.getName()]);});
              combinedData = combinedData.concat(data);
            }
    }
    var wss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Blad1") ;
    ws.getRange("A2:AY").clearContent();

    wss.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);     
    }
  }
}   


function GetData(ssID){

  var aa = SpreadsheetApp.openById(ssID);
  var wa = aa.getSheetByName("Rekenblad");
  var info = wa.getRange("A11:AY136").getValues();
  return info
}