javascript - 如何通过 Google 脚本获取每个单独列的单独总和？

Question

我想获取范围内每个单独列的数值总和。我做错了什么？

  function countNutrients(){
  
  var sh = SpreadsheetApp.getActiveSheet();
  var lastcol = sh.getLastColumn(); //Get last column
  var lastcolval = sh.getRange(1, 1, 1, lastcol).getValues();
  var lastrowval = sh.getRange("C1:C").getValues(); //Trick to get last row
  var lastrow = lastrowval.filter(String).length;   //
  var sumvalues = sh.getRange(2, 14, lastrow, lastcol).getValues();
    (typeof sumvalues === 'number' ? "" : sumvalues);
    Logger.log(sumvalues);
    for (var j = 13; j < lastcolval.length ; j++) {
       var sumcolumnval = sh.getRange(2, [j], lastrow);
       var sum = 0;
       for (var i in sumcolumnval[0]) {
       var sum = sumvalues[0][i] + sum;
     }
     return sum
     Logger.log(sum);
  }
  return sumcolumnval
  
}

屏幕截图上的列以及其他 65 列

enter link description here

CSV 示例

Answer 1

带有脚本的列总和

function sumOfCols() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet14');
  var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  var sum={};
  hA.forEach(function(h,i){sum[h]=0;});
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var v=rg.getValues();
  v.forEach(function(r,i){r.forEach(function(c,j){sum[hA[j]]+=c;});});
  var html='';
  hA.forEach(function(h,i){html+=Utilities.formatString('<br />%s(sum): %s',h,sum[h]);});
  var ui=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(ui, "Column Sums")
}

电子表格:

输出对话框:

数据(csv):

COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10
1,2,3,4,5,6,7,8,9,10
2,3,4,5,6,7,8,9,10,11
3,4,5,6,7,8,9,10,11,12
4,5,6,7,8,9,10,11,12,13
5,6,7,8,9,10,11,12,13,14
6,7,8,9,10,11,12,13,14,15
7,8,9,10,11,12,13,14,15,16

适用于您的特定应用程序的函数

这将仅对您选择的六列进行求和

function countNutrients(){
  var sh=SpreadsheetApp.getActiveSheet();
  var hA=sh.getRange(1,14,1,6).getValues()[0];
  var sum={};
  hA.forEach(function(h,i){sum[h]=0;})
  var vA=sh.getRange(2,14,sh.getLastRow()-1,6).getValues();
  vA.forEach(function(r,i){
    r.forEach(function(c,j){
      if(!isNaN(c)) {
        sum[hA[j]]+=c;
      }
    });
  });
  var html="";
  var keys=Object.keys(sum);
  keys.forEach(function(k,i){
    html+=Utilities.formatString('<br />sum[%s]=%s',k,sum[k]);
  })
  var ui=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(ui,'Sums of Cols N through S')
}