Is there anyway to loop through all the spreadsheets from a specific folder in Google Drive?

Question

Now I have a script that can loop through all the sheets in a spreadsheet. There are many spreadsheets in my folder. How can I loop through all the spreadsheets from a Google Drive folder that contain multiple spreadsheets

I've tried to loop through all the active sheets in one spreadsheet and it worked but I could not figure out how to loop through all the spreadsheets that contain one sheet from a folder

Thee difference between each folder is the month

 function checkSales(){

 var app = SpreadsheetApp;
 var activeSheet = app.getActiveSpreadsheet().getSheets();
 //var data=activeSheet.getDataRange().getValues();

 //loop through sheets to look for value
 for (var i in activeSheet) {

 SpreadsheetApp.setActiveSheet(activeSheet[i])
 var sheet = app.getActiveSheet();
 var data = activeSheet[i].getDataRange().getValues();

 var emailAddress=SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName("Battery").getRange("H26").getValue();
 var resultArr=[];
 var xTitle = 'Part Numbers'; // XAxis Title
 var yTitle = 'Quantity'; // YAxis Title
 var column = sheet.getRange("A1:A22");
 column.setNumberFormat("@");

 //To Loop through the whole data Rows
 for(var i=1;i<data.length;i++)
 {
    //Takes columns from L to S (To loop through the Columns)
    for(var j=11;j<19;j++)
     {
       var cellVal=data[i][j];
       Logger.log(cellVal)
       if(cellVal>0)
       {
          //Stores the Part No, Month Header Value of the Column, Cell 
          Value which is greater then 0
          resultArr.push([data[i][0],data[0][j],cellVal])
        }
      }
   }
    if(resultArr.length>0)
       {
          var subject = 'Range exceeded Alert' + "" + sheet.getName();

          //Creates a body through the obtained values
          var body='';
          for(var m=0;m<resultArr.length;m++)
           {
            body+="For Part No "+resultArr[m][0].toString()+" and Month 
           "+resultArr[m][1]
           .toString()+", Value is "+resultArr[m][2].toString()+"<br>";
           }

       }

      }

   }

Aung49 Aung49 · Accepted Answer · 2019-06-20T01:22:05

You can loop through files using this

function checkSales(){
  var file, files = DriveApp.getFolderById(*****id).getFiles(); 
  //put the id of your target folder in the getFolderById()
  while (files.hasNext()) {
    file = files.next();
   var activeSpreadSheet = SpreadsheetApp.open(file);
    var sheets = activeSpreadSheet.getSheets();

  //loop through sheets to look for value
  for (var sheetIndex = 0; sheetIndex < sheets.length; sheetIndex++) {


  var sheet = sheets[sheetIndex]
  var data = sheet.getDataRange().getValues();

  var emailAddress=activeSpreadSheet.getSheetByName("Battery").getRange("H26").getValue();

/* I edited until here, did not touch code below */

  var resultArr=[];
  var xTitle = 'Part Numbers'; // XAxis Title
  var yTitle = 'Quantity'; // YAxis Title
  var column = sheet.getRange("A1:A22");
  column.setNumberFormat("@");

  //To Loop through the whole data Rows
  for(var i=1;i<data.length;i++)
  {
   //Takes columns from L to S (To loop through the Columns)
     for(var j=11;j<19;j++)
     {
      var cellVal=data[i][j];
      Logger.log(cellVal)
      if(cellVal>0)
      {
        //Stores the Part No, Month Header Value of the Column, Cell 
        Value which is greater then 0
        resultArr.push([data[i][0],data[0][j],cellVal])
      }
     }
   }
   if(resultArr.length>0)
   {
      var subject = 'Range exceeded Alert' + "" + sheet.getName();

      //Creates a body through the obtained values
      var body='';
      for(var m=0;m<resultArr.length;m++)
       {
        body+="For Part No "+resultArr[m][0].toString()+" and Month 
       "+resultArr[m][1]
       .toString()+", Value is "+resultArr[m][2].toString()+"<br>";
       }

   }
  }

  }

 }

Is there anyway to loop through all the spreadsheets from a specific folder in Google Drive?

1 Answers