Escaping text wih google spreadsheet api

Question

I am using the google spreadsheet api to update data in several spreadsheets and I need to escape some texts to use them in the following formula:

=IFNA(FILTER('Another_SheetName'!B:B, 'Another_SheetName'!A:A=92433), HYPERLINK(url, "some_text"))

where Another_SheetName is the the name of the another sheet that I get using the spreadsheet api and some_text a text that I want to put and I have the following two problems:

1. If some_text have doble quotes (") I get a Formula parsing error. I can solve this putting some_text in cell A1 and then changing the formula to =HYPERLINK(url, A1), but I want to know if there is a general way to escape the text some_text to use it inside the formula so that it is not necessary to use an additional cell.
2. If Another_SheetName can have a single quotes (') I get an Unsolved Sheet Name. I can fix this replacing ' by '' everytime in Another_SheetName, but there is another case I need to consider? Or there is a way to scape the text Another_SheetName to use it in a formula like =FILTER('Another_SheetName'!B:B, 'Another_SheetName'!A:A=92433)?

Here is an example.

I update the sheet with the following code:

c = '''=IFNA(FILTER('Another' SheetName'!B:B, 'Another' SheetName'!A:A=92433), HYPERLINK(url, "some" text"))'''
batch_request = {'requests': [
    {'updateCells': {
        'start': {'sheetId': sheetId, 'rowIndex': i, 'columnIndex': i},
        'rows': [
            {'values': [{'userEnteredValue': {'numberValue': 1}}, {'userEnteredValue': {'stringValue': 'asd'}}]},
            {'values': [{'userEnteredValue': {'formulaValue': c}}, {'userEnteredValue': {'formulaValue': c}}]}
        ],
        'fields': 'userEnteredValue'
    }}
]}
serviceSheet = googleapiclient.discovery.build('sheets', 'v4', credentials=credentials)
serviceSheet.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=batch_request).execute()

Thank you for your help!!

---

Update

I decided to detail the problem in the first question with a simpler example. The problem is that I have a text with a double quote (") inside and if I use the following code I get a Formula parsing error.

url = 'https://stackoverflow.com/'
some_text = 'some "text'
c = f'=HYPERLINK("{url}", "{some_text}")'
batch_request = {'requests': [
    {'updateCells': {
        'start': {'sheetId': sheetId, 'rowIndex': 0, 'columnIndex': 0},
        'rows': [
            {'values': [{'userEnteredValue': {'formulaValue': c}}]}
        ],
        'fields': 'userEnteredValue'
    }}
]}
serviceSheet = googleapiclient.discovery.build('sheets', 'v4', credentials=credentials)
serviceSheet.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=batch_request).execute()

The way I find to solve this is problem is to insert the content of some_text in a given cell, for example A1, and then reference it in the above formula. Something like this:

url = 'https://stackoverflow.com/'
some_text = 'some "text'
c = f'=HYPERLINK("{url}", A1)'
batch_request = {'requests': [
    {'updateCells': {
        'start': {'sheetId': sheetId, 'rowIndex': 0, 'columnIndex': 0},
        'rows': [
            {'values': [{'userEnteredValue': {'stringValue': some_text}}, {'userEnteredValue': {'formulaValue': c}}]},
        ],
        'fields': 'userEnteredValue'
    }}
]}
serviceSheet = googleapiclient.discovery.build('sheets', 'v4', credentials=credentials)
serviceSheet.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=batch_request).execute()

but there is a way to solve this without using another cell?

Answer 1

I believe your goal is as follows.

• In your question, Another_SheetName is used as the sheet name. But from your showing script, Another' SheetName is used. Also, from If Another_SheetName can have a single quote (') I get an Unsolved Sheet Name., I thought that your sheet name might include the single quotes.

For example, when ='Another_SheetName'!B:B is put in a cell, it seems that the single quotations are automatically removed. For example, when ='Another SheetName'!B:B is put in a cell, it seems that the single quotations are not removed. This situation occurs for both manually putting and putting by Sheets API.

And, for example, when ='Another' SheetName'!B:B is used, the reference error occurs. In this case, it is required to be ='Another'' SheetName'!A1.

From these results, how about the following modification?

Modified script:

From:

c = '''=IFNA(FILTER('Another' SheetName'!B:B, 'Another' SheetName'!A:A=92433), HYPERLINK(url, "some" text"))'''

To:

• In this case, ' is replaced with '' and use it. When this script is run, =IFNA(FILTER('Another'' SheetName'!B:B, 'Another'' SheetName'!A:A=92433), HYPERLINK("https://stackoverflow.com", "some_text")) is put to a cell.

    url = "https://stackoverflow.com"
    sheetName = "Another' SheetName"
    sheetName = sheetName.replace("'", "''")
    c = f"=IFNA(FILTER('{sheetName}'!B:B, '{sheetName}'!A:A=92433), HYPERLINK(\"{url}\", \"some_text\"))"
  

• In this case, INDIRECT is used. When this script is run, =IFNA(FILTER(INDIRECT("'Another' SheetName'!B:B"), INDIRECT("'Another' SheetName'!A:A")=92433), HYPERLINK("https://stackoverflow.com", "some_text")) is put to a cell.

    url = "https://stackoverflow.com"
    sheetName = "Another' SheetName"
    c = f'=IFNA(FILTER(INDIRECT("\'{sheetName}\'!B:B"), INDIRECT("\'{sheetName}\'!A:A")=92433), HYPERLINK("{url}", "some_text"))'
  

Added:

About your updated question,

When you want to use the following script,

some_text = 'some "text'
c = f'=HYPERLINK("{url}", "{some_text}")'

How about the following modification?

some_text = 'some "text'.replace('"', '""')
c = f'=HYPERLINK("{url}", "{some_text}")'

In this modification, " is replaced with "". By this, the text of some "text can be used.

By the way, about your following 2nd script, the formula is put to the cell "A1". But, the formula uses the value of the cell "A1". In this case, the formula doesn't work. Please be careful about this.