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>
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: