How to make a sum of arrays

Question

I'm using the following code to get all the value of all my sheets that have a numeric name. Once it find a numeric sheet name, it must sum row 8 to 108 of column 6. All the sheets must be sum in the right array. Right now, my script add the data in the array after then it crash instead of doing a sum.

example (cell = value : variable = value)

sheet 1 (8,6)=1 : colonne[1] = 1
sheet 2 (8,6)=2 : colonne[1] = 3
sheet 3 (8,6)=3 : colonne[1] = 6
sheet 1 (8,7)=2 : colonne[2] = 2
sheet 2 (8,7)=2 : colonne[2] = 4
sheet 3 (8,7)=2 : colonne[2] = 6

function CalculTotal() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var s = ss.getActiveSheet();
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var substring1;
  var colonne=[];

  for (var i=0 ; i<sheets.length ; i++) 
  {
    substring1=sheets[i].getName();
    if(!isNaN(parseFloat(substring1)) && isFinite(substring1)){
      colonne=colonne+(ss.getSheetByName(substring1).getRange(8,6,100).getValues());
              }
              }
    s.getRange(8,4,100).setValues(colonne);
}

You're going to need to be a lot more low-level than invoking the `+` operator on an `Array` if you want to sum elements of the elements of those arrays. Review how to access elements in your preferred JavaScript language reference (and do a tutorial or three). — tehhowch, Dec 21 '18 at 21:11
Related [How to find the sum of an array of numbers](https://stackoverflow.com/q/1230233/1595451) — Rubén, Dec 22 '18 at 15:35

score 0 · Answer 1 · answered Dec 22 '18 at 00:49

Here is an example of how to add 2 arrays, but notice this is done in the form of a 2D array. getValues() returns an array of array[i] i = number of rows, array[i][j] j is the column both indexes start with 0. setValues() works the same way but places a 2D array.

function CalculTotal() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var s = ss.getActiveSheet();
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var substring1;
  var colonne=[];

  // Initialize result array
  for( var i=0; i<100; i++ ) colonne[i] = [0];

  for (var i=0 ; i<sheets.length ; i++) 
  {
    substring1=sheets[i].getName();
    if(!isNaN(parseFloat(substring1)) && isFinite(substring1)){
      var values = ss.getSheetByName(substring1).getRange(8,6,100,1).getValues();
      for( var j=0; j<values.length; j++ ) {
        colonne[j][0] = colonne[j][0]+values[j][0];
      }
    }
  }
  s.getRange(8,4,100,1).setValues(colonne);
}

Cooper · Accepted Answer · 2018-12-22T01:26:06.263

Try this:

function CalcTotal() {
  var ss=SpreadsheetApp.getActive(); 
  var sheets=ss.getSheets();
  var regex=new RegExp('^[\\d\\.,]+$')
  var total=0;
  var n=1;
  var html='<style>th,td{border:1px solid #000;}</style><table>';
  html+=Utilities.formatString('<tr><th>%s</th><th>%s</th><th>%s</th></tr>','Item','Sheet','Sum')
  for(var i=0;i<sheets.length;i++){
    var name=sheets[i].getName();
    var sum=0;
    var nameisnum=false;
    var result=regex.exec(name);
    if(result){
      nameisnum=true;
      var shi=ss.getSheetByName(name);
      var rgi=shi.getRange(8,6,101,1);
      var vAi=rgi.getValues();
      for(var j=0;j<vAi.length;j++){
        sum+=vAi[j][0]; 
      }
      if(nameisnum){
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',n++,name,sum);
        total+=sum;
      }
    }
  }  
  html+=Utilities.formatString('<tr><td colspan="2">Total:</td><td>%s</td></tr>',total);
  html+='</table>';
  var ui=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Sheet Sums');
}

How to make a sum of arrays

2 Answers2