The documentation for the hyperlink field is "A hyperlink this cell points to, if any. This field is read-only. (To set it, use a =HYPERLINK formula.)". You're still setting the hyperlink field (although you're attempting to set it to a formula). That won't work, because the field is read only. To set a formula, set a value in userEnteredValue.formulaValue. That will set a formula on the server, and the hyperlink field will be populated as a result.

I figured it out:

def addHyperlink(self, hyperlink, text, sheetId, rowIndex, colIndex):
    requests = []
    requests.append({
        "updateCells": {
            "rows": [
                {
                    "values": [{
                        "userEnteredValue": {
                            "formulaValue":"=HYPERLINK({},{})".format(hyperlink, text) 
                        }
                    }]
                }
            ],
            "fields": "userEnteredValue",
            "start": {
                "sheetId": sheetId,
                "rowIndex": rowIndex,
                "columnIndex": colIndex
            }
        }})
    body = {
        "requests": requests
    }
    request = self.service.spreadsheets().batchUpdate(spreadsheetId=self.spreadsheetId, body=body)
    return request.execute()

I've never interacted through the API, but it took me a hot second to figure out within native g-script. Formula was only if =hyperlink() was used, but if you add a hyperlink in the UI, it's part of the rich text class: https://developers.google.com/apps-script/reference/spreadsheet/rich-text-value Maybe that sends you in another hunting direction? Hopefully someone who has actually done this will answer....

I believe your goal as follows.

• You want to retrieve a hyperlink which was set to a cell on Google Spreadsheet.

In this case, in the current stage, the hyperlink can be retrieved using the method of "spreadsheets.get" in Sheets API. And, it is required to use the field parameter for this request. By this, the hyperlink can be retrieved. The sample curl command is as follows.

As the sample situation, it supposes that the URL is set to a cell "A1" of "Sheet1" in Google Spreadsheet.

Sample curl command:

curl \
  'https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]?ranges=Sheet1!A1&fields=sheets(data(rowData(values(hyperlink))))' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --compressed

Result:

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "hyperlink": "https://example.com/"
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

For gspread:

At gspread, requests library is used as follows. The access token is retrieved from credentials of gspread.authorize(credentials).

gc = gspread.authorize(credentials)
access_token = credentials.access_token
url = 'https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]?ranges=Sheet1!A1&fields=sheets(data(rowData(values(hyperlink))))'
res = requests.get(url, headers={'Authorization': 'Bearer ' + access_token})
print(res.json())

Result:

{'sheets': [{'data': [{'rowData': [{'values': [{'hyperlink': 'https://example.com/'}]}]}]}]}

Note:

• In this sample, sheets(data(rowData(values(hyperlink)))) is used as fields. About this, you can also use sheets. In this case, other values are included in the response values.
• At the sample, Sheet1!A1 is used as the range. So please modify this range for your actual situation.
• In the current stage, when a hyperlinks is set to a part of texts in a cell and the several hyperlinks are set to a cell, unfortunately, those cannot be directly retrieved using Sheets API. At that time, as the current workaround, it is required to use Google Apps Script. Ref Please be careful this.

References:

• Method: spreadsheets.get

• You want to create new Spreadsheet including the cell values using the method of spreadsheets.create in Sheets API.
  • From your request body, I imaged like above.
• You want to put the cell values which have the hyperlink when new Spreadsheet is created.
• You want to know the request body for achieving above.

If my understanding is correct, how about this answer?

Modification points:

When the official document is seen, hyperlink is the read-only like below.

hyperlink: A hyperlink this cell points to, if any. This field is read-only. (To set it, use a =HYPERLINK formula in the userEnteredValue.formulaValue field.)

So in your case, it is required to use the following property.

"userEnteredValue": {"formulaValue": "=HYPERLINK(\"wwww.google.com\",\"google\")"},

Modified request body:

When your request body is modified, it becomes as follows.

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "userEnteredValue": {
                    "formulaValue": "=HYPERLINK(\"wwww.google.com\",\"google\")"
                  }
                },
              ]
            }
          ]
        }
      ],
      "properties": {
        "title": "my testing",
        "sheetId": 0
      }
    }
  ],
  "properties": {
    "title": "My Test"
  }
}

• In this request body, the new Spreadsheet which has the hyperlink at the cell "A1" is created.

References:

• Method: spreadsheets.create
• CellData

If I misunderstood your question and this was not the result you want, I apologize.

How about using sheets.spreadsheets.get? This sample script supposes that service of your script has already been able to be used for spreadsheets().values().get().

Sample script :

spreadsheetId = '### Spreadsheet ID ###'
range = ['sheet1!A1:A1'] # This is a sample.
result = service.spreadsheets().get(
    spreadsheetId=spreadsheetId,
    ranges=range,
    fields="sheets/data/rowData/values/hyperlink"
).execute()

If this was not useful for you, I'm sorry.

I have not found a way to read link contents, but you can write links with "=HYPERLINK(url, text)", if you use the USER_ENTERED valueInputOption when updating a sheet.

About your request body, I would like to propose the following modification.

From:

{
  "values": [
    [
      "=HYPERLINK("#gid=MY_GID&range=A1", "test3")"
    ]
  ]
}

To:

{
  "values": [
    [
      "=HYPERLINK(\"#gid=MY_GID&range=A1\", \"test3\")"
    ]
  ]
}

Hello @karolIf the hyperlink is entered into Google Sheets using the hyperlink formula, you can get the value of the URL by setting the Render Value As parameter in the Google Sheets V4 reader as “Formula”.You can then process the formula to extract the URL.Based on your test spreadsheet, the URLs are simply pasted as hyperlinks. To read the hyperlinks, you can call the Google Sheets API using an HTTPCaller and specify you want the hyperlink values. You can use the same web connection as the Google Sheets reader for authentication in the HTTPCaller.The hyperlink information is then returned as JSON. Process the JSON to get the hyperlink URL and merge the hyperlink information with the feature from the Google Sheets reader based on the row number.I have attached an example workspace made in 2023 demonstrating this approach and I hope it helps.