Sum arrays if a condition is met within a loop

Question

I am using the code below to create a table. It works fine.

My final goal is to add a sub-total for "de-prioritized". Since I am already looping the table, I wanted to leverage the existing loop. (without looping every value in the array which I am afraid will slow down the code)

For Example If my table is:

[A,de-prioritized,2,1,0,0,1],
[B,de-prioritized,0,2,1,1,0],
[C,other,0,5,2,1,1]

I want to get: [2,3,1,1,1] (as sum of [2,1,0,0,1]+[0,2,1,1,0]).

In other words: Let's say I have a table A1:F10. In A there is a flag (either "de-prioritized" or not), while in B1:F10 there are values. I want to replicate the formula in each column: SUMIF(A1:A10,"de-prioritized",B1:B10),SUMIF(A1:A10,"de-prioritized",C1:C10),SUMIF(A1:A10,"de-prioritized",D1:D10) and so on. I cannot set the formulas because the range in the example above is dynamic. I tried to set the formulaR1C1 with sumif in the script, but it did not work.

I found a similar problem, but I may have n arrays to sum and I am not able to fit the solution in mine: Javascript - Sum two arrays in single iteration

Existing code I have:

  var l = sheet2.getRange('A1').getValue();
  for (var i = 0; i<l ;i++) {
    if (sheet3.getRange(i+3,2).getValue() == 'de-prioritized') { 
      sheet3.getRange(i+3,1,1,sheet3.getLastColumn()).setBackgroundRGB(250, 240, 230);
    } else sheet3.getRange(i+3,1,1,sheet3.getLastColumn()).setBackgroundRGB(225, 247, 232)
  }

I have to apologize for my poor English skill. I can understand about `I want to get: [2,3,1,1,1] (as sum of [2,1,0,0,1]+[0,2,1,1,0])`. But I cannot understand about your goal from your script. Can I ask you about the relationship between your goal and your script? — Tanaike, Apr 13 '20 at 22:01
my existing script is just working on the format. Let's say I have a table A1:F10. In A there is a flag (either "de-prioritized" or not), while in B1:F10 there are values. I want to replicate the formula in each column: SUMIF(A1:A10,"de-prioritized",B1:B10),SUMIF(A1:A10,"de-prioritized",C1:C10),SUMIF(A1:A10,"de-prioritized",D1:D10) and so on. — Filippo, Apr 13 '20 at 22:25
To clarify: I do not want to exactly replicate the sumif formula. I want the script to get the same result of those formulas in range B11:F11. — Filippo, Apr 13 '20 at 22:33
Thank you for replying. I thought that the value of `[A,de-prioritized,2,1,0,0,1]` might be different from your replying. From your question, I understood that the column "B" has the value of `de-prioritized`. In my sample script, this is used. And also, I cannot understand about the detail of your formulas. This is due to my poor skill. I have to apologize for this. In order to correctly understand about your question, can you provide the sample input and output values you expect Spreadsheet? By this, I would like to modify the script. If you can cooperate to resolve your issue, I'm glad. — Tanaike, Apr 13 '20 at 22:40
Thank you for your help. How can I provide you the sample inputs? I am going to try one more time with this example (where each comma separate the column). Inputs: Row 1: A,de-prioritized,2,1,0,0,1 Row 2: B,de-prioritized,0,2,1,1,0 Row 3: C,other,5,2,0,0,1 Row 4: D,de-prioritized,0,0,1,1,2 I want the output in Row 5: total,de-prioritized,2,3,2,2,3 Since I am already doing a loop for each row (1:4) as mentioned in my code above, I would like to add a code in that loop to get the total for "de-prioritized" — Filippo, Apr 14 '20 at 16:07
Thank you for replying. From your replying, I could understand that `Let's say I have a table A1:F10. In A there is a flag (either "de-prioritized" or not)` is not correct, and `A,de-prioritized,2,1,0,0,1` is the columns "A" to "G". About this, I think that my sample script returns the output you expect. If my sample script didn't resolve your issue, can I ask you about the difference between your goal and the result of my script? — Tanaike, Apr 14 '20 at 23:11

score 2 · Accepted Answer · edited Jun 11 '20 at 10:51

You want to retrieve [2,3,1,1,1] from [["A","de-prioritized",2,1,0,0,1],["B","de-prioritized",0,2,1,1,0],["C","other",0,5,2,1,1]] using Google Apps Script.

Sample script:

const arr = [["A","de-prioritized",2,1,0,0,1],["B","de-prioritized",0,2,1,1,0],["C","other",0,5,2,1,1]];
const res = arr.reduce((ar, [,b,...cdefg]) => {
  if (b == "de-prioritized") ar = ar.length > 0 ? ar.map((e, i) => e + cdefg[i]) : cdefg;
  return ar;
}, []);
console.log(res) // <--- [2,3,1,1,1]

Test of script:

const arr = [["A","de-prioritized",2,1,0,0,1],["B","de-prioritized",0,2,1,1,0],["C","other",0,5,2,1,1]];
const res = arr.reduce((ar, [,b,...cdefg]) => {
  if (b == "de-prioritized") ar = ar.length > 0 ? ar.map((e, i) => e + cdefg[i]) : cdefg;
  return ar;
}, []);
console.log(res)

Sum arrays if a condition is met within a loop

1 Answers1

Sample script:

Test of script:

References: