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?

Improve this question
5
  • It's a 2 dimensional array. Not a 1D one. Commented Oct 9, 2020 at 8: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. Commented Oct 9, 2020 at 8: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? Commented Oct 9, 2020 at 8:26
  • 1
    @TheMaster I think it is indeed a duplicate of these two posts together :) so I agree with the closing. Commented Oct 9, 2020 at 8: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! Commented Oct 9, 2020 at 8:33

2 Answers 2

Reset to default
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);

}
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@EricJohnson Glad it worked! please mark the answer as accepted if it solved your issue.
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 you are absolutely right :) I updated my answer.
Very clear, thanks guys!
@EricJohnson Please accept the answer if it helped you solve the issue.
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);
Improve this answer

2 Comments

sorry, which array would use this map?
You would run it on numArr, chain it before the .reduce that you already have.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.