Putting Multiple Hyperlinks to a Cell using Sheets API with Google Apps Script and Node.js

GasTips / Spreadsheet
Google Apps Script / Spreadsheet

Gists

I have submitted a report of “Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API” before. At that time, there are no direct methods for setting multiple hyperlinks to the part of text in a cell. But, recently, textFormatRuns was added to Sheets API. By this, multiple hyperlinks got to be able to be set to the part of text in a cell. In this report, I would like to introduce a sample script for this.

Sample script 1: Google Apps Script

The sample script is as follows. As the sample, Sheets API is used with Advanced Google services with Google Apps Script. So when you test this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");

  // 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 resource = {
    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: sheet.getSheetId(),
            startRowIndex: 0,
            startColumnIndex: 0,
          },
          fields: "userEnteredValue,textFormatRuns",
        },
      },
    ],
  };
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
}

Sample script 2: googleapis for Node.js

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));

Result

When this script is run, the following result is obtained. You can see the hyperlink style in the cell “A1”.

References

 Share!