suppose you have a spreadsheet like this in Google Sheets
and you want to
SUM all the values of the cells in Q:Q WHERE the CORRESPONDING cell (i.e. on the same row) in column D:D has certain given values ..
Now, if we were in some DB language like MySql I suppose this would be quite easy and you would probably instruct something like
SELECT cells in "Tot. / Anno" (Q:Q) WHERE "level" (D:D) is B or MID
and that would be it, but I guess nothing so direct is possible in Javascript; so I am afraid a little workaround is needed.
So, first of all, what's the logic behind the sheet?
In column D:D under level you have four options which you can select from, and these are: b, mid, pro and top. B stands for basic, and the logic here is trying to build a gradual budget.
Some kind of expenses are basical and you can't avoid them, like medical or your bank account or telephone; while others like leisure and travels are secondary and you can spare on them; so they are not fundamental, but most likely mid pro or even top :)
This is the logic behind and the scale of values.
When you select a given criterium in D2 you should get the matching SUM in G2 based on the same gradual logic:
b in D2 --> only sum cells in Q:Q where value is b
mid in D2 --> only sum cells in Q:Q where value is b OR mid
pro in D2 --> only sum cells in Q:Q where value is b OR mid OR pro
top in D2 --> sum cells in Q:Q where value is b OR mid OR pro OR top
Now, as to coding, this is the workaround that I've found. It does work, but it's not elegant.
I would like to hear some more efficient and proper way to deal with the matter: I am at a elementary level and I am sure a more effecient solution is available
// onEdit is meant to make sure that a real-time recalculation is triggered
// at any change within the specified columns
function onEdit(e){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sheet.getRange("Q2");
var range = e.range;
var columnOfCellEdited = range.getColumn();
if (columnOfCellEdited === 4 || 17) {
// creating 2 different arrays from the two different comlumns (ranges)
var range1 = sheet.getRange("D3:D100").getValues();
var range2 = sheet.getRange("Q3:Q100").getValues();
// merging the two single arrays into one 2d array (you can't have one single
// 2d array from the start because the two columns are not adjacent I guess)
var range=[],i=-1;
while ( range1[++i] ) {
range.push( [ range1[i][0], range2[i][0] ] );
}
// I tried to come up with a global SUM but I don't know why it does not work
// var valore_glob = range2.reduce(function(acc, val) { return acc + val; }, 0);
// as much filters as given values in the validation drop-down list: each
// filter returns only the correspoding values
// FILTRO BASE - keeps only B values
var filt_bas = range.filter(function (dataRow) { return dataRow[0] === 'b';});
// splitting the 2d array into two singles arrays, we keep only the second
// one for the follwing SUM
var yArray_bas = filt_bas.map(function(tuple) { return tuple[1];});
// summing up all the values within the resulting array
var valore_bas = yArray_bas.reduce(function(acc, val) { return acc + val; }, 0);
// FILTRO MID - keeps only MID values
var filt_mid = range.filter(function (dataRow) { return dataRow[0] === 'mid';});
// splitting the 2d array into two singles arrays, we keep only the second
// one for the follwing SUM
var yArray_mid = filt_mid.map(function(tuple) { return tuple[1];});
// summing up all the values within the resulting array
var valore_mid = yArray_mid.reduce(function(acc, val) { return acc + val; }, 0);
// FILTRO PRO - keeps only PRO values
var filt_pro = range.filter(function (dataRow) { return dataRow[0] === 'pro';});
// splitting the 2d array into two singles arrays, we keep only the second
// one for the follwing SUM
var yArray_pro = filt_pro.map(function(tuple) { return tuple[1];});
// summing up all the values within the resulting array
var valore_pro = yArray_pro.reduce(function(acc, val) { return acc + val; }, 0);
// FILTRO TOP - keeps only TOP values
var filt_top = range.filter(function (dataRow) { return dataRow[0] === 'top';});
// splitting the 2d array into two singles arrays, we keep only the second
// one for the follwing SUM
var yArray_top = filt_top.map(function(tuple) { return tuple[1];});
// summing up all the values within the resulting array
var valore_top = yArray_top.reduce(function(acc, val) { return acc + val; }, 0);
var selector = sheet.getRange("D2").getValue();
switch (selector) {
default:
cell.setValue(valore_bas);
break;
case 'mid':
cell.setValue(valore_bas + valore_mid);
break;
case 'pro':
cell.setValue(valore_bas + valore_mid + valore_pro);
break;
case 'top':
cell.setValue(valore_bas + valore_mid + valore_pro + valore_top);
}
}
}
suggestions are welcome, thanks.
credits:
Writing google Javascript similar to vlookup
Sum elements of an array with specific attribute
Spreadsheet Non-Adjacent Column data
How to find the sum of an array of numbers
How to filter an array of arrays?
Split a 2D array into single arrays
latest working actual code as in code.gs: https://jsfiddle.net/John_Galassi/5ahrLmg9/
