title: Google Spreadsheet script to Return table of subtotals
body: <p>Here is my function to import a range (6 columns ; and lots of rows) from active spreadsheet and return subtotal grouped by the 5th column.</p>
  
  <pre><code>var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getRange("A3:F");
  var values = rows.getValues(); 
  var expense = new Array();
  var expensehead = new Array();
  for (var i = 0, e = 0; i &lt;= rows.getNumRows()-1; i++) {
  
      if (values[i][0] instanceof Date &amp; values[i][2] == "Expense") {  // As long as col0 is a date and col2 == "Expense"
          if (expense.hasOwnProperty(values[i][4])) {
          // if the index "Expense Category" (col4) already exists in the array, add the amount (col 5) to the existing value
          // Add amount (col 5) array expense where index is "Expense Category" (col4)
          // For example Expense['Food'] = Expense['Food']  + 100;  Emulating it like an associative array here
          expense[values[i][4]]= expense[values[i][4]] + values[i][5]; 
        }
        else {
          // The index "Expense Category" (col4) does already exists in the array, assign the amount (col 5) to the new index
          // Add amount (col 5) array expense where index is "Expense Category" (col4)
          // For example Expense['Food'] = 100;  I have spet on food for the first time, hence it does not exist in the array already. Emulating it like an associative array here
          expense[values[i][4]]= values[i][5]; 
  
          //Since Javascript or Google script does not support iteration in an associative array, 
          //I am adding all indexes to another array expensehead so i will be able to pull out the info later.
          expensehead.push(values[i][4])
        } 
      }
    }
  </code></pre>
  
  <p>My Problem: </p>
  
  <p>I know that using array is a bad idea here. I want to use something like a 2D key value pair or an associative array (or some spreadsheet equivalent object)</p>
  
  <p>Can someone point me to an Google spreadsheet object that i can use and also state methods by which  can return the 2 columns "Expense category' and SUM(amount) subtotal.</p>
  
  <p>PS1: I dont want to use pivot table report as i will be introducing additional date based filters that pivot table does not support. (see below)
  PS2: I am currently using spreadsheet's built-in function "query" (see below) but i dont like it because it cannot give me Grand Total of sub totals like Pivots do.
  =query(A2:F;"SELECT SUM(F), D where C like 'Expense' AND A >= date '"&amp; mkdate(I1) &amp;"' AND A &lt;= date '"&amp; mkdate(L1) &amp;"' group by D Order by SUM(F) Label SUM(F) 'Amount', D 'Expense Category' ")
  PS3: I considered using a Sql query like above and running a pivot table on it to generate Subtotals, but i feel like a looser. Besides i have about 1000 rows in the data which makes it super slow. </p>
  
  <p>Any help would be greatly appreciated. </p>