CODEWITHSUNDEEP
apigoogle-sheets
AR. Arif
AR. Arif

Reputation: 63

How to get the link data with pretty text in it from Google Sheet API

I'm getting data from google sheet in CSV format. But when the sheet has a Hyperlink in its cell, like =Hyperlink("URL", "pretty text"), the google sheet only gives me the pretty text not the link, So my question is how can I get both links & pretty text from google sheet.

Thanks for helping me

Upvotes: 0

Views: 892

Answers (1)

ale13
ale13

Reputation: 6062

You will have to make use of the valueRenderOption parameter when making the spreadsheets.values.get API call.

Assuming your cell looks like this:

sheet with HYPERLINK formula

You can retrieve the following values using different values for the valueRenderOption parameter as this one will determine how values are rendered in the output.

1. If valueRenderOption is FORMATTED_VALUE (which is the default value when making the API call):

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/A1?valueRenderOption=FORMATTED_VALUE

Then the response will look like this:

{
  "range": "Sheet1!A1",
  "majorDimension": "ROWS",
  "values": [
    [
      "pretty text"
    ]
  ]
}

2. If valueRenderOption is UNFORMATTED_VALUE:

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/A1?valueRenderOption=UNFORMATTED_VALUE

Then the response will look like this:

{
  "range": "Sheet1!A1",
  "majorDimension": "ROWS",
  "values": [
    [
      "pretty text"
    ]
  ]
}

3. If valueRenderOption is FORMULA:

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/A1?valueRenderOption=FORMULA

Then the response will look like this:

{
  "range": "Sheet1!A1",
  "majorDimension": "ROWS",
  "values": [
    [
      "=HYPERLINK(\"URL\", \"pretty text\")"
    ]
  ]
}

Therefore, depending on the exact result you want to achieve, it would be best to use the FORMULA parameter as this will retrieve the formula with its link and value as well. This might require some string processing if you simply want the link and its value.

Reference

Upvotes: 2

Related Questions