I'm trying to write a function that sets a formula in a cell using relative R1C1 notation because I don't know what row number the cell will have. I've used cell.setFormulaR1C1(string) successfully twice, but in a third case I'm not sure what's not working.

When I pass this string to setFormulaR1C1() applied to cell H34

"=R[0]C[-4]*VALUE(IFERROR(REGEXEXTRACT(INDEX(CSE!I:I;MATCH(R[0]C[-6];CSE!A:A;0);1);\"[0-9]+\");\"0\"))" ;

should spit out

=D34*VALUE(IFERROR(REGEXEXTRACT(INDEX(CSE!I:I,MATCH(A34,CSE!A:A,0),1),"[0-9]+"),"0"))

...right? But instead it is not translating the string into A1 notation and I receive a parse error. The backslashes

Elsewhere I have had success using a very similar formula...

paymentDueFormulaCell.setFormulaR1C1("=R[0]C[-3]-R[0]C[-2]-R[0]C[-1]");

which, when set into cell J34 gets correctly rendered as as

=G34-H34-I34

Can anyone intuit the differences I might be missing?

1

3 Answers

Short answer

Check that the CSE sheet exists in your spreadsheet and that your code use the proper parameter separator.

Explanation

I created a simple function to test the formula provide by the OP. It didn't work at first, but then I realized that my spreadsheet didn't has a sheet named CSE. After I added it, the below code work fine.

function myFunction() { var ss = SpreadsheetApp.getActive(); var range = ss.getRange('H34'); var formula = '=R[0]C[-4]*VALUE(IFERROR(REGEXEXTRACT(INDEX(CSE!I:I,MATCH(R[0]C[-6],CSE!A:A,0),1),"[0-9]"),"0"))'; range.setFormulaR1C1(formula);
}

Remarks:

1. I replaced the external quotes marks by apostrophes and removed the scapes of the inner quotes marks.
2. I replaced semicolons by commas as the later correspond to my default regional settings in my spreadsheets.

Note: After posting the version 1, I observed that the code formula use semicolons as formula parameter separator but a few lines later, when it was shown the expected result, the formula uses commas instead of semicolons. .setFormulaR1C1(formula) requires that a proper formula is passed as parameter in order to convert the R1C1 style references.

I've experienced similar case. It seems that you cannot combine the R1C1 notation with A1 notation. Say if you want to stick with R1C1 you should change the INDEX reference from CSE!I:I to CSE!C9:C9

Have you tried using the Indirect formula? It turns any string into a cell reference.

=indirect("some string"&"some string")

for example:

=indirect("A"&"1") would be interpreted as cell A1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy