CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Terry
Terry

Reputation: 47

Script for finding the sum of the last cell in specific columns

I am trying to make this Google script for Sheets so that finds the sum of the last values in F,N,R,S,T and inputs that value into the last cell in column U. The following works but I want the script to grab the last cell with a value in it from each of the 5 columns.

        function myFunction() {
      var sheetname = "SHEET1"; 
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
    var num1 = sheet.getRange("F2710").getValue();
    var num2 = sheet.getRange("N2710").getValue();
     var num3 = sheet.getRange("R2710").getValue();
     var num4 = sheet.getRange("S2710").getValue();
    var num5 = sheet.getRange("T2710").getValue();
    sheet.getRange("U2710").setValue(num1+num2+num3+num4+num5);
}

Thank you for your time and help!

Upvotes: 0

Views: 126

Answers (1)

Cooper
Cooper

Reputation: 64082

Sum of bottoms

You have to provide the sheet name and the sumrow and then rum getSumNow().

sumrow is one of the properties of the initial value object in the reduce function.

If you wish to display the sum somewhere else the it is available with this method: obj.getSum();

function getSumNow() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var cols=[6,14,18,19,20];
  var obj=cols.reduce(function(a,c,i){
    a.sum+=sh.getRange(getLastRowInCol(c,sh,ss),c).getValue();
    return a;
  },{sumcol:21,sumrow:21,sum:0,getSum:function(){sh.getRange(this.sumrow,this.sumcol).setValue(this.sum);}}).getSum();
}

The function below was used in the above function so it had to be provided.

function getLastRowInCol(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  const rcA=sh.getRange(1,col,sh.getLastRow(),1).getValues().reverse()
  let s=0;
  for(let i=0;i<rcA.length;i++) {
    if(rcA[i][0].toString().length==0) {
      s++;
    }else{
      break;
    }
  }
  return rcA.length-s;
}

Array.reduce()

It appears that this is what you would prefer:

function getSumNow() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet2');
  var cols=[6,14,18,19,20];
  var obj=cols.reduce(function(a,c,i){
    a.sum+=sh.getRange(getLastRowInCol(c,sh,ss),c).getValue();
    return a;
  },{sumcol:21,sumrow:getLastRowInCol(this.sumcol,sh,ss)+1,sum:0,getSum:function(){sh.getRange(this.sumrow,this.sumcol).setValue(this.sum);}}).getSum();
}

Upvotes: 1

Related Questions