Trying to add a hyperlink to Google Sheets via API

Question

I'm using the gapi.client.sheets.spreadsheets.create() method and passing in an object to create a spreadsheet with some predefined values.

I've tried various implementations and haven't yet succeeded in pulling it off. I'm referring to the docs here: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#CellData.

My Object looks something like this:

'sheets': [{
   "properties": {
      "sheetId": 1,
      "title": "Summary",
      "index": 0,
    },
    "data": [
       {
         "startRow": 0,
          "startColumn": 0,
          "rowData": [
             {
               "values": [
                 {
                   "hyperlink": "=HYPERLINK('https://google.com')"
                 }

             ]
          }
       }
     ]
  ]

Google says: "To set it, use a =HYPERLINK formula". Is this not the hyperlink formula? When the spreadsheet renders the hyperlink field is blank. (I want to display a link to a website). How can this be set?

Answer 1

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.

Answer 2

---

You can also use 'USER_ENTERED' if using batchUpdate:

sheets.spreadsheets.values.batchUpdate({
            spreadsheetId,
            valueInputOption: 'USER_ENTERED',
            requestBody: {
              data:[
                 range: *your range*
                 values:[['=HYPERLINK("google.com", "ciao")']]
               ],
            },
          })

This way you basically put there the formula and the api interprets as if the user entered the formula