I2P- Summative Project- Empathy- Google Reminder PE Makeup

Here is my summative project on making a google reminder PE makeup program, that we hope will help the PE teachers in the future.

Day 1 Reflection:

Today we used the time to go to different people and ask about the different problems that occur in our school environment. We made a form in order to try to not only have the opinions of our small group, but also the general public of school. By getting out and doing the survey, it allowed us to empathise with our audience about the specific problems that we saw around school. We had two main problems that we saw, so we tried to see which one was a larger problem that affected more people. In order to do this, we produced a form that briefly outlined the two problems, and asked the user which problem affected them more.

For next class, we hope to finish up the survey, and ask the PE department what they think of our ideas, and some feedback that they have. Furthermore, we hope to start the project, and possibly start to code it.

Here is the form that we used for the empathy part of the problem.

WhatsApp Image 2016-12-13 at 2.51.42 PM

We first started with making three subcategories: medical, school, and other problems. When we first started to make these subcategories, we then just started to list the problems that we saw. After listing all the problems, we managed to narrow it down to six different problems and solutions that could be feasible. We then discussed which ones would be better, and would interest us more. However, we narrowed it down so we still had two left, in which we then could make a survey about the different ideas, and empathise with the user on which one was better.

After we got to the two ideas, we then used the time to make the form so that we could go out and ask people not only which problem affected them more, but also their take, and why the problem does affect them.


What was the problems we looked at?

The problems that we narrowed the survey to was the problem of dehydration and the mile counter.

The first was the dehydration problem:

Many people in our world today face the problem of dehydration; I myself have also faced this problem before, in which it is not the forgetfulness of drinking water, but that you simply don’t feel thirsty, when in fact are dehydrated. When this occurs, many people fail to get up and drink more water. By doing so, they are negligent to the fact that they are dehydrated. After doing some research, we realised that we lose water even by breathing, and so when we don’t put in more water into our body, we can get dehydrated. Most adults should be drinking eight eight ounce cups of water every single day. However, statistics have shown that in general people drink around 5 cups of water, which is not enough for daily input. Furthermore, in a school environment, many students forget to drink water as its not widely accessible to fill up water, and some people simply forget. Other times, people will not drink water after they exercise or after PE, and most often, people will not drink during class, which means that they do not drink water for a whole 1 hour and 30 minutes.

The second problem we noticed in school was the problem of the mile run:

The mile itself, or running it, is a process that every student in HKIS must undergo in order to get a grade for PE. The problem, therefore, is not the mile itself, but instead that of tracking it. A PE class on average will be 20-23 students, in which when all running the mile at the same time, the teachers have to keep the times and laps of each student on the top of their heads. Many choose to run it in two separate groups, where one group will run then the next so there is less kids to track, while other PE classes choose to use the pairing off method, where every student has a partner that is tracking their mile. However, even though these are great compensations, it is still not both time efficient and effective.

The third problem that we noticed:

This problem was when someone does the 12 minute swim. Sometimes, the person who is counting your laps can get distracted and miscounts how many are left. This could not only affect your performance, because you have to take count of how many laps you swam, but it could also affect your grade at the end.

What was the solutions?

Solution #1 for the dehydration problem:

Through programming we hoped that we could create a water bottle that would have different markings on it, in which at a certain time in the day when the water was still touching the mark, it would send a reminder to the phone or apparatus about the fact that the person has to drink water. Since the bottle and the computer or device is linked, it would send the reminder only if its noticed that the person hasn’t drunk water. This would help students and any person not forget to drink water. This would reduce the issue of neglecting to drink water.

Solution #2 for the dehydration problem:

Create some sort of reminder device, like a watch or other wearable device that would allow the user to see and track how much water the have had that day. By doing this, it would allow the user to track how much water they were drinking. This could be a wearable device that tracks the water, and also reminds a person to drink more water.

Solution #1 for the mile tracking problem:

