CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-sheets-apigspread
Batman_110
Batman_110

Reputation: 1

Check if a cell from a google sheet has a hyperlink inserted

I want my code to check the cell values from a column and pick only the rows that contains hyperlinks. The function has_hyperlink works with a local stored excel file, but when I try to use it on the google sheet I get the error AttributeError: 'Cell' object has no attribute 'hyperlink'. This is the code that I used and here is a link of what the google sheet looks like spreadsheet. Thank you for your help!

import gspread
from oauth2client.service_account import ServiceAccountCredentials

def has_hyperlink(cell):
    """
    Check if a cell has a hyperlink.
    """
    if 'hyperlink' in cell:
        return True
    return False

# Set up credentials
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('path/to/your/credentials.json', scope)
gc = gspread.authorize(credentials)

# Open the Google Sheet by title
spreadsheet = gc.open('Your Google Sheet Title')

# Select the worksheet (e.g., the first sheet)
worksheet = spreadsheet.sheet1

# Specify the cell you want to check (e.g., B2)
cell_to_check = worksheet.cell(2, 2)

# Check if the cell has a hyperlink
if has_hyperlink(cell_to_check._properties):
    print(f"The cell B2 has a hyperlink: {cell_to_check.hyperlink}")
else:
    print("The cell B2 does not have a hyperlink")

If you may help me just with the check of the hyperlinks, it would be great. Thank you!

Upvotes: 0

Views: 911

Answers (1)

Tanaike
Tanaike

Reputation: 201673

I believe your goal is as follows.

  • You want to check whether a cell has the hyperlink using gspread with Python.

In your script, how about the following modification?

Modified script:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build


def has_hyperlink(obj, cell):
    """
    Check if a cell has a hyperlink.
    """
    r, c = gspread.utils.a1_to_rowcol(cell)
    o = obj["sheets"][0]["data"][0]["rowData"][r - 1].get("values", [])[c - 1]
    if 'hyperlink' in o:
        return True
    return False


scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('path/to/your/credentials.json', scope)
gc = gspread.authorize(credentials)

spreadsheet = gc.open('Your Google Sheet Title')
worksheet = spreadsheet.sheet1

service = build("sheets", "v4", credentials=gc.auth)
obj = service.spreadsheets().get(spreadsheetId=spreadsheet.id, fields="sheets(data(rowData(values(hyperlink,formattedValue))))", ranges=[worksheet.title]).execute()

cell1 = "A2"
res1 = has_hyperlink(obj, cell1)
print(res1)

cell2 = "B2"
res2 = has_hyperlink(obj, cell2)
print(res2)

  • In this modification, the hyperlinks are checked using Method: spreadsheets.get. In order to use this method, google-api-python-client is used with the gspread client.

  • When this script is run to your provided Spreadsheet, True and False are shown as the output values.

Reference:

Upvotes: 1

Related Questions