Use hyperlink text as number with gspread/google sheets

Question

When inserting a link with text in sheets with gspread I insert the following string with valueInputOption 'USER_ENTERED' into the target cell:

'=HYPERLINK("' + some_link + '","' + str(some_number) + '")'

Because of the python string concatenation, the number has to be cast to a string. In the sheet the number is then handled as a string and not a number and no mathematical functions can be used on this cell.

Is there a way to create a link with gspread whose text can be handled as a number by google sheets?

Answer 1

In your situation, how about the following modification?

From:

'=HYPERLINK("' + some_link + '","' + str(some_number) + '")'

To:

'=HYPERLINK("' + some_link + '",' + str(some_number) + ')'

• By this modification, the double quotes for str(some_number) are removed. By this, when some_number is 123, the formula of =HYPERLINK("some_link",123) is put to a cell.
• When I check this cell using spreadsheet.get method, I confirmed that 123 is numberValue at effectiveValue. When your script is used, 123 is stringValue.