CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsformulas
Frost
Frost

Reputation: 211

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

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)),"))"))');

Upvotes: 0

Views: 89

Answers (2)

Daniel
Daniel

Reputation: 1

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

Upvotes: 0

Frost
Frost

Reputation: 211

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!

Upvotes: 2

Related Questions