How can I format a part of a Google sheets cell value using googleapis?

Question

I'm using https://www.npmjs.com/package/googleapis to insert data. One of my cells should contain multiple links, let's say

https://foo
https://bar

This is easy to do manually and I can query manually antered data and see the formatting. With this:

const res = await sheets.spreadsheets.get({
    spreadsheetId,
    ranges: ['Intro!A1'],
    includeGridData: true
})

I can see the data formatting in res; screenshot below of the data from debugging. Circled in red is the value of the cell, underlined in blue is the first link defined in textFormatRuns.

This is what I tried to test formatting. This successfully writes the stringValue, but the textFormatRuns does nothing

{
  userEnteredValue: {
    stringValue: "ABCDEFGHIJKLMNOP\nabcdefghijklmnop"
  },
  textFormatRuns: [
    {
      format: { bold: true, underline: true, link: { uri: "https://ddg.gg" } }
    },
    {
      format:{}, startIndex: 9,
    }
  ]
}

Answer 1

In the end the solution was that my UpdateCellsRequest contained this part: fields: 'userEnteredValue' which was somehow blocking the formatting update. This is very strange to me.

Here is a final proof of concept request that actually works to insert text into a cell and format parts of the cell (typescript):

async function writeTest(auth) {
  const sheets = google.sheets({ version: 'v4', auth })
  const spreadsheetId = 'xxxx' // SPREADSHEET ID
  const sheetId = 111111111111 // WORKSHEET ID

  const requests: any = [
    {
      updateCells: {
        start: { sheetId, rowIndex: 0, columnIndex: 0 },
        rows: [
          {
            values: [
              {
                userEnteredValue: {
                  stringValue: "the quick brown fox\nlorem ipsum dolor"
                },
                textFormatRuns: [
                  { startIndex: 3, format: { bold: true } },
                  { startIndex: 6, format: {} },
                  { startIndex: 9, format: { bold: true, link: { uri: "https://ddg.gg" } } },
                  { startIndex: 12, format: {} }
                ]
              }
            ],
          },
        ],
        fields: '*' // This was the critical thing that was wrong!
      },
    }
  ]

  await sheets.spreadsheets.batchUpdate({
    spreadsheetId: spreadsheetId,
    requestBody: { requests }
  }, {})
}

authorize() // This provides authentication/authorization
  .then(writeTest)

Here is the result; note the bold formatting and clickable link