Google-sheets – Adding numbers to a cells formula

google sheetsgoogle-apps-script

Let's say A1= X+Y
Now I'd like to add +Z with a script so I end up with A1= x+y+z. I know how to copy it, or overwrite it, but not sure exactly how I would add to it with a changing formula in A1.

Some combination of getrange and copyto but also adding something to the end?

Best Answer

You could also resolve this by storing variable z into the script and then call for it like:

var variables = [2.54]
function zzz() {
  return variables[0];
}

A1:

=5+10+zzz()

or A1:

=B1+C1+zzz()

Different Example:

Short Answer

Replace if( r.getColumn() != 5 ) by if( r.getColumn() != 5 && r.getRow() != 1 ) .

Long answer

&& is the AND operator r.getRow() != 1 compares the row of the edited cell with the row number that you want to be skipped.

Full code minimal modification

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange("E" + row.toString()).setValue(time);
  };
};

Full code improved

Taking advantage that onEdit() event gets an event object that include the range.

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    s.getRange("E" + row.toString()).setValue(time);
  };
};

Best Answer

Related Solutions

Google Sheets – Adding Formulas to Google Forms Responses

Google Sheets – Update Column with Last Row Update Except Header

Short Answer

Long answer

Full code minimal modification

Full code improved

References

Related Topic