How to append cell with multiple links to a Google Sheet

I know how to append a cell with an image link to a Google Sheet. I also know how to append multiple cells with an image link in each to a Google Sheet.

async function addDataToSheet(data) {
    const auth = await authorize();
    const sheets = google.sheets({ version: 'v4', auth });
    const res = await sheets.spreadsheets.values.append({
        spreadsheetId: spreadsheetId, 
        range: 'Sheet1!A1:F',
        // valueInputOption: 'RAW',
        valueInputOption: 'USER_ENTERED',
        resource: {
            values: [data]

        }
    });
}

And, there is a way to add multiple image links in a single cell using batchUpdate.

async function addDataToSheetHtml() {
    const auth = await authorize();
    const sheets = google.sheets({ version: 'v4', auth });

    const html = `
<table>
  <tr>
    <td>sample <a href="https://www.google.com/">link1</a> sample <a href="https://tanaikech.github.io/">link2</a> sample</td>
    <td><a href="https://www.google.com/">link1</a></td>
  </tr>
</table>`;

    await sheets.spreadsheets.values.batchUpdate({
        spreadsheetId: spreadsheetId,
        requests: [{
            pasteData: {
                html: true,
                data: html,
                coordinate: {
                    sheetId: sheets.getSheetId(),  // Modified
                    rowIndex: sheets.getLastRow(),  // Modified
                    columnIndex: 0,
                },
            },
        },],
    })
}

But it appears that batchUpdate can't append to a sheet.

That wouldn't be a problem if there was a way to find that last cell on a sheet, but there seems not to be such a function.

Is there a way to append a cell that contains multiple clickable links to a Google Sheet?

Solution

I believe your goal is as follows.

You want to append the value including multiple hyperlinks to the specific sheet using Sheets API with googleapis for Node.js.

Modification points:

About And, there is a way to add multiple image links in a single cell using batchUpdate., when I saw your showing script, I thought that sheets.spreadsheets.values.batchUpdate cannot be used pasteData. In this case, it is required to use sheets.spreadsheets.batchUpdate(). And, your request body is required to be modified, and const sheets = google.sheets({ version: 'v4', auth }); has no methods of getSheetId() and getLastRow().
In your situation, first, it is required to retrieve the row number of the last row of the sheet.

When these points are reflected in a sample script, how about the following sample script?

Sample script:

const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const sheetName = "Sheet1"; // Please set your Sheet name.
const sheetId = "0"; // Please set your Sheet ID.

const sheets = google.sheets({ version: "v4", auth: auth });

// Retrieve last row number of the sheet.
const res1 = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: sheetName,
});
const lastRow = res1.data.values.length;

// HTML table is appended to the sheet using pasteData.
const html = `
  <table>
      <tr>
      <td>sample <a href="https://www.google.com/">link1</a> sample <a href="https://tanaikech.github.io/">link2</a> sample</td>
      <td><a href="https://www.google.com/">link1</a></td>
      </tr>
  </table>`;
await sheets.spreadsheets.batchUpdate({
    spreadsheetId: spreadsheetId,
    resource: {
      requests: [
        {
          pasteData: {
            html: true,
            data: html,
            coordinate: {
              sheetId: sheetId,
              rowIndex: lastRow,
              columnIndex: 0,
            },
          },
        },
      ],
    },
  }).catch(({ errors }) => console.log(errors));

References:

Added:

When an HTML table is put to the cells using PasteDataRequest, unfortunately, it seems that the hyperlink style cannot be set. So, in this case, as another method, I would like to propose using textFormatRuns. When this is used, the sample script is as follows.

Sample script:

const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const sheetName = "Sheet1"; // Please set your Sheet name.
const sheetId = "0"; // Please set your Sheet ID.

const sheets = google.sheets({ version: "v4", auth: auth });

// Retrieve last row number of the sheet.
const res1 = await sheets.spreadsheets.values.get({spreadsheetId, range: sheetName});
const lastRow = res1.data.values ? res1.data.values.length : 0;

// I modified the below script.

const spreadsheetId = "###"; // Please set Spreadsheet ID.
const sheetId = "0"; // Please set Sheet ID.

// Please set the text and the hyperlinks.
const obj = [
  [
    {
      stringValue: "sample link1 sample link2 sample",
      links: [
        { value: "link1", uri: "https://www.google.com/" },
        { value: "link2", uri: "https://tanaikech.github.io/" },
      ],
    },
    {
      stringValue: "link1",
      links: [{ value: "link1", uri: "https://www.google.com/" }],
    },
  ],
];

const requests = [
  {
    updateCells: {
      rows: obj.map((row) => ({
        values: row.map(({ stringValue, links }) => ({
          userEnteredValue: { stringValue },
          textFormatRuns: links.reduce((ar, { value, uri }) => {
            const temp = stringValue.indexOf(value);
            if (temp != -1) {
              ar.push({ startIndex: temp, format: { link: { uri } } });
              if (stringValue.length != temp + value.length) {
                ar.push({ startIndex: temp + value.length });
              }
            }
            return ar;
          }, []),
        })),
      })),
      range: { sheetId: sheetId, startRowIndex: 0, startColumnIndex: 0 },
      fields: "userEnteredValue,textFormatRuns",
    },
  },
];

const sheets = google.sheets({ version: "v4", auth });
await sheets.spreadsheets.batchUpdate({ spreadsheetId, resource: { requests } }).catch(({ errors }) => console.log(errors));

When this script is run, the values of "sample link1 sample link2 sample" and "link1" are put to the columns "A" and "B". And, 2 hyperlinks are included in the column "A" and 1 hyperlink is included in the column "B". And, all hyperlinks have the hyperlink style. The sample output situation is as follows.