Using apostrophes in names in Google Sheets

Question

Problem:

I have a spreadsheet with tabs and formulae that derive from proper names. I'm running into problems running an apps script where the proper name includes apostrophes.

Goal:

I want to be able to keep the apostrophes in names without causing spreadsheet chaos.

Attempted solutions:

I was able to fix the syntax for the sheet tabs by replacing ' punctuation with ", as follows:

Example 1 (wrong):

spreadsheet.setActiveSheet(spreadsheet.getSheetByName('O'Brien requests page'), true);

Fix for Ex. 1:

spreadsheet.setActiveSheet(spreadsheet.getSheetByName("O'Brien requests page"), true);

However, I can't seem to get it right for search terms and set values in formulae. I've tried using two consecutive ' marks instead of ", which I saw on a previous answer, but that only produced an error. The same thing happened when I replaced all single ' marks with ", so that's no help in this case. These are the two formulae where I'm lost:

Still stuck:

Example 2 (still wrong):

spreadsheet.getRange('B6').setFormula('=iferror(query(\'Contact list\'!A6:F,"Select A,B,C,D where C=\'O'Brien, Conan\'",0),"None")');

Example 3 (still wrong):

spreadsheet.getRange('D3').setFormula('=ARRAYFORMULA(IF((NOT(ISBLANK(A3:A))),"O'Keefe",""))');

How do I fix this without demanding that people change their names? Thank you!

Answer 1

In your script, how about the following modification?

From:

spreadsheet.getRange('B6').setFormula('=iferror(query(\'Contact list\'!A6:F,"Select A,B,C,D where C=\'O'Brien, Conan\'",0),"None")');

spreadsheet.getRange('D3').setFormula('=ARRAYFORMULA(IF((NOT(ISBLANK(A3:A))),"O'Keefe",""))');

To:

const formula1 = `=iferror(query('Contact list'!A6:F,"Select A,B,C,D where C=""O'Brien, Conan""",0),"None")`;
spreadsheet.getRange('B6').setFormula(formula1);

const formula2 = `=ARRAYFORMULA(IF((NOT(ISBLANK(A3:A))),"""O'Keefe""",""))`;
spreadsheet.getRange('D3').setFormula(formula2);