How can I automatically turn a column value in Google Sheets into a hyperlink based on the value?-CodePudding

I have a sheet where one column contains an ID to a Jira ticket.

I would like to automatically convert this to a link to the ticket, based on the value I enter.

E.g. I'll enter SD-1234 into the column, and I would like it to then make it into a clickable link to https://demo.atlassian.net/browse/SD-1234/, but not show the URL int he cell, but the original value I entered (SD-1234).

The column is always E if that helps. Can someone give me a head start with how to script this in Script Editor?

CodePudding user response：

For example, when the cell "E1" has SD-1234, =HYPERLINK("https://demo.atlassian.net/browse/"&E1, E1) is used, the value is SD-1234 with the hyperlink of https://demo.atlassian.net/browse/SD-1234. But in this case, the result cannot be directly shown in the cell "E1". When you want to directly convert SD-1234 in the cell "E1" to SD-1234 with the hyperlink of https://demo.atlassian.net/browse/SD-1234, how about the following sample script?

Sample script:

Please copy and paste the following script and run the function at the script editor. Before you use this, please set the sheet name.

This sample script converts from SD-1234 in the column "E" to =HYPERLINK("https://demo.atlassian.net/browse/SD-1234", "SD-1234").

function sample1() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const baseUrl = "https://demo.atlassian.net/browse/";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("E1:E"   sheet.getLastRow());
  const values = range.getValues().map(([e]) => [`=HYPERLINK("${baseUrl}${e}", "${e}")`]);
  range.setFormulas(values);
}

This sample script converts from SD-1234 in the column "E" to SD-1234 with the hyperlink of https://demo.atlassian.net/browse/SD-1234. In this case, the formula is not used.

function sample2() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const baseUrl = "https://demo.atlassian.net/browse/";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("E1:E"   sheet.getLastRow());
  const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl   e).build()]);
  range.setRichTextValues(values);
}

Note:

If your spreadsheet has the 1st header row, please modify "E1:E" sheet.getLastRow() to "E2:E" sheet.getLastRow().

References:

Added 1:

From your following comments,

Do I have to set the sheet name? I have several sheets that I always want this function to be applied to (I have currently over 20 sheets in the file). – Lee

Also, I've entered this into Script Editor to test, and what do I do from here? There's no save that I can see... and it's not running on my sheet. How can I apply this to my sheet to automatically run whenever a value is entered in the cell?

I have to apologize for my poor English skill. Unfortunately, from your question, I couldn't understand the above situations. When the above situations are reflected in the script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor and save it. And please edit the column "E" of the sheet. By this, the cell has the hyperlink with the inputted text.

function onEdit(e) {
  const sheetNames = ["Sheet1", "Sheet2",,,]; // Please set the sheet names you want to run the script.
  const column = 5; // Column E. From your question, this script run for the column "E".

  const {range} = e;
  const sheet = range.getSheet();
  const baseUrl = "https://demo.atlassian.net/browse/";
  if (!sheetNames.includes(sheet.getSheetName()) || range.columnStart != column) return;
  const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl   e).build()]);
  range.setRichTextValues(values);
}

If you want to run the script for all sheets, you can use the following script.

function onEdit(e) {
  const column = 5; // Column E. From your question, this script run for the column "E".

  const {range} = e;
  const sheet = range.getSheet();
  const baseUrl = "https://demo.atlassian.net/browse/";
  if (range.columnStart != column) return;
  const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl   e).build()]);
  range.setRichTextValues(values);
}

Added 2:

If you want to run the script to the column "E" of all sheets by one script running, you can also use the following script.

Sample script:

This sample script runs for the specific sheets.

function myFunction() {
  const sheetNames = ["Sheet1", "Sheet2",,,]; // Please set sheet names you want to run the script.

  const baseUrl = "https://demo.atlassian.net/browse/";
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
    if (!sheetNames.includes(sheet.getSheetName())) return;
    const range = sheet.getRange("E1:E"   sheet.getLastRow());
    const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl   e).build()]);
    range.setRichTextValues(values);
  });
}

This sample script runs for all sheets.

function myFunction() {
  const baseUrl = "https://demo.atlassian.net/browse/";
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
    const range = sheet.getRange("E1:E"   sheet.getLastRow());
    const values = range.getValues().map(([e]) => [SpreadsheetApp.newRichTextValue().setText(e).setLinkUrl(baseUrl   e).build()]);
    range.setRichTextValues(values);
  });
}