-3

I'm trying to setFormula and I keep getting an error about Missing ) in the code. I understand that it's the double quotes that are messing me up, and that \ can escape those out, but I don't know which double quotes need to be escaped. Can someone shed insight into this?

cell.setFormula('=({filter('Master Fulfillment Sheet'!$A$5705:$A,'Master Fulfillment Sheet'!D5705:D="Published",'Master Fulfillment Sheet'!D5705:D<>"",'Master Fulfillment Sheet'!G5705:G>(today()-30));filter('Tobe Fulfillment Sheet'!$A$2:$A,'Tobe Fulfillment Sheet'!E2:E<>"",'Tobe Fulfillment Sheet'!E2:E<>"Published",'Tobe Fulfillment Sheet'!E2:E<>"Needs Attention",'Tobe Fulfillment Sheet'!E2:E<>"*Onsite Blog Post*",'Tobe Fulfillment Sheet'!E2:E<>"Order Cancelled");filter('Kendra Fulfillment Sheet'!$A$2:$A,'Kendra Fulfillment Sheet'!E2:E<>"",'Kendra Fulfillment Sheet'!E2:E<>"Published",'Kendra Fulfillment Sheet'!E2:E<>"Needs Attention",'Kendra Fulfillment Sheet'!E2:E<>"*Onsite Blog Post*",'Kendra Fulfillment Sheet'!E2:E<>"Order Cancelled");filter('Megan Fulfillment Sheet'!$A$2:$A,'Megan Fulfillment Sheet'!E2:E<>"",'Megan Fulfillment Sheet'!E2:E<>"Published",'Megan Fulfillment Sheet'!E2:E<>"Needs Attention",'Megan Fulfillment Sheet'!E2:E<>"*Onsite Blog Post*",'Megan Fulfillment Sheet'!E2:E<>"Order Cancelled");filter('Kyle Fulfillment Sheet'!$A$2:$A,'Kyle Fulfillment Sheet'!E2:E<>"",'Kyle Fulfillment Sheet'!E2:E<>"Published",'Kyle Fulfillment Sheet'!E2:E<>"Needs Attention",'Kyle Fulfillment Sheet'!E2:E<>"*Onsite Blog Post*",'Kyle Fulfillment Sheet'!E2:E<>"Order Cancelled");filter('Mary Fulfillment Sheet'!$A$2:$A,'Mary Fulfillment Sheet'!E2:E<>"",'Mary Fulfillment Sheet'!E2:E<>"Published",'Mary Fulfillment Sheet'!E2:E<>"Needs Attention",'Mary Fulfillment Sheet'!E2:E<>"*Onsite Blog Post*",'Mary Fulfillment Sheet'!E2:E<>"Order Cancelled");filter('Mariah Fulfillment Sheet'!$A$2:$A,'Mariah Fulfillment Sheet'!E2:E<>"",'Mariah Fulfillment Sheet'!E2:E<>"Published",'Mariah Fulfillment Sheet'!E2:E<>"Needs Attention",'Mariah Fulfillment Sheet'!E2:E<>"*Onsite Blog Post*",'Mariah Fulfillment Sheet'!E2:E<>"Order Cancelled")})');

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Have you tried changing the opening and closing single quote character to a grave character - so single quotes inside do not need escaping? If that doesn't help, what character in the line is the error occuring at? – traktor Oct 04 '17 at 22:09
  • 2
    Please, can you format the code in more readibility appereance? – alepuzio Oct 04 '17 at 22:15

1 Answers1

0

The problem es that code use ' as a string delimiter and as a sheet name delimiter as it is used on Google Sheets but on a Google Apps Script should be escaped by preceding them with \.

Basically, replace 'Master Fulfillment Sheet' by \'Master Fulfillment Sheet\'.

Related question

Rubén
  • 34,714
  • 9
  • 70
  • 166