Webform Development

Object

The goal of this project is to create a Webform using JavaScript that is powered by Google Apps Script. This projects focused on finding solution to a real life problem.

Problem

Schools are organizing Science Fair events at which hundreds of projects showcased. It is troublesome to collect judge rubrics and calculate them accurately in a relatively short amount of time.

Introduction

This project provides effective solutions to judging system and arithmetic of Science Fair Event. Team is working in an agile process and full SDLC.

Methodology

Step 1 - Project Envision

Project acceptance criteria was reviewed and the team has decided about the the project timeline, the sprints, and the date of the project release.

Each part of the project is discussed in terms of scalability and usage as well as testing of the software.

This agile team consists of 5 people, one project manager, one product owner, two developers, and one tester.

Step 2 - Setting the environment

Here are the tools and languages used to develop the project

• JavaScript

• Google Apps Script

• Google Apps Script Editor

• FormApp

• SpreadsheetApp

• Google Charts Service for QR Code Generation

• SQL for Google Spreadsheets

• Google Apps APIs

• OAuth2 Verification

• Continuous Development with Google with G Suite Developer Hub

• Version Control System with Google Version Controller

• Reporting and Bug Tracking

Step 3 - Coding

After setting the planning, analysis and setting the environment requirements, using JavaScript that is powered by Google Apps Script, coding process started.

• • Stage I

There were more than 200 forms created for each student. At this stage Google Apps Script (JavaScript) is used to automate the form creation for each student.

// generates forms

