0

I'm working with a Spreadsheet where I need to get the values of 2 ranges (in arrays) and return the sum of those. I found code and tried to plug it in, but it seems to be just concatenating the 2 arrays instead of actually summing them up.

The data I'm pulling is from my spreadsheet. The arrays are 5 Columns and 23 rows. The arrays are of the same size.

Here is my function that grabs the values of each arrays. It will then run it through the Arrays_sum function I found and return and update the table with the new totals.

    function updateBowl(){  
      var row = mainSheet.getActiveCell().getRow();
      var tagCol = mainSheet.getRange("HP52");
      var rowNum = mainSheet.getRange("HO52");
      
      tagCol.setValue("N4:N" + row);
      rowNum.setValue(row);
      
  var humpedData = mainSheet.getRange("HL54:HP77").getValues();
  var processedTable = mainSheet.getRange("ID54:IH77");
  var currentData = processedTable.getValues();
  
  var newTotals = Arrays_sum(humpedData,currentData);
  
  var setNewTotals = processedTable.setValues(newTotals);
    
      Logger.log("New Totals: " + newTotals);
      
    }

This is a function I found that supposedly sums up each array that's plugged into it, but it is not working for me.

function Arrays_sum(array1, array2) 
{
  var result = [];
  var ctr = 0;
  var x=0;

  if (array1.length === 0) 
   return "array1 is empty";
  if (array2.length === 0) 
   return "array2 is empty";   

 while (ctr < array1.length && ctr < array2.length) 
  {
    result.push(array1[ctr] + array2[ctr]);
    ctr++;
  }

 if (ctr === array1.length) 
 {
    for (x = ctr; x < array2.length; x++)   {
      result.push(array2[x]);
    }
  } 
  else
  {
  for (x = ctr; x < array1.length; x++) 
    {
      result.push(array1[x]);
    }
  }
  return result;
}

Any help would be appreciated!

Edit 1: Pasted picture of the log.

what the logger.log returns

Edit 2: In my log picture the first 1386 value is from the first cell in the FIRST array. The second 1386 is the first value in SECOND array. So it seems to concatenating the first row array with the second row array. For my testing purposes the values are the same (because of lazy) but when I can figure out the array sum, the current values and incoming values will be different.

SOLVED

Coopers answer worked. I'm not sure exactly what I tweaked to get it to work but this is the final working script. It gets 2 different arrays (of the same size) and sums the values in each cell, then pastes those values into the second array (the current totals).

function updateBowl(){  
  var row = mainSheet.getActiveCell().getRow();
  var tagCol = mainSheet.getRange("HP52");
  var rowNum = mainSheet.getRange("HO52");
  
  tagCol.setValue("N4:N" + row);
  rowNum.setValue(row);
  
  var humpedData = mainSheet.getRange("HL54:HP77").getValues();
  var processedTable = mainSheet.getRange("ID54:IH77");
  var currentData = processedTable.getValues();
  
  var newTotals = sumarrays(humpedData,currentData);
  
  var setNewTotals = processedTable.setValues(newTotals);

 
  Logger.log("New Totals: " + newTotals);
}


function sumarrays(arr1,arr2) {
  var o=[];
  var html='[';
  arr1.forEach(function(r,i) {
    o[i]=[];
    if(i>0){html+=','};
    html+='[';
    r.forEach(function(c,j){
      if(j>0){html+=','};
      o[i][j]=arr1[i][j]+arr2[i][j];
      html+=o[i][j];
    });
    html+=']';
  });
  html+=']';
  return o;
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'Output');
}
Improve this question
4

2 Answers 2

Reset to default
2

Try something like this:

function arraytest() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const a1=sh.getRange(1,1,9,9).getValues();
  const a2=sh.getRange(1,10,9,9).getValues();
  sumarrays(a1,a2);
}


function sumarrays(arr1,arr2) {
  var o=[];
  var html='[';
  arr1.forEach(function(r,i) {
    o[i]=[];
    if(i>0){html+=','};
    html+='[';
    r.forEach(function(c,j){
      if(j>0){html+=','};
      o[i][j]=arr1[i][j]+arr2[i][j];
      html+=o[i][j];
    });
    html+=']';
  });
  html+=']';
  return o;
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'Output');
}

Data:

1,10,19,28,37,46,55,64,73,82,91,100,109,118,127,136,145,154,163,172,181
2,11,20,29,38,47,56,65,74,83,92,101,110,119,128,137,146,155,164,173,182
3,12,21,30,39,48,57,66,75,84,93,102,111,120,129,138,147,156,165,174,183
4,13,22,31,40,49,58,67,76,85,94,103,112,121,130,139,148,157,166,175,184
5,14,23,32,41,50,59,68,77,86,95,104,113,122,131,140,149,158,167,176,185
6,15,24,33,42,51,60,69,78,87,96,105,114,123,132,141,150,159,168,177,186
7,16,25,34,43,52,61,70,79,88,97,106,115,124,133,142,151,160,169,178,187
8,17,26,35,44,53,62,71,80,89,98,107,116,125,134,143,152,161,170,179,188
9,18,27,36,45,54,63,72,81,90,99,108,117,126,135,144,153,162,171,180,189

Output:

[[83,101,119,137,155,173,191,209,227],[85,103,121,139,157,175,193,211,229],[87,105,123,141,159,177,195,213,231],[89,107,125,143,161,179,197,215,233],[91,109,127,145,163,181,199,217,235],[93,111,129,147,165,183,201,219,237],[95,113,131,149,167,185,203,221,239],[97,115,133,151,169,187,205,223,241],[99,117,135,153,171,189,207,225,243]]

You can put constraints on it depending upon how the data is collected.

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

7 Comments

Yep that's working! Only thing is, how do I put that into a return so my var newTotals equals that data and I can then use that new data to paste it to my Spreadsheet? Thanks a lot !
Maybe processedTable.setValues(o);
okay I tired that. when I log the return, it just says "undefined" I had to use processedTable.setValue(o); (taking off the s, since it's an array I guess) and when I plug it into my spreadsheet, it pastes that "undefined" text into every cell.
Trying sharing your updated code and ask another question
Okay I might do that. Thanks for the help. Definitely getting somewhere! I changed the return from o to html and when logged, it actually returned the values, but in a string instead of an array. So I pasted that return and it set every single cell in the range to that string. haha.
|
2

I hope this script will be an answer and a guide.

You can use this inside your spreadsheet as a normal function. Like this:

=arr_arr(A1:D5,"+",F6:K9)

The code:

/**
* Return the sum of total array one + array two
*
* @param {A1:D10} range - First range to sum.
* @param {"+ - / *"} operator - Operator to use.
* @param {E1:F10} range - Second range to sum.
* @return the sum of all the values
* @customfunction
*/
function arr_arr(range1,op,range2) {
  
  const one = [].concat(...range1);
  const two = [].concat(...range2);
  const sumOne = one.reduce((a, b) => a + b, 0);
  const sumTwo = two.reduce((a, b) => a + b, 0);
  let sum = 0;
  
  switch (op) {
    case "+":
      sum = sumOne + sumTwo;
      break;
    case "-":
      sum = sumOne - sumTwo;
      break;
    case "*":
      sum = sumOne * sumTwo;
      break;
    case "/":
      sum = sumOne / sumTwo;
      break;
  }
      
      return sum;
      
  }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.