应用程序脚本中两个数组的求和(Google表格) [英] Summing of Two Arrays in App Scripts (Google Sheets)

问题描述

我正在使用电子表格，我需要获取2个范围的值(以数组为单位)并返回这些值的总和.我找到了代码并尝试将其插入，但是它似乎只是连接两个数组，而不是将它们实际求和.

我要提取的数据来自电子表格.数组为5列和23行.数组的大小相同.

这是我的函数，可获取每个数组的值.然后它将通过我找到的Arrays_sum函数运行它，并返回并用新的总数更新表.

 函数updateBowl(){var row = mainSheet.getActiveCell().getRow();var tagCol = mainSheet.getRange("HP52");var rowNum = mainSheet.getRange("HO52");tagCol.setValue("N4:N" +行)；rowNum.setValue(row);var humpedData = mainSheet.getRange("HL54:HP77").getValues();var processingTable = mainSheet.getRange("ID54:IH77");var currentData =已处理的Table.getValues();var newTotals = Arrays_sum(humpedData，currentData);var setNewTotals =已处理的Table.setValues(newTotals);Logger.log("New Totals:" + newTotals)；} 

我发现这是一个函数，它应该对插入其中的每个数组进行汇总，但对我不起作用.

 函数Arrays_sum(array1，array2){var result = [];var ctr = 0;var x = 0;如果(array1.length === 0)返回"array1为空"；如果(array2.length === 0)返回"array2为空"；while(ctr< array1.length&& ctr< array2.length){result.push(array1 [ctr] + array2 [ctr]);ctr ++;}如果(ctr === array1.length){for(x = ctr; x< array2.length; x ++){result.push(array2 [x]);}}别的{对于(x = ctr; x< array1.length; x ++){result.push(array1 [x]);}}返回结果；} 

任何帮助将不胜感激！

粘贴日志图片.

在我的日志图片中，第一个 1386 值来自FIRST数组中的第一个单元格.第二个 1386 是SECOND数组中的第一个值.因此，似乎将第一行数组与第二行数组连接在一起.出于测试目的，这些值是相同的(因为是惰性的)，但是当我可以计算出数组和时，当前值和传入值将有所不同.

已解决

库珀的答案奏效了.我不确定我为使它正常工作而进行了哪些调整，但这是最终的工作脚本.它得到2个不同的数组(大小相同)，并对每个单元格中的值求和，然后将这些值粘贴到第二个数组中(当前的总数).

 函数updateBowl(){var row = mainSheet.getActiveCell().getRow();var tagCol = mainSheet.getRange("HP52");var rowNum = mainSheet.getRange("HO52");tagCol.setValue("N4:N" +行)；rowNum.setValue(row);var humpedData = mainSheet.getRange("HL54:HP77").getValues();var processingTable = mainSheet.getRange("ID54:IH77");var currentData =已处理的Table.getValues();var newTotals = sumarrays(humpedData，currentData);var setNewTotals =已处理的Table.setValues(newTotals);Logger.log("New Totals:" + newTotals)；}函数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 + =']';返回o；SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html)，'Output');} 

解决方案

尝试如下操作:

  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);}函数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 + =']';返回o；SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html)，'Output');} 

数据:

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

输出:

<预> <代码> [[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]]

您可以根据数据的收集方式对其施加约束.

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.

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');
}

解决方案

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.

这篇关于应用程序脚本中两个数组的求和(Google表格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！