Have a watch like device worn by all students that is connected to a DFID reader of some sort. This would mean that every time a person runs a lap, their watch would be like a bar code scanner, and it would be able to track their lap time, as well as the overall time. This would be stored on a database, that could be accessed by students to see how they can improve, as well as the teachers. With this database, it also allows them to track their improvement and also how many IFs they will need to do based o this .

For this solution you would need to produce a scanner, and make a watch with some sort of barcode. Also, it would need to have some sort of indicator if you pass the reader, so that it beeps or does some sort of command that would tell the user that they have just completed a lap. Furthermore, this device would help because the field is not shaped to a 400 meter field, but instead one that is slightly smaller. Hence, this will make the tracking for teachers even harder, where if there was a tracking device it would put more ease on the teachers.

What was our form (step by step)

Screen Shot 2016-12-15 at 11.47.57 AM

The first part of the form was to create awareness. This is the description at the top of the questionnaire in which explained more about the questions, and what the purpose of the form was. The first question that we made was whether the user had had any problems with PE. These problems were varied in the answers, but most had to do with the tracking of the mile and the twelve minute swims.

Screen Shot 2016-12-15 at 11.48.20 AM

The next part was mainly a mixture of empathy questions so that we could really see what people’s perspectives on the problem were. By asking questions that opened the solutions up to the audience, it allowed us to see the viewer’s opinions on these issues.

The other questions followed similar formats. From this we hoped not only to get an insight with our users, but also be able to find out how they would approach these issues.

What did people say:

We are still gathering the information from different people, but once we get a good variety, we will be able to compile what they say into a reflection.

Final reflection:

Overall, the project today went well. I think it’s safe to say that so far we have all been having a lot of fun, especially since we were able to finally solve some of the problems we saw in our world through programming. It’s also fun to see these problems, and be able to come up with creative solutions. Furthermore, going through the entire learning and design process cycle again with programming this time is enticing and interesting. Through doing the survey today, it really opened my eyes towards what people thought were problems, and what they thought the solutions could be.

Day 2 Reflection:

Today we went to see some of the PE teachers down in the middle school. We interviewed them to see what exactly we could help them with to make their lives slightly easier. Throughout this interview we learned more about what exactly they wanted to see in their program.

Idea #1:

The PE teachers wanted a program in which would be like a drag and drop method alike to powerschool, where they could easily make the grading system more user friendly. Although this idea was very interesting, it was too similar to the Powerschool. However, as we further empathised with them, the program they described was very similar to schoology. We realised that the middle school teachers were still not using schoology as a platform for grades even though at times this platform would be more efficient. We decided this was a good opportunity to start the middle school and boost the schoology use throughout. Furthermore, the platform we would have created would have been one that would have been not as efficient as a professional platform such as schoology.

Idea #2:

Since the first idea was too similar to schoology, we then asked them what other things that they hd problems with. One that was mentioned was the fact that the system for doing PE makeups wasn’t as good as it should be. Teachers found it difficult to track the makeups, and students were also forgetting that they had to makeup classes.

We decided in the end to go with the second idea, something that was realistic in the time frame given. In order to do this, we decided to utilise the google scripts, so that we could use the different google apps and link them together. By doing this, it would allow an easy way for students to fill out a form, and it would automatically send the student and the teacher an email based on the things that they filled out in the for.

We had to redo our questionnaire, and ask the teachers of their opinions. Most of the questions that we asked the PE teachers were after we listened to what they said, then adjust the questions to gather more information.

Here is the recording of the interview we did with the teachers:


Throughout the project we exercised a lot of empathy. It was because we kept gathering new data, and asked questions to different audiences that we were able to create a product that fit what the users needed. We asked questions about mile trackers, dehydration, and finally found out that the users really needed a product that was more like a reminder system. We realised that sometimes we see things that are problems in our society, but the majority of the people, or the audience we see best fit, may have more urgent products that they need created.

Design thinking sentence:

