Summing same cell across hundreds of google Spreadsheets

Question

I have hundreds of reports in my google drive. Each report is its own file (Spreadsheet, with one sheet).

I need a total of cell B10 from all those spreadsheets. It would be great if there was a function that took two parameters:

1. the name of the directory containing the Spreadsheet files
2. the specific cell you want totaled.

I tried to do script

function Suma(cell)
{
  var files = DocsList.getFilesByType('spreadsheet', 0, 100);
  for (var i = 0; i < files.length; i++)
  {
   var sheets = SpreadsheetApp.open(files[i]).getSheets();
   var sum = 0;
   for (var i = 0; i < sheets.length ; i++ )
   {
     var sheet = sheets[i];
     var val = sheet.getRange(cell).getValue();

        if (typeof(val) == 'number') 
        {
            sum += val;   
        }
   }
  }
  return sum;
}

need some help of course :) THX

Answer 1

What you are doing is ok however u might want to list files on a folder and not the entire drive.. Also will fail if you have too many ss and script will run out of time. To solve that is possible but requires more complex code. Better do it on client side with jsapi or appengine.

Answer 2

This is going to be SLOW, and has a risk of timing out, due to the time it takes to open each spreadsheet.

This version of your function will take a folder name and a target cell, and produce a sum of the numeric values at that location from all spreadsheets in the folder.

The DriveApp relies on iterators rather than arrays for collections of folders and files, so you've got two examples of using .hasNext() and .next() for accessing objects in collections.

function Suma(folderName, cell)
{
  var folders = DriveApp.getFolders();
  var folderFound = false;
  var folder;
  while (folders.hasNext() && !folderFound) {
    folder = folders.next();
    folderFound = folder.getName() == folderName;
  }
  if (!folderFound) throw "No folder named " + folderName;  // Error

  var sheets = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  var sum = 0;
  while (sheets.hasNext()) {
    var sheet = SpreadsheetApp.openById(sheets.next().getId());
    var val = sheet.getActiveSheet().getRange(cell).getValue();
    if (typeof(val) == 'number') {
      sum += val;   
    }
  }
  return sum;
}