How to replace user input to User Input + URL while making it a hyper link with Google Apps Scripts/Google Sheets?

Question

[Goal] I've created a dedicated column within a table of many rows for users to input certain IDs into the cell. I want to be able to combine those IDs with a specific fixed URL, that would allow us to go to the specific site depending on each ID.

[Issue] I've created the following code, which I understand it would allow use to have set the URL, however it's not combining the URL with the user provided IDs, so it's not allowing me to complete the URL. In other words, in order to complete the URL, we need the fixed URL (example: example.com) and the user provided ID (example: 12345) and make it clickable (example: example.com/12345).

[Question] Is there a way to make the following steps happen ONLY when the enters an ID? If not, I want to have the cell blank.

1. User sets an ID to a cell (example: 12345).
2. Google Apps Script somehow copies the ID, then replaces it with a fixed display text with the ID (example: Go to link for 12345).
3. Google Apps Script somehow uses the fixed URL (example.com/) and the ID (example: 12345), and combines them (example: example.com/12345).
4. The user will not see the entire URL, however they'll see the display text (Go to link for 12345), and if they click the link, it'll take them to the page (example.com/12345) specific to the ID.

// Global veriables
var app = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Status");

function onEdit(e) {
  var setLink = app.getRange("M16:M");
  setLink.setValue('=HYPERLINK("https://example.com/","Go to link for ")');
}

Answer 1

Explanation:

Your goal is to replace a value in the range M16:M with a hyperlink that contains that value upon edit on that cell.

• Firstly, you need to take advantage of the event object.

• Upon editing a cell in the range M16:M the following script will replace that value with the hyperlink that will contain the previous value as the id of the url.

• If you delete the value in M16:M the url will automatically be removed.

Solution:

function onEdit(e) {  
  const ss = e.source;
  const as = ss.getActiveSheet();
  const rng = e.range;
  const row = rng.getRow();
  const col = rng.getColumn();     
  if (as.getName()=='Status' && col == 13 && row > 15){      
      if (rng.getValue()!=''){
          rng.setValue(`=HYPERLINK("https://example.com/${rng.getValue()}","Go to link for ${rng.getValue()}")`)
      }
      else{
          rng.clearContent();
      }      
  }      
}

Be careful:

• This is an onEdit trigger which will automatically be activated when you edit a cell in range M16:M of sheet Status. You must not execute it manually, it does not work like that. See here how a simple onEdit trigger works.

Illustration: