I2P-Simon Kye-Final Project Progress Report

Day 1

Following the design thinking process, my first step to take was empathising. To simply put it, the goal  of empathising here is to learn about the audience and the context of the situation or problem. The problem here is I want to make a automated bidding system using google forms. The audience are bidders who wish for simple, online, and easy-to-use bidding system. Usually to empathise, it is best to interview the actual audience or experience it yourself to gain a deeper understanding. Unfortunately, I do not bid, or know anyone who bids on things, so I am unable to completely finish this step, but I can do the next best thing, ask anyone about the problem. As getting opinions other than yourself is important to making a good product. I asked three of my friends (Sean, Daniel, Jennifer) and my parents what I should do. Here are the things I got:

Few questions, a good explanation on each item on why the bidder should bid on this item, quick, simple, should give option to email the person to notify the person if anything happens (If item is about to be sold, if bid goes higher).

Personally, I agree with all of these points and I’m glad I asked other people for their opinion as they gave me ideas I previously didn’t have.

Now, I have to define the problem. I need to create a more detailed plan than just ‘making a bidding system on google form’. I need to sharpen the question. After I defined the question, the question looked more like ‘making a google form that would be to make a automated bidding system on google form that is easy and simple to use for the user by showing who currently has the highest bids and the amount that they bid on.’ But this is the minimum of my final product. Right now, although this is a good goal to strive for, this isn’t the goal I’m reaching for right now. As said in the video, it is important to take small steps when trying to accomplish your goal and I need to define my goals a bit more realistic with my current knowledge. Keeping this in mind, I came up with the goal to ‘Create a google spreadsheet that can determine what the highest number from a list of numbers and display that onto the form’. I also made a flowchart to help me with planning. This is currently the flowchart for the most simple version of my final product.

For the third step, I need to begin ideating, on how to make a auction with google scripts, but before I can do that, I need to do more research on how a auction would work in javascript and how I can implement it in google forms.






These websites gave examples on what a google script code should look like and all the commands that were available (which was overwhelming at first to process all of them)

Something I realised while working on this is that google scripts isn’t too beginner friendly and most explanations in forums already assume you know most things. I looked through for commands that I needed to help me with this project and found some that were useful, but even when it came to that, it was very confusing how I would implement it into the code. I’m met with videos that are around 20 minutes to 1 hour long videos that are just slightly related to my topic. Something I also learned while doing this is that I need to learn HTML as seen from the custom google forms I could find such as this video: https://www.youtube.com/watch?v=38H7WpsTD0M The biggest challenge I came across were the lack of effective resources that were available especially when it came to making forms, something I also found hard was finding a command that took from a spreadsheet, got the value, and somehow transfer that variable over to the form side of things. Currently the closest function I could find was which was from the same video listed above, and I have little to no clue what it means

var values = SpreadsheetApp.getActive().getSheetByName('Name')

It seems as if for every question I try to answer, more pop up, and although don’t fully understand how this works, I know if I keep trying I will find a solution. This could also be just a lack of researching abilities on my part for finding obscure things.

Basically, today I realised the difficulty of the task I’ve given myself. Although I haven’t learned much in terms of google scripts that is useful to me, I’ve learned little bits and pieces that I hope will help me in the future. Failure is a part of learning to tackle problems, and I will persevere and try my best to keep insightful reports about my experience with this project and create a good final product. It is also important to keep in mind the accomplishments I’ve made with properly empathising and defining the problem. My plan for tomorrow is research more and have a working draft of the project.

Day 2

After more researching, I was still unable to find a way to get google forms to show results of the poll within the form itself, so I went back to defining and thought of a new plan with the resources I was given. Instead of showing something on the form, depending on what the person submits in the form, it will send out a email to that person on if their bid was accepted or not and what the actual maximum bid is. I used the resource given from Ms. Mok, to come up with this code. This video helped me with the basics as it was very beginner friendly and I came up with this code. I’ve made much more progress and have created my first draft. You will have to click on it and zoom in quite a bit. This is basically as simple as the code gets. What this basically does is that it checks the spreadsheet for values, each command explained in the photo below and sends a different email depending on how high the bid was compared to the maximum bid. I tested and was successful.

Now that I was done with my first draft, I was back to ideating. What am I missing here? It was pretty easy to think of as it is currently very bare bones. I redefined my problem as the emails that were sent out always sent out a new email. Even to bidders who have already received their first email. This is very annoying so I found this on the forums https://productforums.google.com/forum/#!topic/docs/3Quykp2Gzjk and used it to make sure no more duplicate emails were sent out. Something that also should happen is that it should notify the user if someone else bids higher than them and is overtaken. So I began writing more and came up with the solution on my own. The previous comments have been deleted and will be deleted everytime I make change to avoid clutter. After testing, it was successful and I moved onto my next step

Now that I was done with that, I needed to ideate once again. The new issue I wanted to tackle was to make the form more realistic and reject any bids made after the end of the auction. This problem probably took the longest besides from starting the project itself. I tried using date.parse, or slicing the string of the date so that it would appear as 20180617 YYYYMMDD and then I would do the if function with the end date. But none of these functions were available in google scripts for some reason and I had to get creative. I looked for more videos and came with this. https://www.youtube.com/watch?v=ZcNmur6xiX4 There is a way to make dates into numerical numbers by formatting and I began google searching how to put that into google scripts. The result was not easy to find but I met this website found in a forum. http://www.blackcj.com/blog/2015/05/18/cell-number-formatting-with-google-apps-script/ This helped me implement the date system into my project so that I could I also made sure that noone could send in anything other than numbers in the google form when bidding so that it doesn’t give bugs. All of this came out successful after testing although I had to test the most amount of times with this part of the challenge.

