Home » Google AppScript20 March 2017

Google AppScript: Create Form using HTML, Publish Form as WebApp & Show data from Spreadsheet

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.

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.

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.

Google AppScript

Get New Post by Email

Find me on

FacebookTwitterGoogle+LinkedInRSS Feed