Get real value from a cell pointing to another cell

Question

I'm trying to create a script to handle with a google spreadsheet, but sometimes, it happens that when I use the command val = worksheet.cell(1, 2).value, the actual value is like =M7, therefore, it doesn't have a real value for the script. Is there any possible workaround to get the content from the "M7" cell and copy it to the cell I'm reading?

A sample sheet is here.

Sample code:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds']
spreadsheet_id = '1sfltKJ1-EBlXJReDSmb5KiqeNSXbHuuLH2d2O1_Qfyc'
credentials = ServiceAccountCredentials.from_json_keyfile_name('C:\credentials.json', scope)
client = gspread.authorize(credentials)
wb = client.open_by_key(spreadsheet_id)

ws = None
sheets = [s for s in wb.worksheets() if s.id == 0]
if sheets:
    ws = sheets[0]

values = ws.get_all_values(value_render_option='FORMULA')

print(values)

# if you run this code, you will see that for gspread, the stirng 'original_value' is only in the B2 cell.
# When the gspread module reads the D2 cell, it shows actually the formula, that is =B2
# Also, I need to used the param 'value_render_option='FORMULA'', because sometimes there is a hyperlink formula in the cell,
# so sometimes I need to read the real content of the cell.

# I hope you understand the formula

Resuming, if you run the above code, you will get this list:

[['', '', '', ''], ['', 'original_value', '', '=B2']]

But the expected output should be:

[['', '', '', ''], ['', 'original_value', '', 'original_value']]

UPDATE:

I have created an workaround for this issue, so if this is useful for anyone, the code will be below:

def column2number(col):
    num = 0
    for c in col:
        if c in string.ascii_letters:
            num = num * 26 + (ord(c.upper()) - ord('A')) + 1
    return num

values = ws.get_all_values(value_render_option='FORMULA')
for i in range(len(values)):
    for j in range(len(values[i])):
        # this will check a combination of any number of uppercase letters followed by any number of numbers, representing the column and the line, respectively.
        p = re.compile('^=[A-Z]+[0-9]+')
        try:
            # if the variable p is not null, we will retrieve the matched regex from the string, split it, and get only the letters.
            r = p.match(str(values[i][j])).group()
            output = re.split('(\d+)',r[1:])
            # this function 'column2number' will convert the column letters to its position numerically
            output[0] = column2number(output[0]) - 1
            output[1] = int(output[1]) - 1
            output[2] = i
            output.append(j)
            
            # the output is filled, respectivelly by: referenced column, referenced line, line where the content will be placed, column where the content will be placed

            values[i][j] = values[output[1]][output[0]]
        except AttributeError:
            pass
print(values)

This workaround will just replace the referencing formulas like =M7 by the existing content in the M7 cell.

Answer 1

I believe your goal as follows.

• You want to retrieve the cell values using gspread.
• You don't want to retrieve the formulas.

Modification points:

• When I saw the document of get_all_values, when value_render_option='FORMULA' is used, the formula is retrieved when the cell has the formula. So when you want to retrieve the cell values which is the formula, please use value_render_option='FORMATTED_VALUE' and value_render_option='UNFORMATTED_VALUE'.
  • When I saw the script of gspread, it seems that FORMATTED_VALUE is the default value. Ref

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

values = ws.get_all_values(value_render_option='FORMULA')

To:

values = ws.get_all_values()

or

values = ws.get_all_values(value_render_option='UNFORMATTED_VALUE')

Reference:

• get_all_values

Added:

From your replying, I understood you want to retrieve the formula of hyperlink when the formula of hyperlink is put to the cell. For this, I would like to propose the following script.

Sample script:

values = ws.get_all_values()
formulas = ws.get_all_values(value_render_option='FORMULA')
for i, r in enumerate(formulas):
    for j, c in enumerate(r):
        if '=hyperlink' in c:
            values[i][j] = c
print(values)

• In this sample script, the values and formulas are retrieved and when =hyperlink is included in the formula, the formula is retrieved. I thought that this method might be a simple.