use setFormula() on a row of cells in Google Script?

Question

I am trying to sum up a column of data points using the setFormula() function. I'm using the formula =Sum().

I'm aiming to add a range of cells inside =Sum(x:y) in my spreadsheet.

So far I've tried to use the get.Range(startRow, startColumn, amountOfRows).

But, I'm only getting an error in my spreadSheet #Name. The actual contents of the cell is '=Sum(Range)'

The problem is in my get.Range() method. Rather than input a range of cells to sum, it just sticks in a String "Range".

I'd like help using getRange() to obtain a valid range of cells that = =Sum(...) can sum up. How can I get the proper range of cells (in this case it would be 15,2,3 Or row 15, column 2, 3 total rows down.

function sumOwe3(startRow, startColumn, lastRow){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  //var sh = SpreadsheetApp.getActiveSheet();
  startColumn = startColumn +1;
 //var sum = 0;
  //var end = 0; //end is what Im temp using 
  var fillDownRange = sheet.getRange(startRow, startColumn, lastRow-startRow);
  Logger.log(fillDownRange);
  sheet.getRange(lastRow, startColumn+2).setFormula("=Sum("+ fillDownRange +")");
  //SpreadsheetApp.getActiveSheet().getRange(end, startColumn).setValue(sum);
  SpreadsheetApp.getActiveSheet().getRange(lastRow, startColumn+2).setBackground("Yellow");
  var sum = sheet.getRange(lastRow, startColumn+2).getValue();
  Logger.log("The sum is from sumOwe3 is " +sum);
  return sum;

}

ADW ADW · Accepted Answer · 2019-08-10T05:12:44

The getRange() gets the range for further processing like getting the values from the range.

If you need to get the address of the range to use in the formula, try using .getA1Notation(). Ref docs here.

Try changing this:

  var fillDownRange = sheet.getRange(startRow, startColumn, lastRow-startRow);

to:

  var fillDownRange = sheet.getRange(startRow, startColumn, lastRow-startRow).getA1Notation();

use setFormula() on a row of cells in Google Script?

1 Answers