0

I have a for loop. The purpose of the loop is to index a one dimensional array of 5 numbers within a two dimensional array, then add the numbers in that array together and place it in on another sheet (testSheet). It's returning text like "0,45,,,40". The program works fine, other than it's not adding the numbers together. I'm guessing this is because some of the cells are null and it's not recognizing them as numbers.

for (var i=0; i < arrTarget.length; i++){
  //find Row
  var row = arrSource.indexOf(arrTarget[i]);


  var numArr = shrinkLog.getRange(row+3,4,5).getValues();

  //add units in an array
  var sum = numArr.reduce(function(a,b){return a+b;},0);

  //execute
  var testsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test Sheet");
  testsheet.getRange(i+1,1,1,1).setValue(sum);

}

Can someone help how I get the program to recognize the null cells as the number 0?

Marios
  • 26,333
  • 8
  • 32
  • 52
Eric Johnson
  • 89
  • 5
  • It's a 2 dimensional array. Not a 1D one. – TheMaster Oct 09 '20 at 08:03
  • @TheMaster half of the question is duplicate. The other half is how to filter out the empty cells from the array. Of course there are many other posts (mainly javascript) to show how to calculate a sum from an array with null/empty values etc. But as a combination, I don't think the question is duplicate. – Marios Oct 09 '20 at 08:10
  • @Marios I added a another duplicate to the list. The combination of answers should be a duplicate. Do you still feel this should be reopened? – TheMaster Oct 09 '20 at 08:26
  • 1
    @TheMaster I think it is indeed a duplicate of these two posts together :) so I agree with the closing. – Marios Oct 09 '20 at 08:28
  • 1
    Great. Your reopen vote should put it under reopen queue. If others feel the duplicates don't satisfy the question enough, it'll be reopened. Currently, I'm not reopening as You're ok with the duplicates list. Cheers! – TheMaster Oct 09 '20 at 08:33

2 Answers2

2

Explanation:

The main issue is that numArr is a two dimensional array in the form of [[],[],[]]. To see why is that, please go through this very useful post.

There are two steps you need to follow to accomplish your goal:

  1. Flatten the array in order for the reduce method to work properly:

    var numArr = shrinkLog.getRange(row+3,4,5).flat()

  2. Filter out empty values (blank cells) to get the correct sum:

    var numArr = shrinkLog.getRange(row+3,4,5).flat().filter(row=>row!="")

Solution:

Replace that:

var numArr = shrinkLog.getRange(row+3,4,5).getValues();

with

var numArr = shrinkLog.getRange(row+3,4,5).flat().filter(row=>row!="");

for (var i=0; i < arrTarget.length; i++){
  //find Row
  var row = arrSource.indexOf(arrTarget[i]);


  var numArr = shrinkLog.getRange(row+3,4,5).flat().filter(row=>row!="");

  //add units in an array
  var sum = numArr.reduce(function(a,b){return a+b;},0);

  //execute
  var testsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test Sheet");
  testsheet.getRange(i+1,1,1,1).setValue(sum);

}
Marios
  • 26,333
  • 8
  • 32
  • 52
  • @EricJohnson Glad it worked! please mark the answer as accepted if it solved your issue. – Marios Oct 09 '20 at 07:46
  • IMO, The part titled "Explanation" is the "Modification/Solution". The part titled "Solution" is the "Sample script/snippet". My peeve is simply that there's no "Explanation". IMHO, **Why? Why? Why?** is more important than any code that works for OP. Any answer that provides the "Why" part is much more valuable than any so called working code. – TheMaster Oct 09 '20 at 08:09
  • 1
    @TheMaster you are absolutely right :) I updated my answer. – Marios Oct 09 '20 at 08:18
  • Very clear, thanks guys! – Eric Johnson Oct 12 '20 at 14:23
  • @EricJohnson Please accept the answer if it helped you solve the issue. – Marios Oct 12 '20 at 14:30
1

You could run a .map on your array before and replace any null values with 0.

var sum = numArr
              .map(e => e === null ? 0 : e)
              .reduce(function(a,b){return a+b;},0);
FRMR
  • 289
  • 3
  • 27