Array reduce concat instead of sum values

Question

I'm trying the first time to use google sheets and the script to sum a column of values and place the sum in the first free cell with a date.

Unfortunately I get a string and not the Sum I'm expecting.

Do you have any clue?

Thanks

KR

function sommami() {
  var foglioAssets = SpreadsheetApp.getActive().getSheetByName('Assets');
  Logger.log(foglioAssets)
  var colonnaP = foglioAssets.getRange('P2:P100');
  Logger.log(colonnaP);
  var array = colonnaP.getValues()
  Logger.log(array)
    var somma = array.reduce(function(a,b) {
      return a+b});
  Logger.log(somma);
  var foglioHistoricalNW = SpreadsheetApp.getActive().getSheetByName('HistoricalNW');
  Logger.log(foglioHistoricalNW)
  var primaRigaLibera = foglioHistoricalNW.getRange('A:A').getValues().length + 1;
  Logger.log(primaRigaLibera)
  var data = Utilities.formatDate(new Date(), "GMT", "dd/MM/yyyy");
  Logger.log(data)
  foglioHistoricalNW.getRange('A' + primaRigaLibera).setValue(data);
  foglioHistoricalNW.getRange('B' + primaRigaLibera).setValue(somma);
}

17:15:04 Notifica Esecuzione avviata 17:15:04 Informazioni Sheet 17:15:04 Informazioni Range 17:15:04 Informazioni [[596.38], [17515.15], [29.79], [369.02], [], [6829.2], [12082.0], [14991.0], [], [300000.0], [32000.0], [18500.0], [], [19971.99], [35820.104140799995], [], [-162547.23], [-48500.0], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []] 17:15:04 Informazioni 596.3817515.1529.79369.026829.21208214991300000320001850019971.9935820.104140799995-162547.23-48500 17:15:04 Informazioni Sheet 17:15:04 Informazioni 6.0 17:15:04 Informazioni 19/02/2024 17:15:05 Notifica Esecuzione completata

I've tried multiple things but nothing worked

I would recommend adding a Logger.log() within your function used for reduce() to confirm the numbers it is adding up. It should work. Make sure they are actual number format and not pieces of text... — Nick Gris
– Nick Gris, Commented Feb 19, 2024 at 16:32

PatrickdC · Accepted Answer · 2024-02-19 17:37:12Z

1

Add .flat() and .filter(Number) to Get the Correct Sum

I have observed that in:

var array = colonnaP.getValues()
  Logger.log(array)
    var somma = array.reduce(function(a,b) {
      return a+b});
  Logger.log(somma);

You did not take into account that the array returned by getValues() is 2-dimensional. In order for you to use the reduce() function correctly, you must flatten the array into 1 dimension by using flat(). You must then use .filter(Number) in order for you to only extract all numerical values to be summed. With this, you should replace:

var array = colonnaP.getValues()

with:

var array = colonnaP.getValues().flat().filter(Number);

Test

Using a modified version of the first half of your code and placing random numbers on column P:

function sommami() {
  var foglioAssets = SpreadsheetApp.getActive().getSheetByName('Assets');
  Logger.log(foglioAssets)
  var colonnaP = foglioAssets.getRange('P2:P100');
  Logger.log(colonnaP);
  //Add flat() since the array produced by getValues() is 2 dimensional
  //Add filter(Number) to get only the number values to add
  var array = colonnaP.getValues().flat().filter(Number);
  Logger.log(array)
  var somma = array.reduce(function (a, b) {
    return a + b
  });
  Logger.log(somma);
}

I got the following output:

References:

answered Feb 19, 2024 at 17:37

PatrickdC

2,5743 gold badges10 silver badges30 bronze badges

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

3 Comments

PatrickdC Over a year ago

Feel free to ask anything about the answer posted above. Please do take note that I only used half of your code since I checked that the cause of getting a string and not the sum you expected is just the error in the first half.

Pietro Ottati Over a year ago

That worked thanks. I'm just trying to learn how to use this app scripts but I've not found simple for beginners guide or wiki... Could you give me some tips how to start from zero? Thanks

PatrickdC Over a year ago

You can actually start by watching tutorials on YouTube. From there, you may read the Google Apps Script Developers Guide like this one for sheets. Also, JavaScript tutorials are also helpful since Google Apps Script is close to JavaScript.

Cooper · Accepted Answer · 2024-02-19 18:29:04Z

0

Try it like this:

function sommami() {
  var sh = SpreadsheetApp.getActive().getSheetByName('Assets');
  var rg = sh.getRange('P2:P100');
  var vs = rg.getValues().flat();
  var somma = vs.reduce((a,v,i) => (a += v, a));
  var sh1 = SpreadsheetApp.getActive().getSheetByName('HistoricalNW');
  var len = sh1.getRange('A1:A'+ sh1.getLastRow()).getValues().length + 1;
  var data = Utilities.formatDate(new Date(), "GMT", "dd/MM/yyyy");
  sh1.getRange('A' + len).setValue(data);
  sh1.getRange('B' + len).setValue(sum);
}

edited Feb 19, 2024 at 18:29

answered Feb 19, 2024 at 18:07

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

Collectives™ on Stack Overflow

Array reduce concat instead of sum values

2 Answers 2

Add .flat() and .filter(Number) to Get the Correct Sum

Test

References:

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Add .flat() and .filter(Number) to Get the Correct Sum

Test

References:

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related