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.
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 <= rows.getNumRows()-1; i++) {
if (values[i][0] instanceof Date & 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])
}
}
}
My Problem:
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)
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.
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 '"& mkdate(I1) &"' AND A <= date '"& mkdate(L1) &"' 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.
Any help would be greatly appreciated.
