1

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.

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.
1
  • Thank you so much! I did want the continue part in there because those were the named sheets I didn't want, but adding that back your modification works perfectly!
    – arigreat
    Jul 9 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.