How to add multiple hyperlinks in a single-cell in Google Sheets using Python?

I'm working on a Python script to update a Google Sheets document using the Google Sheets API, and I need to add multiple hyperlinks in a single cell along with some messages. However, I'm having trouble achieving this. Here's what I'm trying to do:

I have a Python script that updates a specific cell with hyperlinks in a Google Sheets document. The hyperlinks are supposed to be formatted in a way that each link is followed by a message. For example, I want the cell to contain two hyperlinks, each with a distinct message, and I'd like them to look something like this:

1. Google Drive (Hyperlink to "https://drive.google.com/")

2. Amazon Drive (Hyperlink to "https://www.amazon.com")

I've tried using the HYPERLINK function within the cell data, with CHAR(10) to insert line breaks, but it's not working as expected. The cell displays the text, but the links are not clickable.

Here's the Python code I'm using:

def list_problems(percentage_difference, asin, col_name, message="OK"):
        LIST_PROBLEMS = 'List Problems'

        worksheet = gc.open_by_key(spreadsheet_id).worksheet(LIST_PROBLEMS)
        cell = worksheet.find(asin)
        
        hyperlink_url1 = "https://drive.google.com/"
        hyperlink_url2 = "https://www.amazon.com"  # Replace with the Amazon link
        text1 = "Google Drive"
        text2 = "Amazon Drive"
        
        cell_format = {
            "backgroundColor": {
                "red": 183/255,
                "green": 225/255,
                "blue": 205/255
            }
        }
        if percentage_difference >= 10:
            cell_data = (
                f'=HYPERLINK("{hyperlink_url1}", "{text1}") & " | " & HYPERLINK("{hyperlink_url2}", "{text2}")'
            )
        else:
            cell_data = (
                f'=HYPERLINK("{hyperlink_url1}", "{text1}") & " | " & HYPERLINK("{hyperlink_url2}", "{text2}")'
            )

        worksheet.update(f"{ColumnsRange[col_name]}{cell.row}", "", value_input_option='USER_ENTERED')  # Clear the cell
        worksheet.update(f"{ColumnsRange[col_name]}{cell.row}", cell_data, value_input_option='USER_ENTERED')
        
        # Apply cell formatting
        worksheet.format(f"{ColumnsRange[col_name]}{cell.row}", cell_format)

Here's how it looks like in the sheet when i run the code. Google sheet Cell is not hyperlink...

I believe your goal is as follows.

• You want to put a value including multiple hyperlinks into a cell of Google Spreadsheet.
• You want to achieve this using gspread with Python.

In your situation, how about the following sample script?

Sample script:

In this sample, the value is put into a cell "A1" of "Sheet1" on a Google Spreadsheet using gspread.

import gspread

client = # Please use your gspread client.
spreadsheetId = "###" # Please set your Spreadsheet ID.
sheetName = "Sheet1" # Please set your sheet name.

# These values are from your showing script.
hyperlink_url1 = "https://drive.google.com/"
hyperlink_url2 = "https://www.amazon.com"
text1 = "Google Drive"
text2 = "Amazon Drive"
cell_format = {"backgroundColor": {"red": 183/255, "green": 225/255, "blue": 205/255}}

spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)
obj = [{"t": text1, "u": hyperlink_url1}, {"t": text2, "u": hyperlink_url2}]
text = "\n".join([e["t"] for e in obj])
requests = [
    {
        "updateCells": {
            "rows": [
                {
                    "values": [
                        {
                            "userEnteredValue": {"stringValue": text},
                            "textFormatRuns": [{"format": {"link": {"uri": e["u"]}}, "startIndex": text.find(e["t"])} for e in obj],
                            "userEnteredFormat": cell_format
                        }
                    ]
                }
            ],
            "range": {"sheetId": sheet.id, "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 1},
            "fields": "userEnteredValue,textFormatRuns,userEnteredFormat"
        }
    }
]
spreadsheet.batch_update({"requests": requests})

Testing:

When this script is run, the following result is obtained. You can see the cell value including multiple hyperlinks in "A1".

References:

• Method: spreadsheets.batchUpdate
• UpdateCellsRequest