0

Can you help me please?

I want a script to sum rows after a name change.

Something like this:

COLUMN A           - COLUMN B    - COLUMN C - COLUMN D - COLUMN E
aa                 - aa          - 5€       - 5€       - 5€
aa                 - aa          - 5€       - 5€       - 5€
aa                 - aa          - 5€       - 5€       - 5€
bb                 - bb          - 5€       - 5€       - 5€
bb                 - bb          - 5€       - 5€       - 5€

I want this:

COLUMN A           - COLUMN B - COLUMN C - COLUMN D - COLUMN E
aa                 - aa          - 5€       - 5€       - 5€
aa                 - aa          - 5€       - 5€       - 5€
aa                 - aa          - 5€       - 5€       - 5€
blank              blank         - 15€      - 15€      - 15€
blank              blank         blank      blank      blank  
bb                 - bb          - 5€       - 5€       - 5€  
bb                 - bb          - 5€       - 5€       - 5€
blank              blank         - 10€      - 10€      - 10€
blank              blank         blank      blank      blank 

PS:I am in the financial area, but with a great passion for the computer area, but unfortunately, I am not that good in this area, so I asked for your help.

  • will the entries in column A be as ordered as your example suggests or will they be randomly placed in the column (so a mix of aa and bb etc) and will the descriptor always be in the same format in both column A and B and throughout column A (ie not "aa" sometimes and "aaa" others meaning a free format of input)? – User24601 Oct 7 at 14:45
  • hi, they will be ordered by name. Sometimes i have AAA, BB. Its not a fixed number of same name. Thanks – user255969 Oct 7 at 15:29
  • Well, as a starter, would an in-cell dropdown list help to regularise what is allowed in column A seehttps://support.google.com/docs/answer/186103?co=GENIE.Platform%3DDesktop&hl=en ? – User24601 Oct 7 at 15:36
  • 1
    Welcome to Web Applications. Please add a brief description of your search/research efforts as is suggested in How to Ask. You might find helpful to start by reading developers.google.com/apps-script/guides/sheets – Rubén Oct 7 at 23:33
0

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;
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.