Function works in Google Sheets but does not work when using it in Google Script

Question

I have this function which works in Google Sheets, but when I put it in Google script it does not work and says I am missing a ).

=ArrayFormula(split(concatenate(filter('Copy of LeaveCache_LVE'!$G$3:$G, 'Copy of LeaveCache_LVE'!$A$3:$A=A3)),"))"))

This is what I put in Google script:

cell.setValue('=ArrayFormula(split(concatenate(filter('Copy of LeaveCache_LVE'!$G$3:$G, 'Copy of LeaveCache_LVE'!$A$3:$A=A3)),"))"))');

Look at the quote marks you are using. (Hint: the formatter here is showing the same issue that Apps Script is detecting). — tehhowch
– tehhowch, Commented Apr 8, 2019 at 13:01

Frost · Accepted Answer · 2018-01-26 11:12:32Z

2

I have found a solution that keeps the Built In Functions as follows:

This was the original that do NOT work:

cell.setValue('=ArrayFormula(split(concatenate(filter('Copy of LeaveCache_LVE'!$G$3:$G, 'Copy of LeaveCache_LVE'!$A$3:$A=A3)),"))"))');

//////////////////////////////////////////////////////////////////////////////

This is the solution that DID work:

cell.setValue('=ArrayFormula(split(concatenate(filter(\'Copy of LeaveCache_LVE\'!$G$3:$G, \'Copy of LeaveCache_LVE\'!$A$3:$A=A3)),"))"))');

What I found that worked was inserting a \ (backslash) before the ' (single quotation)

Hope this helps someone else too!

edited Jan 26, 2018 at 11:12

answered Jan 26, 2018 at 11:04

Frost

2111 gold badge2 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel · Accepted Answer · 2019-04-08 12:54:23Z

0

Or you can try to use the cell.setFormula method.

answered Apr 8, 2019 at 12:54

Daniel

1

2 Comments

tehhowch Over a year ago

The issue with the post is that nested quotation marks were not escaped, so the value itself was not properly constructed. Changing the function that uses the value will not alter the problem with the value construction.

Daniel Over a year ago

Okay, thanks for help. I misunderstood the question.

Collectives™ on Stack Overflow

Function works in Google Sheets but does not work when using it in Google Script

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related