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

Question

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:

Google Drive (Hyperlink to "https://drive.google.com/")
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 tried replicating but using Apps Script and it works. Have you considered using Apps script as alternative solution? You can also check this https://stackoverflow.com/questions/61787891/how-to-extract-the-link-from-a-cell-now-that-links-are-not-reflected-as-hyperlin/61788303\#61788303 — cnavar
– cnavar, Commented Oct 17, 2023 at 18:11
@cnavar I read about this solution but I need to do that in python... — Roni Jack Vituli
– Roni Jack Vituli, Commented Oct 17, 2023 at 20:27
Although I'm not sure whether I could correctly understand your expected result, I proposed an answer. Please confirm it. If I misunderstood your expected result and that was not useful, I apologize. — Tanaike
– Tanaike, Commented Oct 17, 2023 at 23:59

Tanaike · Accepted Answer · 2023-10-17 23:59:17Z

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".

Collectives™ on Stack Overflow

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

1 Answer 1

Sample script:

Testing:

References:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Testing:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related