Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created October 27, 2022 00:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tanaikech/4c27bf8d1948a5dfa5b0e53ce88c6d30 to your computer and use it in GitHub Desktop.
Save tanaikech/4c27bf8d1948a5dfa5b0e53ce88c6d30 to your computer and use it in GitHub Desktop.
Putting Multiple Hyperlinks to a Cell using Sheets API with Google Apps Script and Node.js

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment