May 12, 2015 A Simple WebApp using Google Apps Scripts

As an evangelist, I travel to universities and educate students about the platform. As a part of my workshop, I usually conduct a closed SRM (Single Round Match) for the students and declare the winners of the contest.

The Problem

Sometimes the students want to know their rank and score long after the conclusion of the event. However, previous SRM results are unloaded as soon as new contests are planned. I wanted to store the results offline and allow the students to access the results as needed, and I needed a quick and easy solution. I found that Google Apps Script does the job nicely, and here I will attempt to describe what I did.

The Solution

The objective is, a member should be able to type in their handle name in a web page, and retrieve their rank and score. The following Google spreadsheet represents the data with a student’s rank and score.

Steps

1. Click Tools on Google Spreadsheet and click Script Editor. Script Editor allows us to write javascript to manipulate data in the Spreadsheet.
2. Select Script as Web App, this allows us to generate HTML for our page.
3. Remove the auto – generated text.
4. Save the Project

Details

The data in the spreadsheet is accessible as a 2 Dimensional array, (ie the first cell in the spreadsheet is in position 0,0). We can use this information to find relevant data from the array. This is a bound script, hence can access the current spreadsheet and all its data.

For pulling out data from the spreadsheet, I used Spreadsheet Service. For fetching the data, I had to first reach out to the spreadsheet, and then the range of cells I wanted to retrieve. I created a new function in script file, which retrieves the user record based on the inputHandle.

[snippet caption="Accessing Spreadsheet"]
function retrieveUserRecord(inputHandle) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];
  var dataArray = sheet.getDataRange().getValues();
  for(var i = 0; i < dataArray.length;i++){
    if(dataArray[i][0]) {
      if(dataArray[i][0].toLowerCase() == inputHandle.toLowerCase()) {
        return dataArray[i];
      }
    }
  }
[/snippet]

The next task was to have an HTML page which serves as the Form View for the participant to enter their handle and retrieve rank and score.

Here we create a new HTML file called index.html with the markup.

The doGet() function is invoked by the Google Apps Script, and allows us to return back an HTML page. Add this file in the Script Editor itself.

[snippet caption="Appending Html"]
function doGet() {
  return HtmlService.createHtmlOutputFromFile('index')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
[/snippet]

Now we have the HTML ready and server side function ready to return the correct result. Now we need a way for the HTML page to talk back to the script function. I used google.script.run to call the functions in the script file. It is an asynchronous client-side JavaScript API available in HTML-service pages that can call server-side Apps Script functions.

How to call retrieveUser function using JS.

[snippet caption="Calling Js function retrieveRecord in Script File "]
$(document).ready(function(){
     $("#btnSearch").on("click",function(){
     google.script.run.withSuccessHandler(onSuccess).retrieveRecord($("#txtHandle").val());
        return false;
     });
[/snippet]

The final step is to deploy the app, but before that we must authorize the script to access our spreadsheet. To authorize the app, click on Run and select doGet function. A window will popup seeking to authorize the scripts from accessing Google Account.

Now we are ready to deploy the app. Click on publish, and deploy as web app.

Finally, we add some bootstrap magic and the final page is ready. We have a working website and all in less than 30 min.

The ability to serve web pages from spreadsheet, is a very powerful tool. We can create a small (or large!) app and get value very quickly!

Thanks!