Google Apps Script - adding in array elements

Question

I'm a begginer ok... I'm trying to use math operators in array elements, but I don't know what I'm doing wrong. I used the parseInt and Number function to increment array elements with some integers, but it's not working.

The original values from my sheet are: [[1000.0], [1000.0], [3000.0], [1000.0], [], [], [], [], [], []]

When I tryed without parseInt/Number the result was: [01000100030001000, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]

When I tryed with parseInt/Number: [NaN, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]

function myFunction() {
  var ssCDB = SpreadsheetApp.getActive().getSheetByName("CDB");
  var cdbRange = ssCDB.getRange(3, 3, 10, 1).getValues();

  var cdbSum = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0];

  Logger.log(cdbRange);

  for (var i=0; i<cdbRange.length; i++) {
     cdbSum[0] = cdbSum[0] + cdbRange[i];
    //cdbSum[0] = parseInt(cdbSum[0]) + parseInt(cdbRange[i]);
  }

  Logger.log(cdbSum);
}

I'm expecting something like: [6000.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]

what am I doing wrong? Any ideas?

What'll be the value of cdbRange[i] during first iteration? — TheMaster
– TheMaster, Commented Jan 21, 2019 at 21:59
Why do you need an array for cbdSum? Try var cbdSum = 0; then in your loop cdbSum = cdbSum + cdbRange[i][0]; since sdbRange is a 2D array [[].[],[]...]. i is each row and 0 is the first (and only) cell in the array. — TheWizEd
– TheWizEd, Commented Jan 21, 2019 at 22:34

Tanaike · Accepted Answer · 2019-01-21 22:38:06Z

1

How about this modification? I think that there are 3 issues for your situation.

cdbRange is 2 dimensional array. But you are using it like cdbRange[i].
- In this case, cdbRange[0] is [1000.0] which is an object.
By cdbSum[0] = cdbSum[0] + cdbRange[i], cdbSum[0] is used as a string value. Because cdbRange[i] is an object. When it adds an object to a number, it becomes the string type.
- This is the reason of 01000100030001000. The initial value of cdbSum[0] is 0.
Value in the element of index of 4 to 9 of cdbRange is undefined.
- When these are summed, the result becomes NaN.

In order to avoid these issues, please modify as follows.

From:

cdbSum[0] = cdbSum[0] + cdbRange[i];

To:

if (cdbRange[i][0]) cdbSum[0] += cdbRange[i][0];

or

if (cdbRange[i][0]) cdbSum[0] += Number(cdbRange[i][0]);

Result:

[6000.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]

Note:

I understood that you want to add the sum of all elements of cdbRange to cdbSum[0].

References:

If I misunderstood your question, please tell me. I would like to modify it.

answered Jan 21, 2019 at 22:38

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Elder Souza Over a year ago

hey @Tanaike, thanks! It helped me a lot. Especially regarding cdbRange ... is a multidimensional array and I did not even notice it. I only changed cdbRange[i][0].

Tanaike Over a year ago

@Elder Souza Thank you for replying. I'm glad your issue was resolved.

Cooper · Accepted Answer · 2019-01-21 23:26:58Z

0

I think this is what you're trying to do:

function myFunction() {
  var sh=SpreadsheetApp.getActiveSheet();
  var vA=sh.getRange(3,3,10,1).getValues();
  var sumA=[0,0,0,0,0,0,0,0,0,0];
  for(var i=0;i<vA.length;i++) {
     sumA[i]+=vA[i][0];//a 1 column 2d array
  }
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(sumA.join(', ')), 'Sum');//Do not need this it just show your sum values
}

answered Jan 21, 2019 at 23:26

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Collectives™ on Stack Overflow

Google Apps Script - adding in array elements

2 Answers 2

From:

To:

Result:

Note:

References:

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

From:

To:

Result:

Note:

References:

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related