Using RichTextValues with Custom Function on Google Spreadsheet

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

In this post, I would like to introduce the method for using RichTextValue with a custom function of Google Apps Script.

This sample is for this thread in Stackoverflow.

In this thread, the OP’s goal is as follows.

  • Put a text to a cell. In this case, use a hyperlink in a part of the text.
  • This is required to be achieved using a custom function.

In the current stage, in order to reflect the hyperlink in a part of the text, it is required to use setRichTextValue of Google Apps Script. In this case, this method cannot be used with the custom function. This is the current specification.

So, in order to achieve the OP’s goal, it is required to use a workaround. In this post, I would like to introduce the workaround. This workaround uses Web Apps. When Web Apps is used, the methods which cannot be used with a custom function can be used with a custom function. This can be seen at this report.

When Web Apps is used for achieving OP’s goal, it becomes as follows.

Usage:

1. Prepare Google Spreadsheet.

Please create a Google Spreadsheet.

2. Prepare sample script.

Please open the script editor of Spreadsheet and copy and paste the following sample script.

function doGet(e) {
  const { range, sheetName, link, text, allText } = e.parameter;
  const idx = allText.indexOf(text);
  const r = SpreadsheetApp.newRichTextValue()
    .setText(allText)
    .setLinkUrl(idx, idx + text.length, link)
    .build();
  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(range)
    .setRichTextValue(r);
  return ContentService.createTextOutput();
}

// This is used as the custom function.
function SAMPLE(link, text, allText) {
  const webAppsUrl = "https://script.google.com/macros/s/###/exec"; // Please set the URL of Web Apps after you set the Web Apps.
  const range = SpreadsheetApp.getActiveRange();
  UrlFetchApp.fetch(
    `${webAppsUrl}?range=${range.getA1Notation()}&sheetName=${range
      .getSheet()
      .getSheetName()}&link=${link}&text=${text}&allText=${allText}`
  );
}
  • Here, webAppsUrl is required to be replaced with your Web Apps URL. Web Apps is deployed in the following flow.

3. Deploy Web Apps.

The detailed information can be seen at the official document.

Please set this using the new IDE of the script editor.

  1. On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.
  2. Please click “Select type” -> “Web App”.
  3. Please input the information about the Web App in the fields under “Deployment configuration”.
  4. Please select “Me” for “Execute as”.
  5. Please select “Anyone” for “Who has access”.
  6. Please click “Deploy” button.
  7. Copy the URL of the Web App. It’s like https://script.google.com/macros/s/###/exec, and replace webAppsUrl in the above sample script.
  8. Reflect the latest script to the Web Appps. Because the script of Web Apps is changed. This is an important point.

4. Testing:

In order to test the above sample, please put a custom function like =SAMPLE("###URL###","sampleLink","sampleText sampleLink sampleText"). By this, sampleLink of sampleText sampleLink sampleText has the hyperlink. You can see the demonstration at the top image.

Note

  • In this case, the inputted custom function is overwritten by the RichTextValue. Because in the current stage, the RichTextValue cannot be used in a custom function.

References

 Share!