On Form submit; convert the input value into a hyperlink by combining domain and user input (slug), with input value as display name

Thanks for reading. I'm a novice with simple needs, looking to automate actions and make workflows more efficient, but I'm finding all the related posts and documentation difficult. I'm looking for help please as I am now just going around in circles, and can no longer remember the script which got me the closest result. School boy error...

I have a Google Form where users are required to upload images and then input What3Words locations as the answer to a couple of different questions.

I've already setup an OnFromSubmit script in the Google Sheets form responses spreadsheet to convert the multiple Google Drive URL locations in a single cell for the images into hyperlinks, but I would also like to convert the input W3W locations into hyperlinks that can then be clicked on.

I have a script that combines the domain and the user input (slug), but the URL is longer than I would like displayed on the spreadsheet, so I'm looking to take the user input e.g. "daring.lion.race", convert it to the hyperlink "https://what3words.com/daring.lion.race", BUT retain the original input as the display text "daring.lion.race".

I have tried variations of the below, using RichTextValueBuilder, but I'm struggling. Any help would be greatly appreciated.

function onEdit(e) {
W3WURL1(e);
W3WURL2(e);
}

function W3WURL1(e) {
var range = e.range;
var responseArray = e.values;
var W3Wprefix = "https://what3words.com/";

    responseArray[4] = W3Wprefix + e.values[4]; 
    range.setValues([responseArray]);
}

function W3WURL2(e) {
var range = e.range;
var responseArray = e.values;
var W3Wprefix = "https://what3words.com/";

    responseArray[7] = W3Wprefix + e.values[7]; 
    range.setValues([responseArray]);
}

I feel I am on the right lines with the below, but I'm stuck.

function onEdit(e) {
 var cell = e.range.getCell(1,5);
 var text = cell.getValue();
 const domain = "https://what3words.com/";
 var url = domain + (text);
 var richTextValueBuilder= SpreadsheetApp.newRichTextValue()
 richTextValueBuilder.setText(text);
  {
   richTextValueBuilder.setLinkUrl(url)
 }
 cell.setRichTextValue(richTextValueBuilder.build());
}

Answers for Tanaike The OnFormSubmit trigger is related to a different script running on the same spreadsheet. Due to my limited knowledge of writing scripts, I have separated the scripts into different files, although I imagine they could be combined somehow into one Script.

The onEdit and W3W functions are my attempt to run a new process on the same spreadsheet without affecting the Script that works. I have used onEdit as this is what the online examples I found and used as a basis for writing these scripts used — perhaps I should be using OnFormSubmit too? However, as it worked for my original W3W script referenced above, I have continued to use it.

Again, due of my lack of knowledge, because I need the script to convert values in 2 columns, I created W3W1 and W3W2 to target the different columns.

An example of my desired output:

Form Input Value

Col E — daring.lion.race

Col H — pretty.needed.chill

Cell value after Script has run

Col E — daring.lion.race

Col H — pretty.needed.chill

Apologies if I've misunderstood, but I hope that answers your questions and better explains what I'm looking to do.

Solution

Using the onFormSubmit function, listing multiple functions to run in order, the below script did what I wanted.

function onFormSubmit(e) {
    W3WURL1(e);
}

function W3WURL1(e) {
    var cell = e.range.getCell(1,5); //where column 5 contains the URL text value I want to retain
    var text = cell.getValue();
    const domain = "https://what3words.com/"; //the domain I want to use as a prefix to the input value in column 5 to create the URL
    var url = domain + text; //combining the domain and input value to create the URL
    var richTextValueBuilder= SpreadsheetApp.newRichTextValue()
        .setText(text)
        .setLinkUrl(url);
    cell.setRichTextValue(richTextValueBuilder.build());
}