Script

function onFormSubmit(e) {

  var URL = "https://docs.google.com/spreadsheet/ccc?key=0Ap1pFKc61f68dHdxdk0wa29IQWV2U21CZlV0SFZQT0E"; // 表單的spreadsheet URL
  var GName = "軟體創作研發社團十月聚餐";
  var GDate = "10/2(三)";
  
  var yourTimestamp = e.values[0];
  var yourGrade = e.values[1];
  var yourName = e.values[2];
  var yourPhone = e.values[3];
  var yourMail = e.values[4];
  var yourGroup = e.values[5];
  var yourURL = e.values[6];
  var yourSay = e.values[7];
 
  var timestamp;
  var name;
  var mail;
  
  var subject;
  var mailBody;
  
  var sheets = SpreadsheetApp.openByUrl(URL).getSheets();
  var sheet = sheets[0];
  var sheetList = sheets[1];
  var data = sheet.getDataRange().getValues();
  var dataList = sheetList.getDataRange().getValues();
  
  var checkExist = false;
  
  for(var i = 1; i < data.length-1; i++){
    timestamp = sheet.getRange(i+1, 1).getValue();
    name = sheet.getRange(i+1, 3).getValue();
    mail = sheet.getRange(i+1, 5).getValue();
    
    if(mail == yourMail){
      checkExist = true;
      sheet.getRange(data.length, 9).setValue(i+1);
      break;
    }
  }
  
  if(checkExist == true){
    subject = "[" + GName + "] 收到報名資料(重複報名)──" + yourName;
    mailBody = 
      yourName + "您好，\n" + 
      "感謝您於 " + yourTimestamp + " 報名 " + GDate + " 的 " + GName + " ，\n" +
      "不過我們發現您於 " + timestamp + " 曾以相同的EMail " + yourMail + " 報名本次活動，\n" + 
      "若您真的未報名過" + GName + "，\n" + 
      "還請您回信告訴我們，否則此次報名將無效，感謝您！";
  }
  else{
    subject = "[" + GName + "] 收到報名資料──" + yourName;
    mailBody = 
      yourName + "您好，\n" + 
      "感謝您於 " + yourTimestamp + " 報名 " + GDate + " 的 " + GName + " ，\n" + 
      "以下為您的報名資料：\n" + 
      "系級：" + yourGrade + "\n" +
      "姓名：" + yourName + "\n" + 
      "連絡電話：" + yourPhone + "\n" + 
      "EMail：" + yourMail + "\n" + 
      "若您的報名資料有誤，煩請回信告知，感謝您！" ; 
    if(yourGroup=="否")
      mailBody += "\n\n另外，由於您原先並非「軟體創作研發社團」成員，稍晚會邀請您加入，還請您不要將它當作是廣告社團而退出了，謝謝您！";
   
    sheetList.getRange(dataList.length+1, 1).setValue(yourTimestamp);
    sheetList.getRange(dataList.length+1, 2).setValue(yourGrade);
    sheetList.getRange(dataList.length+1, 3).setValue(yourName);
    sheetList.getRange(dataList.length+1, 4).setValue(yourPhone);
    sheetList.getRange(dataList.length+1, 5).setValue(yourMail);
    sheetList.getRange(dataList.length+1, 6).setValue(yourGroup);
    sheetList.getRange(dataList.length+1, 7).setValue(yourURL);
    sheetList.getRange(dataList.length+1, 8).setValue(yourSay);
  }
    
    mailBody = mailBody + "\n\n" + 
      "--\n" +
      "東華資工二 林育慈\n" +
      "410121021@ems.ndhu.edu.tw";
    
  MailApp.sendEmail("\"" + yourName + "\" <" + yourMail + ">,\"林育慈\" <linyutsu23@gmail.com>", subject, mailBody);

}