メールアドレスの記録を有効にしたフォームの回答結果スプレッドシートから，メールアドレスを元に必要な情報を自動入力します．

sit-form.gs

function updateStudentId() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetId = sheet.getSheetId();
  var range = sheet.getDataRange();
  var vals = range.getValues();
  var mailColumn;
  var studentIdColumn;
  var facultyColumn;
  var departmentColumn;
  
  for (var i = 0; i < vals[0].length; i++) {
    if (vals[0][i] == "メールアドレス") mailColumn = i;
    if (vals[0][i] == "学籍番号") studentIdColumn = i;
    if (vals[0][i] == "学部") facultyColumn = i;
    if (vals[0][i] == "学科") departmentColumn = i;
  }
  
  for (var i = 1; i < vals.length; i++) {
    var studentId = vals[i][mailColumn].substr(0, 7).toUpperCase();
    if (vals[i][mailColumn].length > 7) {
      vals[i][studentIdColumn] = studentId;
    }
    if (facultyColumn != undefined) {
      var fac = studentId.substr(0, 1);
      if (fac == "A") vals[i][facultyColumn] = "工学部";
      else if (fac == "B") vals[i][facultyColumn] = "システム理工学部";
      else if (fac == "C") vals[i][facultyColumn] = "デザイン工学部";
      else if (fac == "D") vals[i][facultyColumn] = "建築学部";
      else if (fac == "M") vals[i][facultyColumn] = "大学院";
    }
    if (departmentColumn != undefined) {
      var dep = studentId.substr(0, 2);
      if (dep == "AA") vals[i][departmentColumn] = "機械工学科";
      else if (dep == "AB") vals[i][departmentColumn] = "機械機能工学科";
      else if (dep == "AC") vals[i][departmentColumn] = "材料工学科";
      else if (dep == "AD") vals[i][departmentColumn] = "応用化学科";
      else if (dep == "AE") vals[i][departmentColumn] = "電気工学科";
      else if (dep == "AF") {
        if (parseInt(studentId.substr(2, 2), 10) >= 18) vals[i][departmentColumn] = "情報通信工学科";
        else vals[i][departmentColumn] = "通信工学科";
      }
      else if (dep == "AG") vals[i][departmentColumn] = "電子工学科";
      else if (dep == "AH") vals[i][departmentColumn] = "土木工学科";
      else if (dep == "AJ") vals[i][departmentColumn] = "建築学科";
      else if (dep == "AK") vals[i][departmentColumn] = "建築工学科";
      else if (dep == "AL") vals[i][departmentColumn] = "情報工学科";
      
      else if (dep == "BP") vals[i][departmentColumn] = "電子情報システム学科";
      else if (dep == "BQ") vals[i][departmentColumn] = "機械制御システム学科";
      else if (dep == "BR") vals[i][departmentColumn] = "環境システム学科";
      else if (dep == "BN") vals[i][departmentColumn] = "生命科学科";
      else if (dep == "BV") vals[i][departmentColumn] = "数理科学科";
      
      else if (dep == "CY") vals[i][departmentColumn] = "デザイン工学科";
      
      else if (dep == "DZ") vals[i][departmentColumn] = "建築学科";
    }
  }
  range.setValues(vals);
}

何故switch文を使わなかったのか覚えていないのですが，多分変則的な名前（特にMAとかその辺）に対応するためだった気がします．
必要に応じてswitch文に差し替えてください……(◞‸◟ )