Looping through multiple tabs in multiple google sheets

Question

I am working to create a "master" google sheet based on a folder of google sheet documents (currently 6 files). Each of these sheets have a slightly different number of tabs (some with slight name variations), but I want to loop a formula to get the values off of each tab within each sheet file. (ex. fileA has 3 tabs, I want to grab the data off of each tab and repeat similarly in fileB that has 4 tabs). I also want to skip a few named tabs.

Here is a piece of my code (2 interlaced functions):

function getDataFromSpreadsheet(ssID){

  var ss = SpreadsheetApp.openById(ssID)
  var allsheets = ss.getSheets();

    for(var s in allsheets){
    var sheet = allsheets[s];
    var data = []
      if(
       (sheet.getName() == "Summary") || 
       (sheet.getName() == "Data") || 
       (sheet.getName() == "Sheet1") ||
       (sheet.getName()== "Sheet2")
      ) continue;
      var lastrownum = sheet.getLastRow();
      var values = sheet.getRange("A2:L" + lastrownum).getValues();
      data = data.concat(values)
      Logger.log(data);
      return data;
      }
}

However, this is only grabbing data off of the first tab in each file that is not named "Summary", "Data", etc.

How do I get it to loop through all the (non-excluded) tabs in all sheet files??? I am unable to edit the files as this is to create an automated system.

Answer 1

Try this mod to your code:

function getDataFromSpreadsheet(ssID){

  var ss = SpreadsheetApp.openById(ssID)
  var allsheets = ss.getSheets();

  var data = []

  for(var s in allsheets){
    var sheet = allsheets[s];
    if(
       (sheet.getName() == "Summary") || 
       (sheet.getName() == "Data") || 
       (sheet.getName() == "Sheet1") ||
       (sheet.getName() == "Sheet2")
    ){

      var lastrownum = sheet.getLastRow();
      var values = sheet.getRange("A2:L" + lastrownum).getValues();
      data = data.concat(values)      
    }
  }
  return data;
}

• continue is having the effect of skipping any of the named sheets. It would be better to just delete it and include the required code in curly brackets.
• you can only return data once: I moved this to the end of the function, but an alternative would be to write data (setValues) to a sheet.
• similarly, var data = []: you are clearing the array for every sheet (which only makes sense if you were to setValues(data) within the loop.