Google-sheets – Sum the totals for each product over all sheets in a spreadsheet

google sheetsgoogle-apps-script

I'm trying to make a spreadsheet to take online orders from clients.
I send them a price list, which they complete with their order and every time a client sends an order, it automatically adds a new sheet to the "master" spreadsheet. The added sheet is named with the mail from the client and the time they make the order.

The thing is, I want to sum the totals ordered for each product on the list, but sheets are added all the time, and with non predictable names.
I have managed to make a list of the sheets using a script (actually I'm having problems making it update automatically), and then using indirect and sumif. But i have to use a sumif for every sheet in the list, and then again the number of sheets may variate. For example I use 

=sumif(indirect(E5&"!B$5:B"),B$2,indirect(E5&"!F$5:F"))

where in E5 is the name of the first sheet of the list.
Is there a way to use an array formula to aply the sumif formula, to all the sheets in the list? (The list will be in the E column)?

Here is the spreadsheet

Best Answer

When you have many sumif formulas in your spreadsheet, this is usually a sign you need to use query instead. The script you are currently using to fetch the sheet names can be modified so that it creates a query formula instead: 

function queryFormula() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formula = "=query({";
  var sheets = ss.getSheets();
  for (var i=1; i<sheets.length; i++) {
    formula = formula + "'" + sheets[i].getName() + "'!A5:E;";
  }  
  formula = formula.replace(/;$/, '}, "select Col1, Col2, sum(Col5) group by Col1, Col2")');
  ss.getSheetByName("Totales").getRange("H2").setFormula(formula);
}

The result of running this script is that cell H2 gets the formula 

=query({'02/26/2016 20:33:08 gcrosta@gmail.com'!A5:E;'02/26/2016 19:23:17 leyla.s.rodriguez@gmail.com'!A5:E;'02/26/2016 17:26:37 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:26 gcrosta@gmail.com'!A5:E;'02/26/2016 17:26:17 iris2908@fibertel.com.ar'!A5:E;'02/26/2016 17:05:49 iris2908@fibertel.com.ar'!A5:E}, "select Col1, Col2, sum(Col5) group by Col1, Col2")

which does everything else. The first parameter of the query is the array, which consists of the ranges A5:E put together, on top of one another.

The second parameter is a query string, which says to sum column 5 (which in the range A:E is E), grouping by columns 1, 2 (which are A,B).

The output looks like this:

+-------------------------------------------------+------+------+
| ACEITE DE oLIVA Escencias de la Tierra x 500 ml | 2215 |  582 |
| Aceite de Coco God Bless 325 g                  | 2488 |  900 |
| Aceite de Coco Refinado Napus 660 ml            | 2626 |  840 |
| Aceite de Coco Virgen God Bless 225 g           | 2479 |  900 |
| Aceite de Coco Virgen God Bless 500 g           | 2387 | 1500 |
+-------------------------------------------------+------+------+

Updating the formula

If you rename the script function "queryFormula" into onOpen, it will run every time you open the spreadsheet, thus ensuring you get the current set of sheets in the formula.

Related Topic