It is a common requirement to link a Google Form to your spreadsheet to display user responses in your Awesome Table. In this article we will learn how to handle Google Form's new feature for Google Workspace users, the file upload ,by following a quick example.
This tip was submitted by James Pearson on his blog tech cogitation. If this article was interesting to you, check out the original article.
1. What we are going to create
In this quick article we are going to create a homework dropbox. Students will fill out a form with basic information and upload their homework file.
2. Setting up the form and spreadsheet
To create this Awesome Table app, we will create a simple Google Form attached to a Google Spreadsheet. We will create a file upload question with 5 maximum number of files.
We created 5 columns that will have the links (we called them Link 1 -> Link 5) and 5 columns that will have the files names (File 1 -> File 5).
We now use a formula to populate the 'Link' columns. In this example it will look like this :
=ARRAYFORMULA(IF(LEN(A3:A),ArrayFormula(split(E3:E,", ")),IFERROR(1/0)))
3. Fetch the files names
We first need to create a sheet called "FileList".
We are now going to use a simple script that will find the names of the files that were submitted.
From your spreadsheet go to Tools > Script Editor and paste this :
function search() { |
Set up the right folder Id (the folder where you have all of your responses for this question) and run the search function.
We now want to populate the File 1 -> File 5 columns with the corresponding file names with the following formula :
=ArrayFormula(if(isna(vlookup(F3:F,FileList!A:B,2,false)),"",vlookup(F3:F,FileList!A:B,2,false)))
This formula has to be applied to the third row of each "File" column after changing the red part to the corresponding "Link" column (in this example F G H I J ).
4. Creating an Awesome Table
We now have all the information needed to display the files in our Awesome Table app; the link and the name of the file.
We will then finish by creating a simple template looking like this :
<a target="_blank" href ="{{Link 1}}">{{File 1}}</a><br>
<a target="_blank" href ="{{Link 2}}">{{File 2}}</a><br>
<a target="_blank" href ="{{Link 3}}">{{File 3}}</a><br>
<a target="_blank" href ="{{Link 4}}">{{File 4}}</a><br>
<a target="_blank" href ="{{Link 5}}">{{File 5}}</a><br>