I need to create a form to send out to customers to fill in.
The first question is to select your team: Team1, Team2, Team3. The second question is to attach and upload a file (of any type.) After that, different questions should follow based on the answer to the first question.
The questions are in a Google spreadsheet that has tabs Team1, Team2, Team3. So based on the answer to the first question, additional questions should be read from the relevant tab and shown to the user dynamically.
I was able to design an HTML form for this and it is working fine, with responses correctly saved in a Response tab in a Google spreadsheet. Problem is that the HTML form lacks the file attachment question. I cannot use Google forms.
Here’s my existing code:
function doGet() {
return HtmlService.createHtmlOutputFromFile('Index');
}
function getQuestionsForTeam(team) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(team);
if (!sheet) {
return '';
}
var data = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues(); // Assumes questions are in the first column
var questionsHtml = data.map(function(row) {
return '<label for="' + row[0] + '">' + row[0] + ':</label><br>' +
'<input type="text" id="' + row[0] + '" name="' + row[0] + '" required><br><br>';
}).join('');
return questionsHtml;
}
function processForm(formData) {
var fileBlob = formData.file;
if (fileBlob) {
var folder = DriveApp.getFolderById("YOUR_FOLDER_ID"); // Provide your folder ID here
var file = folder.createFile(fileBlob);
var fileUrl = file.getUrl();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');
if (!sheet) {
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Responses');
var headers = Object.keys(formData).concat(['File Link']);
sheet.appendRow(headers);
}
var values = Object.values(formData);
values.push(fileUrl);
sheet.appendRow(values);
}
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
/* Apply the Google Sans Medium font */
@import url('https://fonts.googleapis.com/css2?family=Google+Sans:wght@500&display=swap');
body {
font-family: 'Google Sans', sans-serif;
background-color: #f1f3f4;
color: #202124;
margin: 0;
padding: 0;
display: flex;
justify-content: center;
align-items: center;
height: 100vh;
}
.container {
background: #ffffff;
border-radius: 8px;
box-shadow: 0 4px 8px rgba(0,0,0,0.1);
padding: 20px;
width: 100%;
max-width: 600px;
box-sizing: border-box;
height: 80vh; /* Fixed height for the container */
display: flex;
flex-direction: column;
}
h1 {
color: #4285f4;
font-size: 24px;
margin-bottom: 20px;
text-align: center;
}
label {
font-weight: 500;
color: #202124;
display: block;
margin-bottom: 8px;
}
select {
font-size: 16px;
padding: 8px;
border-radius: 4px;
border: 1px solid #ccc;
width: 100%;
box-sizing: border-box;
margin-bottom: 20px;
}
.questions {
flex: 1; /* Take up remaining space */
overflow-y: auto; /* Scrollable area if content overflows */
background: #ffffff; /* White background for the questions section */
padding: 10px; /* Ensure padding around the questions */
border-radius: 4px; /* Rounded corners */
box-shadow: 0 0 10px rgba(0,0,0,0.1); /* Light shadow for distinction */
}
.questions label {
margin-bottom: 10px;
color: #202124;
display: block;
}
.questions input {
font-size: 16px;
padding: 8px;
border-radius: 4px;
border: 1px solid #ccc;
width: calc(100% - 16px);
box-sizing: border-box;
margin-bottom: 12px; /* Add margin to separate questions */
}
input[type="submit"] {
background: #34a853;
color: #ffffff;
border: none;
padding: 12px 20px;
border-radius: 4px;
cursor: pointer;
font-size: 16px;
transition: background 0.3s ease;
width: 100%;
box-sizing: border-box;
}
input[type="submit"]:hover {
background: #2c8c3a;
}
input[type="file"] {
margin-bottom: 20px;
}
</style>
</head>
<body>
<div class="container">
<h1>Job Intake Form</h1>
<form id="myForm">
<label for="team">Select Team:</label>
<select id="team" name="team">
<option value="">--Select a team--</option>
<option value="Team1">Team1</option>
<option value="Team2">Team2</option>
<option value="Team3">Team3</option>
</select>
<div id="questions" class="questions"></div>
<label for="file">Attach a file:</label>
<input type="file" id="file" name="file" required>
<input type="submit" value="Submit">
</form>
</div>
<script>
document.getElementById('team').onchange = function() {
var team = this.value;
if (team) {
google.script.run.withSuccessHandler(function(questionsHtml) {
document.getElementById('questions').innerHTML = questionsHtml;
}).getQuestionsForTeam(team);
} else {
document.getElementById('questions').innerHTML = '';
}
};
document.getElementById('myForm').onsubmit = function(event) {
event.preventDefault();
var formData = new FormData(this);
google.script.run.withSuccessHandler(function() {
alert('Form submitted successfully!');
}).processForm(formData);
};
</script>
</body>
</html>
2
Upload attachment in App Script Web Page
Below is my custom code that will also cater your needs.
Code.js
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
function uploadFiles(formObject) {
try {
var folder = DriveApp.getFolderById("Your Folder ID");//Your Folder ID
var sheet = SpreadsheetApp.getActive().getSheetByName("Responses");//Google Sheet Name
var fileUrl = "";
var fileName = "";
//Upload file if exists and update the file url
if (formObject.myFile.length > 0) {
var blob = formObject.myFile;
var file = folder.createFile(blob);
file.setDescription("Uploaded by " + formObject.team);
fileUrl = file.getUrl();
fileName = file.getName();
} else{
fileUrl = "Record saved without a file";
}
//This is the method were we save the file to the sheet
sheet.appendRow([
formObject.team,
fileName,
fileUrl,
Utilities.formatDate(new Date(), "GMT+5:30", "yyyy-MM-dd'T'HH:mm:ss'Z'")]);// Change to your local time or remove depends on you.
// Return the URL of the saved file
return fileUrl;
} catch (error) {
return error.toString();
}
}
Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
/* Apply the Google Sans Medium font */
@import url('https://fonts.googleapis.com/css2?family=Google+Sans:wght@500&display=swap');
body {
font-family: 'Google Sans', sans-serif;
background-color: #f1f3f4;
color: #202124;
margin: 0;
padding: 0;
display: flex;
justify-content: center;
align-items: center;
height: 100vh;
}
.container {
background: #ffffff;
border-radius: 8px;
box-shadow: 0 4px 8px rgba(0,0,0,0.1);
padding: 20px;
width: 100%;
max-width: 600px;
box-sizing: border-box;
height: 80vh; /* Fixed height for the container */
display: flex;
flex-direction: column;
}
h1 {
color: #4285f4;
font-size: 24px;
margin-bottom: 20px;
text-align: center;
}
label {
font-weight: 500;
color: #202124;
display: block;
margin-bottom: 8px;
}
select {
font-size: 16px;
padding: 8px;
border-radius: 4px;
border: 1px solid #ccc;
width: 100%;
box-sizing: border-box;
margin-bottom: 20px;
}
.questions {
flex: 1; /* Take up remaining space */
overflow-y: auto; /* Scrollable area if content overflows */
background: #ffffff; /* White background for the questions section */
padding: 10px; /* Ensure padding around the questions */
border-radius: 4px; /* Rounded corners */
box-shadow: 0 0 10px rgba(0,0,0,0.1); /* Light shadow for distinction */
}
.questions label {
margin-bottom: 10px;
color: #202124;
display: block;
}
.questions input {
font-size: 16px;
padding: 8px;
border-radius: 4px;
border: 1px solid #ccc;
width: calc(100% - 16px);
box-sizing: border-box;
margin-bottom: 12px; /* Add margin to separate questions */
}
input[type="submit"] {
background: #34a853;
color: #ffffff;
border: none;
padding: 12px 20px;
border-radius: 4px;
cursor: pointer;
font-size: 16px;
transition: background 0.3s ease;
width: 100%;
box-sizing: border-box;
}
input[type="submit"]:hover {
background: #2c8c3a;
}
input[type="file"] {
margin-bottom: 20px;
}
</style>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-6">
<form id="myForm" onsubmit="handleFormSubmit(this)">
<label for="team">Select Team:</label>
<select id="team" name="team">
<option value="">--Select a team--</option>
<option value="Team1">Team1</option>
<option value="Team2">Team2</option>
<option value="Team3">Team3</option>
</select>
<div id="questions" class="questions"></div>
<label for="file">Attach a file:</label>
<input type="file" id="FormControlFile" name="myFile" required>
<button type="submit" class="btn btn-primary btn-block">Submit</button>
</form>
<br>
<div id="output"></div>
<br>
<div id="output"></div>
</div>
</div>
</div>
<script>
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener('load', preventFormSubmit);
function handleFormSubmit(formObject){
google.script.run.withSuccessHandler(updateUrl).withFailureHandler(onFailure).uploadFiles(formObject);
}
function updateUrl(url) {
var div = document.getElementById('output');
if(isValidURL(url)){
div.innerHTML = '<div class="alert alert-success" role="alert"><a href="' + url + '">File uploaded successfully!</a></div>';
document.getElementById("myForm").reset();
}else{
//Show warning message if file is not uploaded or provided
div.innerHTML = '<div class="alert alert-danger" role="alert">'+ url +'!</div>';
}
}
function onFailure(error) {
var div = document.getElementById('output');
div.innerHTML = '<div class="alert alert-danger" role="alert">'+ error.message +'!</div>';
}
function isValidURL(string) {
var res = string.match(/(http(s)?://.)?(www.)?[-a-zA-Z0-9@:%._+~#=]{2,256}.[a-z]{2,6}b([-a-zA-Z0-9@:%_+.~#?&//=]*)/g);
return (res !== null);
}
</script>
</body>
</html>
See the Screenshot below:
Reference:
- Upload files to Google Drive