Google script to automatically add predetermined file paths to Google form response

Question

I have little to, really no coding experience. Spare for a bit of HTML, CSS, and AppleScript However, I'm not sure where else to go in terms of insight on this question. Currently I am creating a Google Form for colleagues to submit work orders that will be recorded on a Google Spreadsheet.

But, I need to figure out how to write a script that will automatically add a specific file path next to files uploaded to the form.

For example, if a user uploaded a file named "061217_VideoAuthor_Slide1.mp4" instead of the Google Drive link (which is what is automatically populated with this file upload form option) I need that file name to be recorded and then it's cell needs to be changed from a Google Drive link to a specific file path (Ex. /user/editor1/template/061217_VideoAuthor_Slide1.mp4)

Is there anyone that can point me to a sort-of specific tutorial or some documentation that could help me figure this out?

Answer 1

You can run logic every time the form is submitted that looks at the values in the sheet and augments them as needed. Here's a walkthrough someone else put together:onFormSubmit

Answer 2

Your goal could be achieved by running a script every time a form is submitted, on certain schedule or by demand.

You will need to use JavaScript to get the file Id from the file URL, then get the file by using first getFileById(id) and getName() method from the Google Drive Service to get the file itself and its name respectively.

By the other side, instead or replacing the file URL I suggest you to add the file path to another column, just in case you need it later.