How to write HYPERLINK formula through Google Sheets API (hashtag for #gid is not accepted)

Question

I'm trying to write create a hyperlink to a cell in another sheet in a Google sheets document through API. To test this, I found that I can try it out on Google here:

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update via "try this method".

However, when I create the request, I get a wanring/error that an unexpected character is encountered in the hyperlink formula, specifically the hashtag when referring to a certain gid.

So this is how I THINK the http request should look like, but throws the error:

PUT https://sheets.googleapis.com/v4/spreadsheets/MY_SHEET_ID/values/UPDATED_CELL?valueInputOption=USER_ENTERED&key=[YOUR_API_KEY] HTTP/1.1

Authorization: Bearer [YOUR_ACCESS_TOKEN]
Accept: application/json
Content-Type: application/json

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

And this is how I at least get the API call run technically, but then the formula doesn't work in the real gsheets, because I'm using single quotes instead of double quotes:

PUT https://sheets.googleapis.com/v4/spreadsheets/MY_SHEET_ID/values/UPDATED_CELL?valueInputOption=USER_ENTERED&key=[YOUR_API_KEY] HTTP/1.1

Authorization: Bearer [YOUR_ACCESS_TOKEN]
Accept: application/json
Content-Type: application/json

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

So how would I need to specify the hyperlink formula so that Google will accept it?

BTW, trying to use this solution (i.e. using single quotation marks at the outer side of the formula) doesn't work either: https://stackoverflow.com/a/71354041/2725773

Answer 1

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