CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Andrew Harrell
Andrew Harrell

Reputation: 63

Google Apps Script Q - In Sheets is it possible to setFormula (SUM) where cells to sum change depending on row?

I am trying to write a function which will setFormula to sum values of 2 cells. The formula would need to change depending on the row being worked on.

For example:

I have a function that will set the formula in the first useable row, but can't figure out if possible to modify it to work as described above.

function onEdit(e) {
  var row = e.range.rowStart;
  var col = e.range.columnStart;
  
  if (row > 7 && col == 4) {
    insertCurrentTime(e.range.offset(0, 1)); // This sets the value in Column E
    insertDurationFormula(e.range.offset(0, 2)); //This sets the formula in Column F
  }
}

function insertCurrentTime(cell) {
  cell.setValue(new Date());
}

function insertDurationFormula(cell) {
  cell.setFormula("=SUM(E8-D8)");
}

Upvotes: 1

Views: 166

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

You need to use template literals to incorporate the cell row number into the string argument of the setFormula function.

let crow = cell.getRow();
cell.setFormula(`=SUM(E${crow}-D${crow})`);

Please modify insertDurationFormula as it is suggested in my solution.

Solution:

 function onEdit(e) {
  var row = e.range.rowStart;
  var col = e.range.columnStart;
  
  if (row > 7 && col == 4) {
    insertCurrentTime(e.range.offset(0, 1)); // This sets the value in Column E
    insertDurationFormula(e.range.offset(0, 2)); //This sets the formula in Column F
  }
}

function insertCurrentTime(cell) {
  cell.setValue(new Date());
}

function insertDurationFormula(cell) {
  let crow = cell.getRow(); // new code
  cell.setFormula(`=SUM(E${crow}-D${crow})`); // modified
}

Upvotes: 2

Related Questions