CODEWITHSUNDEEP
arraysmathgoogle-apps-scriptgoogle-sheets
Elder Souza
Elder Souza

Reputation: 3

Google Apps Script - adding in array elements

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?

Upvotes: 0

Views: 3264

Answers (2)

Cooper
Cooper

Reputation: 64042

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
}

Upvotes: 0

Tanaike
Tanaike

Reputation: 201348

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

  1. 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.
  2. 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.
  3. 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.

Upvotes: 1

Related Questions