如何使用Google Sheets API v4获取超链接 [英] How to Get Hyperlinks Using Google Sheets API v4

问题描述

我正在尝试使用v4 API

gsheets:

  print(worksheet.acell('A2'，value_render_option ="FORMULA").value)#吃 

cURL:

  URL =" https://sheets.googleapis.com/v4/spreadsheets/$SHEET_ID/values/%27Sheet1%27%21A2"curl -X GET"$ URL?valueRenderOption = FORMULA";-H授权:承载$ TOKEN"；# 输出{范围":"Sheet1！A1:Z1001"，"majorDimension":"ROWS"，值":[[名称"，其他"]，["sup"，单词"]，["k"，100]，["== AVERAGE(1,2,3)"，"k"]]} 

解决方案

我相信您的目标如下.

• 您要检索设置为Google Spreadsheet上的单元格的超链接.

在这种情况下，在当前阶段，可以使用"spreadsheets.get"方法检索超链接.在Sheets API中.并且，要求对此请求使用field参数.这样，可以检索超链接.样本curl命令如下.

作为示例情况，假设将URL设置为单元格"A1"."Sheet1"的在Google电子表格中.

示例curl命令:

  curl \'https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]?ranges=Sheet1!A1&fields=sheets(data(rowData(values(hyperlink)))))'\--header'授权:承载[YOUR_ACCESS_TOKEN]'\--header'接受:application/json'\-压缩 

结果:

  {表格":[{数据":[{"rowData":[{值":[{超链接":"https://example.com/"}]}]}]}]} 

对于gspread:

在gspread， requests 库的用法如下.访问令牌是从 gspread.authorize(credentials)的 credentials 中检索的.

  gc = gspread.authorize(凭证)access_token =凭据.access_tokenurl ='https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEETID]?ranges=Sheet1!A1&fields=sheets(data(rowData(values(hyperlink)))))'res = requests.get(URL，标头= {'Authorization':'Bearer'+ access_token})打印(res.json()) 

结果:

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

注意:

• 在此示例中， sheets(data(row((values(hyperlink))))用作 fields .关于此，您也可以使用 sheets .在这种情况下，其他值也将包含在响应值中.
• 在示例中， Sheet1！A1 被用作范围.因此，请根据您的实际情况修改此范围.
• 在当前阶段，当将超链接设置为单元格中一部分文本并且将多个超链接设置为单元格时，不幸的是，这些链接无法使用Sheets API直接检索.当时，作为当前解决方法，需要使用Google Apps脚本.参考请注意这一点.

参考文献:

• 方法:sheetssheets.get

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?

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"
    ]
  ]
}

解决方案

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

这篇关于如何使用Google Sheets API v4获取超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！