0

Background:

I have a Google Form that asks clients to upload files. The form is attached to a Google Sheet where I want the files to be accessible to processing staff. A problem occurs when multiple files are uploaded. The Google sheet seems to not be able to create hyperlinks for the all the individual files.

There is a question about this already that has been resolved here: https://stackoverflow.com/questions/70799922/google-app-script-to-convert-a-comma-seperated-list-of-urls-to-hyperlinks-in-she?answertab=modifieddesc#tab-top

The answer poses a custom function using Google App Script:

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())
}

My question is on how to use this as I have not had success.

First, when I run this script in the editor, I receive this:

TypeError: Cannot read property 'range' of undefined onFormSubmit @ Code.gs:3

I'm pretty unfamiliar with java and have a working knowledge of python and am sure alot is missed in the details of how this script works.

As I understand it this error occurs if the script is run in the app script test function due to the formula being activated on an event which I believe is the submission of the form from the client.

I'm assuming that the custom function is to be set in a blank column that references the original results of the Google form links and converts them into the rich text links.

I've tested the script on the sheet on a blank column referencing the link information in the form results column and submitted a new form with the same result:

Link addresses appear as text and do not pull the documents.

Can anyone give me some clarity?

  • I thought that your situation might be related to this thread. https://stackoverflow.com/q/16089041 – Tanaike Nov 29 '22 at 00:22

0 Answers0