Use setFormula method with appendRow

user_9441494 0
Viewed 604

I have a Google Apps Script web app that takes values from the request and inserts them in a new row using the appendRow method. Next to inserting the values, I also want to insert formulas in a few columns. Is it possible to do this within appendRow()? Or is there another way to get the row number of the row that was just added, get the cell via getRange and then use the setFormula method?

Please note that I need to keep using the appendRow method and not a workaround via getLastRow due to that multiple request can be made at the same time.

google-apps-script google-sheets
2 Answers

You're going to have to use getLastRow() if you need the last row unless you want to keep track of it yourself.

You can do something like this:

function testtest() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const lr=sh.getLastRow()+1;
  const s=`=SUM(A${lr}:C${lr})`;
  sh.appendRow([1,1,1,s]);//use whatever values you wish
}

Note: you must be using V8

user_72150910

Values starting with '=' get interpreted as formulas:

In order to set formulas via appendRow, you just need to provide the formula itself, starting with '='. It will get interpreted as a formula. It's the same behaviour than setValue(value):

Sets the value of the range. The value can be numeric, string, boolean or date. If it begins with '=' it is interpreted as a formula.

Example:

sheet.appendRow(["yourRegularValue", "=yourFormula"]);
user_106120110
Related