I2P-Simon Kye-Final Project Code Rundown

I honestly think I do a better job talking about the code in the report than the video if the video didn’t make sense.

I would just recommend using the form itself and looking at what happens to the google spreadsheet but here is the code if you want to implement it yourself. Remember to change the if statement to the numerical version of the date just as mentioned in the video. Make sure the questions in the form are formatted in the exact same way so that it ends up on the same columns. Make sure to change the value with the maximumBid to =MAX(H2:H) in the google spreadsheet itself. I also changed the code so that although it says July 1st in the auction, I increased the deadline so that you can test out all the features instead of just a email saying “The auction is closed”

Here is the spreadsheet, form and a video of the code working and the actual code itself.

https://docs.google.com/spreadsheets/d/1QND1MQa6TuAUAPJbmso0oR1HE2Oc6rFI1M4WPTGjGb4/edit?usp=sharing

https://docs.google.com/forms/d/e/1FAIpQLSf7pGRet8Gc8skH5b-0PDU1AaVsZL7UifftLDXayOE-Lxl3jA/viewform

function sendEmail() {
  for(var a=0; a<2; a++){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var column = ss.getRange("A2:B");
  column.setNumberFormat("00000");
  for (var i=2 ; i<=lr; i++){
    var currentEmail = ss.getRange(i,3).getValue();
    var currentName = ss.getRange(i,2).getValue();
    var currentBid = ss.getRange(i,4).getValue();
    var emailSent = ss.getRange(i,7).getValue();
    var currentDate = ss.getRange(i,1).getValue();
    var currency = ss.getRange(i,5).getValue();
    var hkdBid = ss.getRange(i,8);
    if (hkdBid.isBlank()){
      if(currency=='USD'){
        ss.getRange(i,8).setValue(currentBid*7.85);
    }
      if(currency=='HKD'){
        ss.getRange(i,8).setValue(currentBid*1);
    }
      if(currency=='WON'){
        ss.getRange(i,8).setValue(currentBid*0.007);
    }
    }
    var emailBid = ss.getRange(i,8).getValue();
    var maximumBid = ss.getRange(2,6).getValue();
    if (currentDate<=43282){
      if (emailBid==maximumBid){
        if (emailSent != 2){
          ss.getRange(i,7).setValue(2);
          MailApp.sendEmail(currentEmail, currentName+" your bid has been accepted!", "Dear "+currentName+","+"\n\n"+"Thank you for participating in the wonton google form auction!"+"\n"+"Your bid of "+currency+"$"+currentBid+" 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")
        }
    }
      if (emailBid<maximumBid){
        if (emailSent==2){
          ss.getRange(i,7).setValue(1);
          MailApp.sendEmail(currentEmail, currentName+" someone else has taken the max bid!", "Dear "+currentName+","+"\n\n"+"You have previously participated in our wonton google form auction, and someone has overtaken your bid of "+currency+"$"+currentBid+"! The new maximum bid is now HKD$"+maximumBid+"\n"+"Come back and bid higher to claim your tasty wontons!"+"\n\n"+"By Simon")
      }
       if (emailSent!=2){
         if (emailSent!=1){
           ss.getRange(i,7).setValue(1);
           MailApp.sendEmail(currentEmail, currentName+" your bid has been rejected!", "Dear "+currentName+","+"\n\n"+"Thank you for participating in the wonton google form auction."+"\n"+"Your bid of $"+currency+"$"+currentBid+" has been rejected!"+"\n"+"The current maximum bid is HKD$"+maximumBid+". If you want those tasty wontons, bid higher than the maximum bid!"+"\n\n"+"By Simon")
        }
      }
      }
  }
    if (currentDate>43282){
      ss.getRange(i,4).setValue(0);
      MailApp.sendEmail(currentEmail,"Sorry the auction is now closed.", "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")
    }
}
  }
}

function sendResults(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var maximumBid = ss.getRange(2,6).getValue();
  for (var i=2 ; i<=lr; i++){
    var currentEmail = ss.getRange(i,3).getValue();
    var currentName = ss.getRange(i,2).getValue();
    var currentBid = ss.getRange(i,4).getValue();
    var currency = ss.getRange(i,5).getValue();
    var hkdBid = ss.getRange(i,8).getValue();
    if (hkdBid==maximumBid){
      MailApp.sendEmail(currentEmail, currentName+" you won!", "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 "+currency+"$"+currentBid+"!"+"\n"+"Come pick it up at Hong Kong International School at the start of school. Thank you!"+"\n\n"+"By Simon")
    }
}
  
}

Leave a Reply