Google-sheets – Script to SUM Values

You have transaction data including three currency/value fields. The total number of rows, and the number of transactions for each user/customer is unknown and can vary.

This key facets of this script are:

• create a temporary array (temparray) to capture row values
• create a second array (targetarray) to accumulate row values, and to enable a single "write" command to update the output sheet.
• loop through the rows:
  • test for the first row (there are no previous values)
  • test for a change in name
    • if no chnage, continue to accumulate totals
    • if change, then add totals to the array, zero the "total" variables, add the values for the new line.
• after the loop,
  • add the totals for the last name.
  • Update the output range

function dataview() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sourcesheetname = "source";
  var source = ss.getSheetByName(sourcesheetname);
  var targetsheetname = "output";
  var target = ss.getSheetByName(targetsheetname);
  
  // get the number of rows of information
  var LR = source.getLastRow();
  
  // get the range and the values
  var sourcerange = source.getRange(1, 1, LR, 5);
  // Logger.log("DEBUG: the range is "+sourcerange.getA1Notation());
  var sourcevalues = sourcerange.getValues();
  
  // create variables for processing
  var targetarray = []; // array for cumulative data
  var totalC = 0;
  var totalD = 0;
  var totalE = 0;

  // loop through the rows
  for (var i=0;i<sourcevalues.length;i++){
    
    // set a temporary array for row data
    var temparray = [];
      
    // is this first row
    if (i==0){
      
      // build the line array
      temparray.push(sourcevalues[i][0],sourcevalues[i][1],sourcevalues[i][2],sourcevalues[i][3],sourcevalues[i][4]);
      // update the targetarray
      targetarray.push(temparray);
      // update the totals
      totalC = totalC + sourcevalues[i][2];
      totalD = totalC + sourcevalues[i][3];
      totalE = totalC + sourcevalues[i][4];
      
    } else{

      // not the first row, so check the names
      var thisname = sourcevalues[i][0];
      var lastname = sourcevalues[i-1][0];

      // is this a new name - No
      if (thisname ==lastname){ 
        // same name
        
        // build the line array
        temparray.push(sourcevalues[i][0],sourcevalues[i][1],sourcevalues[i][2],sourcevalues[i][3],sourcevalues[i][4]);
        // update the targetarray
        targetarray.push(temparray);
        // update the totals
        totalC = totalC + sourcevalues[i][2];
        totalD = totalC + sourcevalues[i][3];
        totalE = totalC + sourcevalues[i][4];
      }
      else{
        
        // is this a new name - Yes
        
        // populate the subototal line
        temparray.push("","",totalC, totalD,totalE);
        // update the targetarray
        targetarray.push(temparray);
        // reset the temparray
        temparray = [];
        // reset the totals
        var totalC = 0;
        var totalD = 0;
        var totalE = 0;
        
        // populate the blank line
        temparray.push("","","", "","");
        // update the targetarray
        targetarray.push(temparray);
        // reset the temparray
        temparray = [];
        
        // build the line array
        temparray.push(sourcevalues[i][0],sourcevalues[i][1],sourcevalues[i][2],sourcevalues[i][3],sourcevalues[i][4]);
        // update the targetarray
        targetarray.push(temparray);
        // reset the totals
        totalC = totalC + sourcevalues[i][2];
        totalD = totalC + sourcevalues[i][3];
        totalE = totalC + sourcevalues[i][4];
        
      }
    }
  } //end of loop
  
  // add the final subtotal
  // reset the temparray
  temparray = [];
  // populate the subototal line
  temparray.push("","",totalC, totalD,totalE);
  // update the targetarray
  targetarray.push(temparray);
  // reset the temparray
  temparray = [];
  // populate the blank line
  temparray.push("","","", "","");
  // update the targetarray
  targetarray.push(temparray);

  // define the target range
  var targetrange = target.getRange(1, 1, targetarray.length, 5)
  // update for the source values
  targetrange.setValues(targetarray)
  return false;
}