CODEWITHSUNDEEP
google-apps-script
Don Beasley
Don Beasley

Reputation: 27

google script to keep a running total when updated

I'm designing a Google sheet to track stats for members of my gaming guild. Stats will be updated often for each member and a running total must be kept for each stat of each member.

Example: if Stat1 is updated then Stat1Total will equal Stat1Total + Stat1.

This needs to happen for multiple stats and the stats will not be updated at the same time. Sometimes Stat1 for member 1 may be updated but not Stat2 and no stats for member 2.

I've tried Stat1Total=Stat1Total+Stat1 with Iterative calculation on but this updates all Stat Totals even if the Stat has not been updated. I need the StatTotal to update only if the Stat has be updated.

I found this script, but can not work out how to adapt it to work on multiple/different cells. This only works with B7 and B6 of a certain sheet. Maybe if it would get the active cell and then update a another cell based on what the active cell is?

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var b7 = s.getRange("B7");
  var b6 = s.getRange("B6");
  var activeCell = s.getActiveCell().getA1Notation();
  if( s.getName()=="Sheet1" && activeCell=="B6" )
    b7.setValue(b7.getValue()+b6.getValue());
};

Thank you in advance for any insight/help.

Edit: Thanks for all the answers, but, probably due to my own inexperience, I'm still having trouble making it work. So I will back track to my original problem with example:

         INF      INF TOTAL    Gems      GEMS TOTAL

Character 1 (A1) (B1) (C1) (D1)

Character 2 (A2) (B2) (C2) (D2)

So, I put in 100 in A1, B1 should update with B1+A1 and now B1 should =100. Nothing else should update. Then sometime later, I put 200 in A1, B1 should update to 300 (B1(100)+A1(200). Again, nothing else should change. Should also work for Character 2 and gems. Make sense? Note:The totals will be on another sheet, but even if they must stay on same sheet I can get them over.

Again, I tried B1=B1+A1 but B1 updates on any event causing B1 to update by the value in A1 even if A1 is not updated.

I was hoping for a function which could see what was updated. Something like:

(this would be in formula bar of cell B2 =MyFunction(B1) It sees that B1 has been updated and then updates cell by that value.

Again, thank you all for the help and I apologize for my inexperience :)

Upvotes: 0

Views: 491

Answers (2)

Cooper
Cooper

Reputation: 64062

I modified it just a bit by using e.range and added a few temp variables to debug it and it's working okay like this. Make sure you create your onEdit event. And if you set notificaton to immediate you'll get feedback within a few minutes of problems in the event. BTW you don't have to name the function onEdit you can give it any valid function name.

function onEdit1(e) 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var b7 = s.getRange("B7");
  var b6 = s.getRange("B6");
  //var activeCell = s.getActiveCell().getA1Notation();
  var editCell = e.range.getA1Notation();
  //var editCell='B6';
  if( s.getName()=="Sheet6" && editCell=="B6" )
  {
    var v = b7.getValue()+b6.getValue();
    //SpreadsheetApp.getUi().alert('v = ' + v + ' editCell = ' + editCell);
    b7.setValue(v);
   }
}

Okay here's the code for the second example.

function onEdit1(e) 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var col = e.range.getColumn();
  var row = e.range.getRow();
  if(col==1 && row<3)
  {
    s.getRange(row, col+1).setValue(Number(s.getRange(row, col+1).getValue())+Number(e.value));
  }
}

Upvotes: 1

Anton Dementiev
Anton Dementiev

Reputation: 5716

You can get the coordinates of the edited cell and calculate the coordinates of the cell you need to write to by inspecting the event object (e). Try this

function onEdit(e) {

var editRange = e.range;  // the edited range
var sheet = SpreadsheetApp.getActiveSpreadsheet() //change to your sheet
                          .getSheets()[0];
var editRow = editRange.getRow(); // row of the edited cell
var editCol = editRange.getColumn(); //column of the edited cell

var targetCol = 3;  //column index for the target cell

if(editCol == 2) {  // check if the cell was in column 2(B)

var editValue = editRange.getValue();

if(editValue) { //check if the value is not empty string("") or 0

    var targetRange = sheet.getRange(editRow, targetCol); //target cell in the same row as the edited cell
    targetRange.setValue(targetRange.getValue() + editValue);


}



}

}

Upvotes: 1

Related Questions