I can normally find enough info on here to cobble together functional (not elegant!) code, but in this instance I'm struggaling and could use some help.

Background:

I have a Google form that is used to submit reprographics requests to the repro team. The form works well and dumps responses to a Google Sheet. Files are uploaded to the form and the Google Sheet creates hyperlinks to the files so they can be easily accessed. So far so good.

Problem:

When a user submits multiple files they are inserted into the Google Sheet as a comma seperated list of URLs in a single cell. These URLs are not automatically converted to Hyperlinks making it harder for the repro team to access the files.

Solution?:

I'm trying to put together a script that will (on form submission) check to see if the URL cell contains multiple URLs (I thought checking to see if there were any commas would work) then if it did convert them to hyperlinks.

In a previous project I was able to achieve somthing similar in a Google Doc with the following code:

  //Convert text to hyperlinks
  var urlRegex = 'http[s]?:\/\/[^ \n\r\v] ';
  var urlElement = body.findText(urlRegex);  

  while (urlElement != null) {    
    var urlText = urlElement.getElement().asText();

    var startOffset = urlElement.getStartOffset();
    var endOffset = urlElement.getEndOffsetInclusive();
    var urlString = urlText.getText().substring(startOffset, endOffset  1);
    
    urlText.setLinkUrl(startOffset, endOffset, urlString);

    urlElement = body.findText(urlRegex, urlElement);
  }

I was thinking that using the commas might be easier than regex for locating the URLs but I'm not sure where to start.

Example URL cell output:

https://drive.google.com/open?id=199Uj-9fvctB6H6yoIRIvvo3x3pbAmRyO, https://drive.google.com/open?id=1PADRfFT00Hckatf395MG4ZEZ_E1q6weELAdGUqKYiRA, https://drive.google.com/open?id=1gchCWaOTJ6R7bfuD4-rUY4ZeLeF95FHI

CodePudding user response：

To do this, you'll need to use the RichTextValueBuilder class to assign the hyperlinks to subsets of the cell text. One approach would be as follows:

1. Use .split() to get each of the individual urls.
2. Use SpreadsheetApp.newRichTextValue() to create a new RichTextValueBuilder.
3. Calculate the start and end offset of each url within the cell.
4. Call .setLinkUrl(startOffset, endOffset, url) on the RichTextValueBuilder.
5. Build the rich text value using .build().
6. Assign the rich text value to the cell using .setRichTextValue().

Something like this:

function onFormSubmit(e) {
  // Get a Range reference to the cell containing the urls, possibly by using the event object passed in the form submit trigger:
  var cell = e.range.getCell(1, urlColumn); // where urlColumn is the index of the column that contains the urls in question
  var text = cell.getValue();
  var richTextValueBuilder= SpreadsheetApp.newRichTextValue()
  richTextValueBuilder.setText(text);
  var urls = text.split(', ')
  urls.forEach(function(url){
    var startIndex = text.indexOf(url)
    var endIndex = startIndex   url.length;
    richTextValueBuilder.setLinkUrl(startIndex,endIndex,url)
  })
  cell.setRichTextValue(richTextValueBuilder.build())
}