for (var i in students) {

students[i][cScores] = null; //clears the scores to avoid formula conflicts

students[i][cForm] = formulas[i][cForm];

if (propertyCont) {

if (i == propertyContToken) {

Logger.log('Continuation occuring. Continue from i=%s', i);

propertyCont = null;

} else {

Logger.log('Continuation recognized. Skipping to %s from i=%s', propertyContToken, i);

continue;

}

}

dNew = new Date();

if ((dNew.getTime() - dStart.getTime())/1000 > timeout) {

Logger.log('Time out. Writing the values to the students sheet');

studentSheet.getRange(2, 1, students.length, students[0].length).setValues(students);

Logger.log('data entered');

Logger.log('Pausing the script');

properties.setProperty(CONS.PROPERTY.CONTINUE, 3);

properties.setProperty(CONS.PROPERTY.CONT_TOKEN, i);

properties.setProperty(CONS.PROPERTY.TRIGGER, setTrigger());

Logger.log(properties.getProperties());

return;

}

// skips empty rows

if (students[i][cStudent] == "") {

Logger.log('empty row skipped');

continue;

}

var newFile = tempFormFile.makeCopy(students[i][cPrjCode], folder);

Logger.log('form file copied\nName: %s', newFile.getName());

var newID = newFile.getId();

var newForm = FormApp.openById(newID);

Logger.log('newform opened\nID: %s', newForm.getId());

var items = newForm.getItems(FormApp.ItemType.SECTION_HEADER);

Logger.log('newform items received');

//replaces student info fields on the forms

for (var j in items) {

switch (items[j].getTitle()) {

case "[Student Name]":

items[j].setTitle('Student: ' + students[i][cStudent]);

Logger.log('[Student Name] replaced with %s', students[i][cStudent]);

break;

case "[Project Code]":

items[j].setTitle('Code: ' + students[i][cPrjCode]);

Logger.log('[Project Code] replaced with %s', students[i][cPrjCode]);

break;

case "[Project Name]":

items[j].setTitle('Project: ' + students[i][cProject]);

Logger.log('[Project Name] replaced with %s', students[i][cProject]);

break;

}

}

Logger.log('title replacement complete');

newForm.setDestination(FormApp.DestinationType.SPREADSHEET, responses.getId());

Logger.log('form destination set');

// links

students[i][cForm] = "=HYPERLINK(\"" + newForm.getEditUrl() + "\",\"" + newID + "\")";

Logger.log('form link entered');

try {

students[i][cShort] = newForm.shortenFormUrl(newForm.getPublishedUrl());

Logger.log('short URL created');

} catch(e) {

students[i][cShort] = newForm.getPublishedUrl();

Logger.log('short URL NOT created\n%s', e);

}

...

• • Stage II

Collecting and analyzing students data. At this stage each student form data of at least three responses from distinct judges collected. These data are collected in one single Google Spreadsheet. See the code below.

// renames new sheets

var resSheets = responses.getSheets();

resSheets[0].setName(students[i][cPrjCode]);

Logger.log('sheet name changed to %s', students[i][cPrjCode]);

resSheets[0].deleteRows(3, 90);

Logger.log('extra rows deleted');

// adds formula to the points sheet

var formula = "=IFERROR(SUM(";

for (var j in gradeItems) {

formula += "'" + students[i][cPrjCode] + "'" + "!C[" + Number(gradeItems[j] - 2) + "]:C[" + Number(gradeItems[j] - 2) + "]";

formula += (j < gradeItems.length - 1) ? "," : ")/(COUNTA('" + students[i][cPrjCode] + "'!C[-1]:C[-1])-1),0)";

}

pointsSheet.appendRow([students[i][cPrjCode]]).getRange(pointsSheet.getLastRow(), 2).setFormulaR1C1(formula);

Logger.log('formula added to points sheet');

• • Stage III

Once all the data collected, using Spreadsheet Formulas all the arithmetic and aggregations are performed to give it a final shape. See the Formula sample below

=IFERROR(ARRAY_CONSTRAIN(TRANSPOSE(SORT(FILTER(Students!$A:$F, Students!$C:$C = $A2, Students!$F:$F > 0),COLUMN(Students!$F$1),FALSE)),1,$A$1),"")

=IFERROR(ARRAY_CONSTRAIN(TRANSPOSE(SORT(FILTER(Students!$A:$F, Students!$B:$B = $A2, Students!$C:$C = $B2, Students!$F:$F > 0),COLUMN(Students!$F$1),FALSE)),1,$B$1),"")

=IFERROR(SUM('Project-01'!D:D,'Project-01'!E:E,'Project-01'!F:F,'Project-01'!G:G,'Project-01'!H:H,'Project-01'!I:I,'Project-01'!J:J,'Project-01'!K:K,'Project-01'!L:L,'Project-01'!M:M,'Project-01'!N:N,'Project-01'!O:O,'Project-01'!P:P,'Project-01'!Q:Q,'Project-01'!R:R,'Project-01'!S:S,'Project-01'!T:T,'Project-01'!V:V,'Project-01'!W:W,'Project-01'!X:X,'Project-01'!Y:Y,'Project-01'!Z:Z)/(COUNTA('Project-01'!A:A)-1),0)

• • Stage IV

Each project needs to be accessed by scanning a QR code on a smartphone or tablet. For this stage Google Charts API Service calls made for each form. This call is made with Google Apps Script (JavaScript). See the sample code below.

for (var i in students) {

Logger.log(students[i]);

// skips blank names

if (students[i][cStudent] == "") {

continue;

} else {

// Google Charts API service call

qrRow.push("=IMAGE(\"https://chart.googleapis.com/chart?cht=qr&chs=" + width + "x" + width + "&choe=UTF-8&chld=H|3&chl=" + students[i][cShort] + "\")");

infoRow.push(students[i][cStudent] + "\n" + students[i][cGrade] + "th Grade" + "\n" + students[i][cPrjCode] + "\n" + students[i][cProject]);

// adds an extra row for verticalSpacing

if (vSpacing) vSpaceRow.push(null);

hRun--; // label recorded

// checks if the row is complete; 0 complete, >0 not yet

if (hRun) {

// adds an extra column for horizontalSpacing

if (hSpacing) {

qrRow.push(null);

infoRow.push(null);

if (vSpacing) vSpaceRow.push(null);

}

} else {

// inserts row for the QR row

qrs.push(qrRow);

currentRow++;

qrSheet.setRowHeight(currentRow, width); // Squares cells for QR codes

qrSheet.getRange(currentRow, 1, 2, qrRow.length).setBorder(borders, borders, borders, borders, borders, false).setHorizontalAlignment("center").setVerticalAlignment("top").setWrap(true);

qrs.push(infoRow); // inserts the project info

currentRow++;

qrSheet.setRowHeight(currentRow, height - width); // sets the rest of the space on the label for the project info

vRun--; // labels in the row recorded

if (vRun) { // checks if the label sheet is complete; 0 complete, >0 not yet

if (vSpacing) { // adds an extra row for verticalSpacing

qrs.push(vSpaceRow); // inserts the vertical spacing row

currentRow++;

qrSheet.setRowHeight(currentRow, vSpacing); // adds extra row for vertical spacing

}

} else {

vRun = down; // resets the number of labels down

}

qrRow = []; // resets the row

infoRow = [];

vSpaceRow = [];

hRun = across; // resets the number of labels across

}

}

}

• • Stage V

Students data analyzed and finalized instantly after each update made by form submissions and reflected on a Google Slides document.

Step 4 - Testing

Testing is performed on the test scripts. Performance and scalability of the project is approved to be compliance with acceptance criteria.

Step 5- Deployment and Maintenance

This project provided an easier and faster solution to a complex data collection and analysis. It was used for 4 years in a row since it was initiated.

Bugs are fixed and maintained and new features are added. Google Continuous development and integration used throughout the software development life-cycle of this project.

Future Extensions

This project will be converted to an app on Google App store so that it will be downloadable and ready to use without requiring heavy technical skills to run and maintain.

Conclusion

This project included both agile projects and software development lifecycle that provided an effective solution that school districts face every year. The language used throughout this project is JavaScript and Google Developer Hub - Google App Scripts.

See my other projects

My Github

LinkedIn Page