CODEWITHSUNDEEP
google-apps-script
MortenMoulder
MortenMoulder

Reputation: 6646

Add up previous and current cell values when changed

I am trying to update 1 value in a Google sheet. When I select a cell which already contains a number (ex: 500), and I write a number (ex: 100), it should add them up without pressing a button (500+100).

Right now, when I edit the cell, the onEdit(e) will get triggered. I can then grab the cell's value by doing:

SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getValue()

However, I cannot get the previous (before the change/edit) value, so I will always end up adding the two same values together. Here's some example code:

//Obviously only example code
var sheet = SpreadsheetApp.getActiveSpreadsheet();

function onEdit(e) {
    if(activeRange.getColumn() == 3) {
        var currentValue = sheet.getActiveRange().getValue();
        var newValue = currentValue + sheet.getActiveRange().getValue();
        sheet.getActiveRange().activeRange.setValue(oldValue);
    }
}

Obviously I want to replace sheet.getActiveRange().getValue() in the newValue to the PREVIOUS value, otherwise I add both values up, which are the same. I hope you get what I mean. The previous value of the cell is never being sent/saved/logged anywhere.

In something like Javascript/jQuery, I would do this: When cell has been selected, save the current value in a variable. Then, when the cell has changed value, add the old value to the new value, and change the cell's value to be the newest added up value. Farely easy, but I cannot find anything about how to do this kind of trigger in Google Apps Script.

How can I possibly do this? Thanks!

Upvotes: 0

Views: 3916

Answers (1)

Bryan P
Bryan P

Reputation: 5051

"it should add them up without pressing a button" no that won't happen, but if you have 500 in Sheet1 cell C2 you need to duplicate it else where like i first commented - maybe C2 on another tab ( Sheet2 ). Then when you type 100 (and hit enter) you'll need to go and grab the old duplicated value(500), add it to the current edited value (100) then set the new calculated one(600): 

function onEdit(e) {
    if(activeRange.getColumn() == 3) {
        var ss = e.source;
        var sheet1 = ss.getActiveSheet();
        var sheet2 = ss.getSheetByName("Sheet2"); 
        var old = sheet2.getRange('C2').getValue(); // duplicated value
        var current = sheet.getActiveRange().getValue(); // value after hitting enter to edit
        var new = current + old;
        sheet1.getActiveRange().setValue(new);
        sheet2.getRange('C2').setValue(new); // update duplicated value
    }
}

You'll need to handle matching up what rows to look at for duplicate values but for this example I just used row 2.

Upvotes: 1

Related Questions