Step 1: Ideate and Empathize

Before we could start making anything, we had to ideate and come up with ideas. Olivia, Weilyn, and I did this by trying to empathize with the students and teachers of the HKIS community and seeing if there were any problems that we could help solve through programming. We compiled them into one big mind map.

Step 2: Questionnaire

Next, we wanted to test our ideas to see if they were actually real problems that people could relate to. We did this by creating a questionnaire and asking people all around the school to give their input. The two main ideas that we narrowed it down to was having an reminder app to remind people to drink water and to create a mile tracker. Here were some of the results:

Step 3: Investigate

After we received student input on our problems, we decided that it would also be a good idea if we received input from some of the teachers as well (specifically for the mile tracker issue). For the water dehydration issue, we discussed it among us and decided that it wasn’t a problem that a large crowd of students were dealing with. Once we talked to the MS PE teachers, we realized that this wasn’t really an issue to them since they already had their own tracking system that they were happy with. However, they did express their concerns in that they wanted to have either a new click-based grading system which is more convenient to use, or a system that would help track any make up sessions students would have to complete. After discussing it, we decided that we should try and solve the make up session problem as Schoology, a platform that fits their needs, already exists and is being used by other HKIS teachers.

Here is our one-sentence summary statement of our project: We want to create a program that automatically sends reminders to students in order to help MS PE teachers keep track of make ups that need to be completed. 

Step 4: Planning/Pre-code stage

Now that we had a solid problem that we were gonna work to tackle, we needed to start coding as soon as possible so that we could finish the project in time. However, I2P has taught us that psuedo code is actually a very important development stage for programmers. Instead of jumping straight into coding, we decided to plan out what we were going to do by writing some psuedo code. Here is the psuedo code we wrote: http://bit.ly/2iNujas

Equipment needed:

-Laptop

-Google spreadsheets

Here are also some of the physical planning documents and flowcharts we created:

Step 5: Research

After Mr. Lin’s demonstration on how to send emails through Google Sheets using Google Script, our group thought that it would be best if we used this method as well. The only problem that we faced by using this tactic is that Google Scripts uses Javascript, while we have spent the whole semester learning only Python. However, this obstacle didn’t phase us. We used online resources such as Google Script tutorial articles and YouTube videos to figure out how to get the code to work (here is one of the videos we used, but didn’t end up incorporating into our project: https://www.youtube.com/watch?v=xGMIttLXpo4)

Other sources that we used during our development phase:

https://developers.google.com/apps-script/articles/mail_merge

https://developers.google.com/apps-script/articles/sending_emails

Step 6: CODING

Now that we have got all of the preparation work done, it is finally time to actually start coding. We took all of the code and knowledge gathered from our research and put it together to create a working program. Here is a video explaining (voice over by me) explaining how our code works: https://www.youtube.com/watch?v=-Z_wU02z6ds&feature=youtu.be

Here is the actual code:

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

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

// 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);
}
}
// 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;
}

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.

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)) {
continue;
}
object[keys[j]] = cellData;
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}
function normalizeHeaders(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) {
var key = normalizeHeader(headers[i]);
if (key.length > 0) {
keys.push(key);
}
}
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.

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;
continue;
}
if (!isAlnum(letter)) {
continue;
}
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;
}
function isCellEmpty(cellData) {
return typeof(cellData) == “string” && cellData == “”;
}
function isAlnum(char) {
return char >= ‘A’ && char <= ‘Z’ ||
char >= ‘a’ && char <= ‘z’ ||
isDigit(char);
}

function isDigit(char) {
return char >= ‘0’ && char <= ‘9’;
}

Testing Document:

Throughout the coding process, we did come across many parts of the script that we needed to test to see if it works. I2P has taught us that in order to keep track of what has been tested and what needs to be tested, we created a testing document to log what we tested and when.

Teamwork:

Although this project was supposed to be a team effort, I still believe that there were some people who pulled more weight than others. For this project, we were all supposed to work together and contribute equally to the final project. Splitting the work evenly into three parts proved to be a difficult task especially when it came to a brand new coding language. Because this was a new language that we had to learn, Weilyn and I both had to put in some extra effort to watch videos and read articles on how to learn Javascript. Even though we both had to put in extra effort, I believe that it was needed since without the effort, we wouldn’t have been able to finish the project on time.

Even though Weilyn and I did put in a lot of effort into our project, I do believe that there could’ve been more that Olivia could’ve done. Despite the fact that Olivia did contribute towards the project such as the infographic, I don’t believe that she directly contributed to the development and production of the code itself. I feel like the project would’ve run more smoothly if we worked better as a team with all 3 of us working together. I think what ended up being the problem was that Weilyn and I went ahead with all the research without asking Olivia to research as well and that Olivia wasn’t especially proactive in contributing towards the code. I believe that there is more collaborative effort we could’ve put in, which is something all of us need to improve on n the future.

Final Thoughts:

Overall, I really enjoyed this semester course. Through this semester, I was able to go from having no knowledge in programming at all to having a substantial amount knowledge that I could use and apply to solving certain problems. One of the main takeaways I had from this course was improving my design thinking skills. I believe that spending the first few months working on design thinking was important because it taught me a skill that helped us program and we could use outside of this class.

I really enjoyed this class because it helped me improve my problem solving skills as well as teach me how to code a computer program.

Summary Video:

Instead of reading all of this information, we have also produced a video that also explains the process that we had to go through in order to achieve the product of this project: https://www.youtube.com/watch?v=wpUolsy1ffg&feature=youtu.be