Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API

GasTips / Spreadsheet / workaround
Google Apps Script / Spreadsheet

Gists

This is a current workaround for putting the multiple hyperlinks to a cell using Sheets API.

Description

Recently, at Spreadsheet service, the multiple hyperlinks got to be able to be put to a cell. Ref In this case, it can be achieved using RichTextValue. On the other hand, at Sheets API, in the current stage, there are no methods for directly putting the multiple hyperlinks to a cell. And also, such methods have not been added. I believe that such methods will be added in the future update. I think that when this is implemented, it might be added to TextFormatRun.

So, in this post, I would like to introduce for putting the multiple hyperlinks using Sheets API as a current workaround.

Workaround

In this workaround, PasteDataRequest of the method batchUpdate in Sheets API is used. PasteDataRequest can parse the HTML data and put the values to the cells. When I used this, I noticed that when one hyperlink is included in a table, the pasted value on the sheet has the hyperlink. By this, I thought that when the multiple hyperlinks are used in the HTML data, those might be able to be pasted to a cell. When I tested this, it was found that this can be achieved.

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.

Sample script

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  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>`;
  const resource = {
    requests: [
      {
        pasteData: {
          html: true,
          data: html,
          coordinate: {
            sheetId: sheet.getSheetId(),
          },
        },
      },
    ],
  };
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
}

Result

Limitation

  • In the current stage, it seems that PasteDataRequest cannot use the rich text for a part of text in a cell as shown in above images. By this, when 2 texts with 2 hyperlinks are put to a cell using PasteDataRequest, each text has the hyperlink. But the rich text for the hyperlink cannot be seen. On the other hand, when only the text with the hyperlink is put to a cell, the rich text for the hyperlink is set. I would like to expect to resolve this for the future update.

Note

  • As another workaround, I think that it is to use Web Apps as an API. When Web Apps created by Google Apps Script is used, the spreadsheet service can be used. By this, the rich text can be also used.

 Share!