Using Row() within a formula with appendRow-CodePudding

So I'm pulling some data from GMail and adding a new row to a sheet that has a specific format. Name, Address, etc etc

On Column "P" I want to replicate the below:

=IF(NOT(ISBLANK($J3985)),"Replied", IF((TODAY()>=$O3985),"Late", "OK"))

However, I want to replace 3985 with Row(), for the row number that I'm appending, while I'm appending it. I've tried playing with: ADDRESS(row(),10) but this returns a string value that I can't seem to re-insert into a formula in a manner that works.

What I'm passing through in appendRow now:

  var replied = "";
  var later = x // a Date that's today   6 weeks
  var checkResult = `=IF(NOT(ISBLANK(`   replied   `)), "Replied", IF((TODAY()>=`   later   `), "Late", "OK"))`;

I want it so that I can populate the "responded" cell at a later point in the sheet and for this to still work. Would be keen to hear your suggestions for the same.

CodePudding user response：

If you use appendRow:

=IF(NOT(ISBLANK(INDIRECT("RC[-6]",FALSE))),"Replied", IF((TODAY()>=INDIRECT("RC[-1]",FALSE)),"Late", "OK"))

If you use setFormulaR1C1:

Method A

Putting the row number directly with template literal

Method B

You could use setFormulaR1C1(formula)

'=IF(NOT(ISBLANK(RC[-6])), "Replied", IF((TODAY()>=RC[-1]), "Late", "OK"))';