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.