Created
December 20, 2018 10:06
-
-
Save cordx56/71cbd6e2c6d1b545cc24a9869e841e5e to your computer and use it in GitHub Desktop.
メールアドレスの記録を有効にしたフォームの回答結果スプレッドシートから,メールアドレスを元に必要な情報を自動入力します.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
何故switch文を使わなかったのか覚えていないのですが,多分変則的な名前(特にMAとかその辺)に対応するためだった気がします.
必要に応じてswitch文に差し替えてください……(◞‸◟ )