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?