Hey everyone! I’m having a tough time with a Google Sheets script I’m working on. The setValue() function is giving me grief. Here’s the line that’s causing problems:
myCell.setFormula('=IF(AND(B5>0,OR((MONTH(B2)>1),(DAY(B2)>InfoSheet!D8),(AND(MONTH(TODAY()),A2=0)))),"X",)')
The script works fine if I take out the quotes around ‘X’, but that’s not what I want. I’m pretty sure it’s a quotation mark issue, but I can’t figure out how to fix it.
Has anyone run into this before? Any suggestions on how to make it work with the quotes intact? I’ve been scratching my head over this for a while now. Thanks in advance for any help!
I’ve run into this issue before, and it can be frustrating.
One solution that’s worked for me is using the CONCATENATE function to build your formula string. It might look something like this:
myCell.setFormula('=CONCATENATE("IF(AND(B5>0,OR((MONTH(B2)>1),(DAY(B2)>InfoSheet!D8),(AND(MONTH(TODAY()),A2=0)))),"","X","",")")');
This method avoids the quotation mark conflicts by treating the ‘X’ as a separate string within the CONCATENATE function. It’s a bit more complex, but it’s saved me headaches when dealing with intricate formulas in Google Sheets scripts. Give it a shot and see if it resolves your issue.
hey mikezhang, i’ve dealt with this before. try using double quotes for the outer quotes and single quotes for the inner ones. like this:
myCell.setFormula(“=IF(AND(B5>0,OR((MONTH(B2)>1),(DAY(B2)>InfoSheet!D8),(AND(MONTH(TODAY()),A2=0)))),‘X’,)”)
should fix ur problem. lemme know if it works!
I’ve encountered similar issues with Google Sheets scripts. The problem lies in how Google Apps Script handles string escaping. A more robust solution is to use String.fromCharCode(34) to represent quotation marks within your formula. Try this modified version:
myCell.setFormula(‘=IF(AND(B5>0,OR((MONTH(B2)>1),(DAY(B2)>InfoSheet!D8),(AND(MONTH(TODAY()),A2=0)))),’+String.fromCharCode(34)+‘X’+String.fromCharCode(34)+‘,)’)
This approach ensures proper escaping and should resolve your setValue() troubles. It’s a bit more verbose, but it’s a reliable method for handling quotes in complex formulas.