Search code examples
google-apps-scriptgoogle-sheetshyperlinkconcatenation

Dynamic hyperlink & Text in the same cell with a formula in google sheets

Note: consider checking the edits first if you have a similar problem

I have Link, Lable, Text and Formula as input
and the formula uses Link, Lable, Text as an input, like this
Make a copy of my example sheet.

=Function(HYPERLINK(A3,B3)," ",C3)

I want to create a custom Function to get the result like in E3 Hyperlink & Text, Google Text, after reading suggested answers i concluded there is no way to achive this result by creating a custom formula that can format the output.
the next best thing is to have a script that extract the formula parameters A3, B3 and the tailing text and use it to output the result in the next cell either automatic onedit or with menu botton.

enter image description here

I tested this script but the problem is the formula is replaced by Plain text only, see the github project google-apps-script-projects. or Make a copy of my example sheet the script is included.

Building to what @Tanaike answer, storing the parameters of the formatting in the Custom formula like this and feed it to the script to output the result in the next cell.

=CustomFunction([A3,B3],C3...)

Explanation
=CustomFunction([Hyperlink,Lable],text...)

Solution

  • I believe your goal is as follows.

    • Put a text to a cell. In this case, use a hyperlink in a part of the text.
    • You are required to achieve this using a custom function like =CustomConcatenationFunction(Hyperlink(Link,Lable),"Text1","Text2"...).

    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.

    And, in the case of a custom function like =CustomConcatenationFunction(Hyperlink(Link,Lable),"Text1","Text2"...), the arguments at the custom function side are label, "Text1" and "Text2". I think that in this case, the URL cannot be retrieved at the custom function. I think that this is also a modification point.

    So, in order to achieve your 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 and Error when running Youtube Data Service in App Scripts (js) – Daily Limit for Unauthenticated Use Exceeded.

    When Web Apps is used for achieving your 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 as follows.

    enter image description here

    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.

    • This is a simple sample script. So, please modify this for your actual situation.

    References:

    Added:

    From we need a workaround to keep the formula in place eiather in a seprate cell or in the formatted cell, I understood you perfectly. we have half of the question answered the last bit is to keep the formula extract the url, lable plain text from it and output the formatted result to a cell on the right as a workaround., how about the following sample script?

    In this sample script, the simple trigger of OnEdit is used.

    Sample script:

    const SAMPLE = _ => "Done";

function onEdit(e) {
  const customFunction = "=SAMPLE";
  const { range } = e;
  const formula = range.getFormula();
  if (!formula.includes(customFunction)) return;
  const arguments = formula.match(/\((.+)\)/);
  if (!arguments) return;
  const [link, text, allText] = arguments[1].replace(/"/g, "").split(",");
  const idx = allText.indexOf(text);
  const r = SpreadsheetApp.newRichTextValue().setText(allText).setLinkUrl(idx, idx + text.length, link).build();
  range.offset(0, 1).setRichTextValue(r);
}
    • When you use this script, please put a custom function of =SAMPLE("###URL###","sampleLink","sampleText sampleLink sampleText") to a cell. By this, the script of onEdit is automatically run by the trigger.

    Testing:

    When this script is used, the following result is obtained.

    enter image description here