Back to ideating again! Quite late into this project I realised I had a problem. The function did not run automatically when a new data was submitted. It was instead had to be run manually by me in the script editor. So I look to fix that. The solution to this was quite simple. After google searching, I found this and luckily had to write no extra code for this. https://productforums.google.com/forum/#!topic/docs/_skuJIuf-eU I tested once again and it worked as expected.

There was one more thing I wanted to do before finishing it off. To notify the winner of the bidder when the auction ends. I found the solution to this in google’s official function listings of a clock trigger builder and made this (1st image). https://developers.google.com/apps-script/reference/script/clock-trigger-builder. But for this it needs to have a whole new function for when  the auction ends. This was basically just a bunch of copy and pasting with taking multiple variables, functions, and if statements out as they were unnecessary (2nd image). I tested this function and it works! I also made sure that people could only submit emails in the what is your email box to avoid bugs.

This was the final code I was left with at the end of day 2.


Today I managed to accomplish so many things. All I needed was a place for me to start. It was just so hard to get started when you had no clue what anything meant and what to search on google. My searches initially looked like “How to insert google spreadhseet cells into google forms google scripts” but by the end I knew exactly where I was going. Thanks to the video sent by Ms. Mok, which was very beginner friendly, I had a direction to go and I was able to make much more progress than I expected of myself and I’m very proud of myself for sticking through and finishing this. I had to learn so many new functions that were specific to google scripts and javascripts functions that we learned in the past didn’t work sometimes and I had to find a way around that. I got better at researching and learned much more google scripts functions.

Day 3

It is the third day and back to ideating! I have so far completed the core mechanics of a auction but there were just a few pieces missing that I could add to make this project even better. I first polished up my code to send more professional looking emails. I also added the location of where to receive the prize. I’ll just copy and paste the new code here as it is very long to take a photo of.

“Dear “+currentName+”,”+”\n\n”+”Thank you for participating in the wonton google form auction!”+”\n”+”Your bid of $”+maximumBid+” has been accepted!”+”\n”+”If you know anyone else that is interested in participating in the auction, please let them know!”+”\n\n”+”By Simon”)

“Dear “+currentName+”,”+”\n\n”+”You have previously participated in our wonton google form auction, and someone has overtaken your bid of $”+currentBid+”! The new maximum bid is now $”+maximumBid+”\n”+”Come back and bid higher to claim your tasty wontons!”+”\n\n”+”By Simon”

“Dear “+currentName+”,”+”\n\n”+”Thank you for participating in the wonton google form auction.”+”\n”+”Your bid of $”+currentBid+” has been rejected!”+”\n”+”The current maximum bid is $”+maximumBid+”. If you want those tasty wontons, bid higher than the maximum bid!”+”\n\n”+”By Simon”)

“Dear “+currentName+”,”+”\n\n”+”Thank you for your interest in participating in the google form auction, but we are past July 1st, 2018, and the auction is now closed. Sorry!”+”\n\n”+”By Simon”)

“Dear “+currentName+”,”+”\n\n”+”Thank you for participating in the google form auction. The auction has now ended and you have won your prize with your bid of $”+currentBid+”!”+”\n”+”Come pick it up at Hong Kong International School at the start of school. Thank you!”+”\n\n”+”By Simon”)

After this, I began brainstorming again. This was just a simple auctioning system. For people to want to use it, it should be something a bit extra to make it stand out, as the end goal should technically be to make as many people as possible. For this to happen, we should also be as inclusive as possible by not limiting it to Hong Kong. So I tried implementing currency conversion and this is what I got. Once again I did it all on my own. A unmentioned change that isn’t in the comments are that the maximumBid is now taking the maximum of the 7th column. It also turned out that the script for creating triggers didn’t work and there was a option to set up a trigger on the day the auction ends. Here is the new code with the comments

After the three days of work on my final project, here is the result



As you could see I couldn’t implement as much as I did on the other day as I had little time available, that is why a big proportion of the project is based around the second day as I dedicated a entire day to it.

I absolutely loved this challenge and it challenged me way more than the python challenge would’ve and I’m so glad I didn’t change my final project. This gave me a sense of accomplishment and everytime I figured out something that seemed impossible, it felt amazing. The hardest part about this challenge was getting started. I didn’t know what anything meant and diving in blind to a new coding language was very overwhelming at first. But I overcame these challenges and constantly used the design thinking process to help me along the way. In the end, I was able to make a bidding system that was able to accomplish all of the things listed above and was like a real basic bidding system and even managed to add in a currency converter. I originally thought my final product would be very flawed and miss many features but I managed to include all the core features a auction would have. Although it wasn’t like my original plan of showing the highest bid in the google form, I came up with a way around it to make the auction still work.

EDIT: There is a bit of inconsistency. For some reason it doesn’t send the email all the time. If it doesn’t work try again but with a different bid number.

Leave a Reply