This article shows:
1) How to create Google Form using standalone HTML file
2) Fetch data from Google Spreadsheet and populate Select box of the HTML Form
3) Populate second select box based on the value selected from first select box
4) Show result data from spreadsheet on form submit
Basically, we have a spreadsheet that contains exam marks obtained by students of different grades/classes.
- Our spreadsheet contains 3 sheets within it named Grade 1, Grade 2, and Grade 3.
- Each Grade sheet contains symbol number, name of students and marks obtained in different subjects like Maths, Science, English, Nepali, etc.
Here is our spreadsheet:
Create HTML file
We create a Form in a new HTML File.
- Go to
http://script.google.com - Go to
File -> New -> Html file - Type the name of the file, e.g. form.html
- Write the following code in it.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
window.onload = function() {
init(); // initial function to run on page load
};
// initializing object that will be storing all result data
var allResultData = {};
// loading image
var loading = '<img width="25px" ' +
'src="https://lh6.googleusercontent.com/PshQKOjKS5j6IEaEXZ6r7-JOFZwYOBopBt82Q-kTGdRcW6wi3SBHDAUHebb2yreOeTpGfzgs=w1280-h661"> ' +
'Loading...';
/**
* First function to run on page load
*/
function init() {
document.getElementById('progressClass').innerHTML = loading; // show loading image
/**
* This calls getAllResult() function of Code.gs
* The returned value from getAllResult() is passed to populateClass(value) function
* present in this html file
*/
google.script.run
.withSuccessHandler(populateClass)
.getAllResult();
}
/**
* Second function to run after init() function
* Populates the "Class" select box
*/
function populateClass(value) {
document.getElementById('progressClass').innerHTML = ''; // hide loading image
allResultData = value; // all result data is stored in allResultData object
var className = Object.keys(value);
var classes = '<option>Please Select</option>';
for (i = 0; i < className.length; i++) {
classes += "<option>" + className[i] + "</option>";
}
document.getElementById("class").innerHTML = classes;
}
function populateSymbolNumber(value)
{
var symbol = "<option>Please Select</option>";
var data = allResultData[value]
for (i=1; i < data.length; i++) { // start from i=1, skipping 1st row as it's heading
symbol += "<option value="+i+">" + data[i][0] + "</option>";
}
document.getElementById("symbol").innerHTML = symbol;
}
/**
* Show name of student and marks result
*/
function submitForm() {
var grade = document.getElementById("class");
var gradeValue = grade.options[grade.selectedIndex].value;
var classResult = allResultData[gradeValue];
var symbol = document.getElementById("symbol");
var symbolText = symbol.options[symbol.selectedIndex].text;
var symbolValue = symbol.options[symbol.selectedIndex].value;
var marks = '';
var headerRow = classResult[0];
//console.log(headerRow);
for (i = 1; i < headerRow.length; i++) { // start from i=1, skipping 1st column as it contains symbol number
marks += headerRow[i] + ': ' + classResult[symbolValue][i] + '<br>';
}
var result = "Symbol Number: " + symbolText + '<br>' + marks;
document.getElementById('result').innerHTML = result;
return false;
}
</script>
</head>
<body>
<div id="form">
<table cellpadding="5px">
<tr>
<td>Class</td>
<td>
<select id="class" onchange="populateSymbolNumber(this.value)">
<option value="">Please Select</option>
</select>
<span id="progressClass"></span>
</td>
</tr>
<tr>
<td>Symbol</td>
<td>
<select id="symbol">
<option value="">Please Select</option>
</select>
</td>
</tr>
<tr>
<td> </td>
<td>
<button onclick="submitForm(); return false;">Submit</button>
</td>
</tr>
</table>
</div>
<div id="result" style="border-top: 1px solid #ccc; padding: 5px"></div>
</body>
</html>
In the above form.html file:
– First Javascript function to run is init()
– This JS function calls getAllResult() function of Code.gs
– And then the returned values from getAllResult() function is passed to populateClass() function
– populateClass() function populates the ‘Class’ Select box
– populateSymbolNumber() function is called on changing option value of ‘Class’ select box
– This function populates the ‘Symbol’ select box
– submitForm() function is called when the ‘Submit’ button is clicked
– This function displays the marks obtained by the student of the particular class and with the particular symbol number
There is a Code.gs file by default in the script editor. Write the following code in it.
Note that we need doGet(e) function to publish project as WebApp. This function uses HTMLService to return HTML Output using the form.html file we created. We can also set the title of the page from here.
function doGet(e) {
return HtmlService
.createHtmlOutputFromFile('form.html')
.setTitle("Exam Result");
}
/**
* Load Spreadsheet by ID
* Get all Sheets present in that spreadsheet
* Loop through each sheet
* Create an object with sheet's name as Key of the object
* and sheets data as Value of the object
* Return the key-value paired object
*/
function getAllResult()
{
var id = '5BOWU19ZVmoJeoASaJOSejsHL8LqtkZxxxxxxxxxxxxx'; // ID of the Result spreadsheet
var ss = SpreadsheetApp.openById(id);
var sheets = ss.getSheets(); // get all the sheets in the spreadsheeet
var allSheetsData = {}; // initialize javascript object
var sheetName = '';
var sheetValues;
for (i = 0; i < sheets.length; i++) {
sheetName = sheets[i].getName();
sheetValues = sheets[i].getDataRange().getValues();
allSheetsData[sheetName] = sheetValues;
}
//Logger.log(allSheetsData);
return allSheetsData;
}
About publishing your script as web app is given here: https://developers.google.com/apps-script/guides/web
The output of the form and the result shown after submit button click is given in the below image:
Hope this helps. Thanks.