4

I am using the Python module gspread to try and extract a link inside an href tag from a cell of a Google spreadsheet. I have tried the following, and noted their problems:

  1. worksheet.acell ('B5').value: Gets cell text, not link inside href tag.
  2. worksheet.acell ('B5', value_render_option='FORMULA').value: Gets cell text, not link inside href tag.
  3. worksheet.acell('B5').input_value: Returned none. Also, deprecated.

How can I correctly get a link inside href tags from a cell in a Google spreadsheet?

zerecees
  • 697
  • 4
  • 13
Chaithanya Krishna
  • 1,406
  • 1
  • 8
  • 13
  • Hey @Tanaike, you have answered related stuff. But it wasn't a direct answer for my question. But it is useful for some others who will look at this post. – Chaithanya Krishna Jul 16 '20 at 08:32
  • @Tanaike regarding my solution. I have found another column with the exact direct link in the column instead of a hyperlink. It's not a solution to this question, it's just an alternate for my problem. – Chaithanya Krishna Jul 16 '20 at 08:34
  • Thank you for replying. I could understand about it. So I would like to undelete my answer. When that was useful for users, I'm glad. – Tanaike Jul 16 '20 at 08:35
  • 1
    I thank you for your contribution and participation :) – Chaithanya Krishna Jul 16 '20 at 08:37
  • @Tanaike can you please start a bounty on my question. If possible! – Chaithanya Krishna Jul 16 '20 at 08:42
  • I apologize for my poor English skill. I cannot understand about your replying. – Tanaike Jul 16 '20 at 08:43
  • You can see a link 'start a bounty' below my question. If possible can you please start the bounty. So that this question will get highlighted for few days. It costs you some reputation. But you have lot of it. So, it wouldn't be a problem for you. – Chaithanya Krishna Jul 16 '20 at 08:47
  • I cannot understand about it. I apologize for this. – Tanaike Jul 16 '20 at 08:48

1 Answers1

3

In order to retrieve a hyperlink of a cell, it is required to use the method of spreadsheets.get in Sheets API using the fields. Unfortunately, I couldn't find this method in gspread. So in this answer, I would like to propose the following flow.

  1. Retrieve the access token.
    • I think that in this case, the script of your authorization for gspread can be used.
  2. Request to the method of spreadsheets.get in Sheets API using requests module.
  3. Retrieve the hyperlink.

Sample script:

import requests
import urllib.parse


spreadsheetId = "###"  # Please set the Spreadsheet ID.
cellRange = "Sheet1!A1"  # Please set the range with A1Notation. In this case, the hyperlink of the cell "A1" of "Sheet1" is retrieved.

client = gspread.authorize(credentials)  # I think that this is also used in your script for using gsperad.

# 1. Retrieve the access token.
access_token = client.auth.token

# 2. Request to the method of spreadsheets.get in Sheets API using `requests` module.
fields = "sheets(data(rowData(values(hyperlink))))"
url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "?ranges=" + urllib.parse.quote(cellRange) + "&fields=" + urllib.parse.quote(fields)
res = requests.get(url, headers={"Authorization": "Bearer " + access_token})

# 3. Retrieve the hyperlink.
obj = res.json()
link = obj["sheets"][0]['data'][0]['rowData'][0]['values'][0]['hyperlink']
print(link)
  • This sample script retrieves the hyperlink in the cell "A1" on "Sheet1".

Note:

  • Recently, Google Spreadsheet got to be able to have multiple hyperlinks in a cell. But in the current stage, unfortunately, it seems that those links cannot be retrieved using Sheets API. I believe that this will be resolved in the future update.
  • So, in this sample script, when one hyperlink is set in one cell, this script can retrieve the hyperlink. So please be careful this.

Reference:

10 Rep
  • 2,217
  • 7
  • 19
  • 33
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks for the response. As of my requirement, I have chosen some other place to get url. – Chaithanya Krishna Jul 08 '20 at 12:48
  • @Chaithanya Krishna Thank you for replying. I have to apologize for my poor English skill. I cannot understand about `As of my requirement, I have chosen some other place to get url.`. Can I ask you about it? If I misunderstood your question, I have to also apologize for this. – Tanaike Jul 08 '20 at 23:09
  • I said that I have found another source where I can get the URL instead of getting it from the hyperlinked text. So, for now, no issues. Thank you for your response. – Chaithanya Krishna Jul 16 '20 at 08:20