How to Get Hyperlinks Using Google Sheets API v4

user_4728007 0
Viewed 1205

I am trying to access a hyperlink using the v4 API ValueRenderOption param (valueRenderOption=FORMULA). I have tried with both python's gsheets and cURL. Either way, I cannot seem to get the formula that will show a hyperlink. Below is a screenshot of the spreadsheet; note the cell containing the value 2 has its formula shown, as expected, but that the hyperlink to https://example.com is shown as "sup". Is there a new way that we are supposed to access the contents of hyperlinks?

enter image description here

gsheets:

print(worksheet.acell('A2', value_render_option="FORMULA").value)
# sup

cURL:

URL="https://sheets.googleapis.com/v4/spreadsheets/$SHEET_ID/values/%27Sheet1%27%21A2"
curl -X GET "$URL?valueRenderOption=FORMULA" -H "Authorization: Bearer $TOKEN"

# output
{
  "range": "Sheet1!A1:Z1001",
  "majorDimension": "ROWS",
  "values": [
    [
      "Name",
      "Other"
    ],
    [
      "sup",
      "word"
    ],
    [
      "k",
      100
    ],
    [
      "=AVERAGE(1,2,3)",
      "k"
    ]
  ]
}
google-sheets-api google-sheets
1 Answers

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:

user_71086530
Related