We wanted to create a reminder system that would not only send a reminder email to the student, but also notify the teacher about the makeup the students needed to do. By doing this, we hope to alleviate any sort of problems that would have been faced when asking students to do a makeup. This system would also create a google  calendar event, that would then later be shown in the calendar for both the student and the teachers.

Materials needed:

1. Google scripts

2. Google form

3. Java script

HEre is some of the planning documents we had:

final project idea project topic sentence original project idea

The process of creating the reminder system:

One of the first things that we did was set up a google document that started to plan what the reminder system would do. This allowed us to play and work towards a goal in mind on what exactly our reminder system would do. Furthermore, we had to discuss with Mr. Lin about the most effective way for us to make this reminder system. We settled on making it with google scripts, which would allow us to utilise all the different accounts and google apps.

This was our original planning document, with the start of the pseudo code as well. Since we had never used google scripts before, it was hard to make the pseudo code. However, as the process continued, we managed to understand the code, and what it would look like in python.

This was our code to send the email, and make a mail merge. By doing this, we were able to use the different items and answered in the form inside the email as the email would help to remind the student of what they need to do for their PE makeup. The email that was sent would be on form submit, which means that every time a user submits the form, the form would record it, and send an email based on what the user inputted.

We decided for the pseudo code and flow chart to write it all out by hand so we could better visualise all of the parts of our code:


Here you can see the two parts of the code and how they relate to each other. Through this code, you are able to see the parts of the code, as well as the key aspects and how they are relative to the functions of the code.This first part you can see a visual flowchart of how our code works. It explains what happens on a superficial level, in terms of how the form is filled in, and an email is being sent.


Here we show the different way that the program works. We decided to explain some of the flow chart  as a written infographic, in which people (normal people) could understand the basis of how our code works. This means that we explained what would happen on a superficial level. So the form is submitted and thus the email is sent to the people who have submitted the form. We also chose to include different ways the form could be used besides the way we used it to make a PE make up form. Some of the ways we thought of wasany situation where the people would need to send out a form to ask their colleagues to fill out, and gather information. It could also work for large competitions, or conferences, where the form could be sent out and the email would come back as a reply.


Here we showed the pseudo code simplified. The code itself is self explanatory, as the java script used in google scripts will have titles that show  exactly what that variable, or part does. In order to complete this we also used some of the indexes that were found on the google script website. This allowed us to find some of the sources, and understand the code better. Furthermore, we explained the calendar function within the code. This part was mostly theoretical, since we learned the code, but didn’t have enough time to add it, and make it function along side the email code. However, the calendar feature is one that was quite interesting, since after filling out the form, the people


Here we showed how the email merge setting worked.

function sendEmails() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var dataSheet = ss.getSheets()[0];
 var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 11);

This first part was used as a function that was called throughout the entire code, to gather the data from the sheet, and use it throughout the sheet. We used the special ways functions of google scripts in order to get the data from the sheet. One of the problems that we did face was that the code at first was set for the max rows to be limited to 4 -1= 3 rows. This meant that when we added more rows, the email function didn’t work. Once we were able to find the piece of code that controlled this, we were able to change the max rows so that it could fit how many rows we had. We then adjusted the last number, in order for the code to read more rows. The code above gets a range that contains the data in the spreadsheet.


Screen Shot 2017-01-17 at 4.08.21 PM

var templateSheet = ss.getSheets()[1];
 var emailTemplate = templateSheet.getRange("A1").getValue();

Here we showed how the email merge setting worked. The code above retrieves the string template that will be used to generate the personal emails. Now, it allows the PE teachers to draft an email, and in the email draft template, the ${“”} can be used to show which cells the word needs to be replaced by.

// Create one JavaScript object per row of data.
 objects = getRowsData(dataSheet, dataRange);
