Google Sheet hyperlink from one cell to another in Python

Question

Given a specific cell from one Google Sheet that contains a hyperlink to another cell in another Google Sheet - how to get this link properly and how to get the value of the linked cell?

So far I've got this:

table_entities = gc.values().get(spreadsheetId=excel_id, range='XYZ').execute()['values']
table_entities_synthesis = gc.get(spreadsheetId=excel_id, ranges='XYZ', fields="sheets/data/rowData/values/hyperlink").execute()
synthesis_rangeid_list = []
for hyperlink in table_entities_synthesis.get('sheets')[0].get('data')[0].get('rowData'):
    if hyperlink.get('values', ''):
        hyperlink = hyperlink.get('values')[1].get('hyperlink')
        synthesis_rangeid_list.append(hyperlink)
        request = gc.spreadsheets().values().get(spreadsheetId=ABC, range=hyperlink, 
        valueRenderOption=value_render_option, dateTimeRenderOption=date_time_render_option)
        response = request.execute()

But this approach gives me a list of range ids like this one: '#rangeid=737563017' and I have no idea how to access the data from that other sheet with this (for sure it should not be the way I've done it above because gc.spreadsheets().values().get() method accepts rather sth like 'B2' when talking about cell ranges and not the id) + another problem is that this solution will work only if every row of my first sheet will have a hyperlink to that second sheet (I want to connect one value to another in the end) - so if one raw will not contain it then I loose the whole order and I have a huge problem.

The ideal situation would be to get the hyperlink of a specific cell in a loop and then connect it to the value of the hyperlinked cell from another sheet.

Answer 1

I've spent a whole day trying to work this one out so to anyone struggling with this issue -> I'll write down all of my observations and the final solution.

1. My first and biggest mistake was that I was creating the hyperlink by adding it to the original cell an then CLICKING on the cell I wanted to link it to. This way resulted in a link that consisted only of the rangeid with which I didn't know what to do with cause I needed the A1 notation -> SO you need to copy the path to the cell you're interested in linking and add it to the hyperlink (this way you'll have a link with BOTH rangeid + A1 notation)
2. Then you iterate over the results from the service.spreadsheets().get() request and you use regex from @ale13 answer to get the exact cell locations in A1 notation.

hyperlink_entities = gc.get(spreadsheetId=excel_id, ranges='sample_range!A1:P', fields="sheets/data/rowData/values/hyperlink", includeGridData='true').execute()
cell_range = []
for hyperlink in hyperlink_entities.get('sheets')[0].get('data')[0].get('rowData'):
    if hyperlink.get('values', ''):
        cell_address = (re.search('[^0-9a-zA-Z_$:](\$?[a-zA-Z]{1,2}\$?[1-9][0-9]*)(?![0-9a-zA-Z_:])', hyperlink.get('values')[1].get('hyperlink'))).group(1)
        print(cell_address)
        cell_range.append(cell_address)

Answer 2

To get the hyperlink from a cell and you should try this:

SPREADSHEET_ID = 'ID_OF_THE_SS'
RANGE_NAME = 'SS_RANGE'
FIELDS = 'sheets/data/rowData/values/hyperlink'
request = service.spreadsheets().get(spreadsheetId = SPREADSHEET_ID, ranges = RANGE_NAME, includeGridData = 'true', fields = FIELDS)
response = request.execute()

As for retrieving the range for the cell referenced in the hyperlink, you just need to use the following regex formula:

response = str(response)
results = re.search('[^0-9a-zA-Z_$:](\$?[a-zA-Z]{1,2}\$?[1-9][0-9]*)(?![0-9a-zA-Z_:])', response)
print(results.group(1))

Note

You will have to import the regular expressions module in Python by using import re in order to use regex. Also, please note that you might have to adjust the code to the ranges you have as this was made for one cell only.

Reference

• Sheets API v4 Method: spreadsheets.get;

• Regular Expression To Extract Valid Cell References From A Spreadsheet Formula;

• Regular Expressions Module Python.