// For every row object, create a personalized email from a template and send
 // it to the appropriate person.
 for (var i = 0; i < objects.length; ++i) {
 // Get a row object
 var rowData = objects[i];
// Generate a personalized email.
 // Given a template string, replace markers (for instance ${"First Name"}) with
 // the corresponding value in a row object (for instance rowData.firstName).
 var emailText = fillInTemplateFromObject(emailTemplate, rowData);
 var emailSubject = "MS PE Makeup Reminder";
 MailApp.sendEmail(rowData.emailAddress, rowData.emailAddress, emailSubject, emailText);

This code is what sends the actual email. It was fascinating to see how with such little code could make an email send to the user. Furthermore, the function “FillInTemplateFromObject” is used to match the code and fill in the email response.

The variableData is used to retrieve the value of a data object. This is done by normalising the marker name. By doing this, and normalising the name, it allows us to replace all the markers with the string made by the form. This data could replace the object.

// Replaces markers in a template string with values define in a JavaScript data object.
// Arguments:
// - template: string containing markers, for instance ${"Column name"}
// - data: JavaScript object with values to that will replace markers. For instance
// data.columnName will replace marker ${"Column name"}
// Returns a string without markers. If no data is found to replace a marker, it is
// simply removed.
function fillInTemplateFromObject(template, data) {
 var email = template;
 // Search for all the variables to be replaced, for instance ${"Column name"}
 var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);
// Replace variables from the template with the actual values from the data object.
 // If no value is available, replace with the empty string.
 for (var i = 0; i < templateVars.length; ++i) {
 // normalizeHeader ignores ${"} so we can call it directly here.
 var variableData = data[normalizeHeader(templateVars[i])];
 email = email.replace(templateVars[i], variableData || "");
return email;

getRowsData iterates row by row in the input range and returns an array of objects. // Each object contains all the data for a given row, indexed by its normalized column name.


– sheet: the sheet object that contains the data to be processed

– range: the exact range of cells where the data is stored

– columnHeadersRowIndex: specifies the row number where the column names are stored.

// This argument is optional and it defaults to the row immediately above range; 
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
 columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
 var numColumns = range.getEndColumn() - range.getColumn() + 1;
 var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
 var headers = headersRange.getValues()[0];
 return getObjects(range.getValues(), normalizeHeaders(headers));
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
// - data: JavaScript 2d array
// - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
 var objects = [];
 for (var i = 0; i < data.length; ++i) {
 var object = {};
 var hasData = false;
 for (var j = 0; j < data[i].length; ++j) {
 var cellData = data[i][j];
 if (isCellEmpty(cellData)) {
 object[keys[j]] = cellData;
 hasData = true;
 if (hasData) {
 return objects;
// Returns an Array of normalized Strings.
// Arguments:
// - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
 var keys = [];
 for (var i = 0; i < headers.length; ++i) {
 var key = normalizeHeader(headers[i]);
 if (key.length > 0) {
 return keys;
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
 var key = "";
 var upperCase = false;
 for (var i = 0; i < header.length; ++i) {
 var letter = header[i];
 if (letter == " " && key.length > 0) {
 upperCase = true;
 if (!isAlnum(letter)) {
 if (key.length == 0 && isDigit(letter)) {
 continue; // first character must be a letter
 if (upperCase) {
 upperCase = false;
 key += letter.toUpperCase();
 } else {
 key += letter.toLowerCase();
 return key;
// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function isCellEmpty(cellData) {
 return typeof(cellData) == "string" && cellData == "";
// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
 return char >= 'A' && char <= 'Z' ||
 char >= 'a' && char <= 'z' ||
// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
 return char >= '0' && char <= '9';

Here is a video of our code working:


Furthermore, we tried to create a code that would allow us to make a calendar event based on what the form filled out. After following some of the code from different sources, we saw how it fundamentally worked as individual parts, but when we tried to put it together, it didn’t fully work according to plan.

Here is the code:

 * A special function that inserts a custom menu when the spreadsheet opens.
function onOpen() {
 var menu = [{name: 'Set up conference', functionName: 'setUpConference_'}];
 SpreadsheetApp.getActive().addMenu('Conference', menu);
 * A set-up function that uses the conference data in the spreadsheet to create
 * Google Calendar events, a Google Form, and a trigger that allows the script
 * to react to form responses.
function setUpConference_() {
 if (ScriptProperties.getProperty('calId')) {
 Browser.msgBox('Your conference is already set up. Look in Google Drive!');
 var ss = SpreadsheetApp.getActive();
 var sheet = ss.getSheetByName('Sheet 1');
 var range = sheet.getDataRange();
 var values = range.getValues();
 setUpCalendar_(values, range);
 setUpForm_(ss, values);
 * Creates a Google Calendar with events for each conference session in the
 * spreadsheet, then writes the event IDs to the spreadsheet for future use.
 * @param {String[][]} values Cell values for the spreadsheet range.
 * @param {Range} range A spreadsheet range that contains conference data.
function setUpCalendar_(values, range) {
 var cal = CalendarApp.createCalendar('Conference Calendar');
 for (var i = 1; i < values.length; i++) {
 var session = values[i];
 var title = session[0];
 var start = joinDateAndTime_(session[1], session[2]);
 var end = joinDateAndTime_(session[1], session[3]);
 var options = {location: session[4], sendInvites: true};
 var event = cal.createEvent(title, start, end, options)
 session[5] = event.getId();
// Store the ID for the Calendar, which is needed to retrieve events by ID.
 ScriptProperties.setProperty('calId', cal.getId());
 * Creates a single Date object from separate date and time cells.
 * @param {Date} date A Date object from which to extract the date.
 * @param {Date} time A Date object from which to extract the time.
 * @return {Date} A Date object representing the combined date and time.
function joinDateAndTime_(date, time) {
 date = new Date(date);
 return date;
 * Creates a Google Form that allows respondents to select which conference
 * sessions they would like to attend, grouped by date and start time.
 * @param {Spreadsheet} ss The spreadsheet that contains the conference data.
 * @param {String[][]} values Cell values for the spreadsheet range.
function setUpForm_(ss, values) {
 // Group the sessions by date and time so that they can be passed to the form.
 var schedule = {};
 for (var i = 1; i < values.length; i++) {
 var session = values[i];
 var day = session[1].toLocaleDateString();
 var time = session[2].toLocaleTimeString();
 if (!schedule[day]) {
 schedule[day] = {};
 if (!schedule[day][time]) {
 schedule[day][time] = [];
// Create the form and add a multiple-choice question for each timeslot.
 var form = FormApp.create('Conference Form');
 form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
 for (var day in schedule) {
 var header = form.addSectionHeaderItem().setTitle('Sessions for ' + day);
 for (var time in schedule[day]) {
 var item = form.addMultipleChoiceItem().setTitle(time + ' ' + day)
 * A trigger-driven function that sends out calendar invitations and a
 * personalized Google Docs itinerary after a user responds to the form.
 * @param {Object} e The event parameter for form submission to a spreadsheet;
 * see https://developers.google.com/apps-script/understanding_events
function onFormSubmit(e) {
 var user = {name: e.namedValues['Name'][0], email: e.namedValues['Email'][0]};
// Grab the session data again so that we can match it to the user's choices.
 var response = [];
 var values = SpreadsheetApp.getActive().getSheetByName('Conference Setup')
 for (var i = 1; i < values.length; i++) {
 var session = values[i];
 var title = session[0];
 var day = session[1].toLocaleDateString();
 var time = session[2].toLocaleTimeString();
 var timeslot = time + ' ' + day;
// For every selection in the response, find the matching timeslot and title
 // in the spreadsheet and add the session data to the response array.
 if (e.namedValues[timeslot] && e.namedValues[timeslot] == title) {
 sendInvites_(user, response);
 sendDoc_(user, response);
 * Add the user as a guest for every session he or she selected.
 * @param {Object} user An object that contains the user's name and email.
 * @param {String[][]} response An array of data for the user's session choices.
function sendInvites_(user, response) {
 var id = ScriptProperties.getProperty('calId');
 var cal = CalendarApp.getCalendarById(id);
 for (var i = 0; i < response.length; i++) {
 * Create and share a personalized Google Doc that shows the user's itinerary.
 * @param {Object} user An object that contains the user's name and email.
 * @param {String[][]} response An array of data for the user's session choices.
function sendDoc_(user, response) {
 var doc = DocumentApp.create('Conference Itinerary for ' + user.name)
 var body = doc.getBody();
 var table = [['Session', 'Date', 'Time', 'Location']];
 for (var i = 0; i < response.length; i++) {
 table.push([response[i][0], response[i][1].toLocaleDateString(),
 response[i][2].toLocaleTimeString(), response[i][4]]);
 body.insertParagraph(0, doc.getName())
 table = body.appendTable(table);

For this code, we coded the parts we understood first. Some of the parts were how the document would be connected towards the form ad the calendar. This meant that using the function sendDoc, this document would be sent as an Itenary to the user. Furthermore, through the event series ID, the calendar would be set up.

Throughout the code we managed to use the function of triggers. Triggers allowed us to create the program to run every single time a new event was created, or the form was created. The triggers allow for on form submit, which then allows the entire program to run throughout this time.

Here is our testing document. Since Java script on google script was completely new to us, we faced many errors. Some of which we still need to fix, but we managed to fix majority of the problems faced.

Here is the videos explaining the code, and some of the challenges we faced:


I feel like for this project teamwork was a bit of a challenge. In general, to split the work three ways is difficult, especially if we were learning a whole new language to code in. I also feel like even though I tried to spread the work pretty evenly, most of the time it was hard to give work out if  the work was all in the new programming language. I think that overall, the brainstorming part went well as a team, but the actual programming took a toll on our teamwork, and the work wasn’t spread as evenly.


Thus, throughout this project I learned not only how to make a reminder system, but more importantly learned that coding itself is not a hard thing to learn. The hardest part about coding is empathising with our audiences. Although the code at times was frustrating, and the code is still not perfect now, the end goal was less of a finished product, but a product that in the end would help our users in the best way possible. We changed our project from a mile tracker, to  balance board, to a dehydration bottle, to a gradebook and lastly to the reminder system, all to best fit our audience. It was this process where we learned and empathized most with our audience.

I came into the course thinking that the empathy part was terrible, and completely useless, but now I realise that coding is just a tool in order to help us help our audience better. I came in thinking that the empathy part was useless, and that we should have been instead using the class time to go over functions, and lists. I came throughout this project, whilst lerning a complete new language, that coding itself is one that is something you can learn if you put your heart to, but empathy is one that makes purpose behind the code made.

Furthermore, for the course review, I think it was one of the best classes that I have taken, because throughout it, I managed to see how I really wanted to pursue computer science as some part of my learning path. I realised that the coding language, and everything tech, had fascinated me throughout this course. Furthermore, it pushed me to challenge logic, and think clearer in my other subjects as well. Most importantly, I had  fun throughout the course, and all the projects we did, I was glad to challenge myself, and find the different methods to code  the same things. It was due to this class where I found another passion of mine, that I want to pursue in the future. I also think that through this course I learned to be more self motivated, because the entire point of coding is for it to be a project that you do have to search things up online and utilise your teachers around you. Hence, I think that this class was one of the best classes I have taken this semester, and I can’t wait to learn more through SDLT, as well as AP Computer Science!!


GOOGLE. “Overview of Google Apps Script  |  Apps Script  |  Google Developers.” Google Developers. Google, Github, 12 Aug. 2016. Web. 18 Jan. 2017.
Hugo Fierro. “Tutorial: Simple Mail Merge  |  Apps Script  |  Google Developers.” Google Developers. Google, 13 May 2016. Web. 18 Jan. 2017.
Stack Exchange. “Code Review Stack Exchange.” Code Review Stack Exchange. Stack Exchange, 2017. Web. 18 Jan. 2017.





Leave a Reply