form trigger
unknown
javascript
2 years ago
3.4 kB
2
Indexable
Never
const targetFolderId = '1Xhpwe-nqEELVNuSyu_1LCrwq5-QKPC7v'; // result mentor 1 folder const targetFolder = DriveApp.getFolderById(targetFolderId); const dataFileId = '1wSAWGlYV6rNZMmlZ4QBhqaSY-SEh5m8rA_2AJIQHeIo'; // data for form const noMentorSheet = 'studentWithoutMentor'; function onSubmit(e) { // FormApp.getActiveForm() let data = SpreadsheetApp.openById(dataFileId).getSheetByName('Student_Mentor').getDataRange().getValues(); data.shift(); // remove header row // convert to dictionary studentEmail => mentorEmail let student_mentor = {}; data.forEach(row => student_mentor[row[0]] = row[1]); // Logger.log(student_mentor); let studentEmail = e.response.getRespondentEmail(); let formName = DriveApp.getFileById(e.source.getId()).getName(); let lessonNumber = formName.substring(formName.lastIndexOf(' ') + 1) let time = getTime(); var mentorEmail = null; if (studentEmail in student_mentor) { mentorEmail = student_mentor[studentEmail]; } let fileId = e.response.getItemResponses()[0].getResponse(); let fileURL = 'https://drive.google.com/open?id=' + fileId; write_submit(studentEmail, mentorEmail, lessonNumber, time, fileURL); } function getTime() { var d = new Date(); let datestring = (d.getHours()).toString().padStart(2, '0') + ':' + (d.getMinutes()).toString().padStart(2, '0') + ':' + (d.getSeconds()).toString().padStart(2, '0') + ' ' + (d.getDate()).toString().padStart(2, '0') + '/' + (d.getMonth() + 1).toString().padStart(2, '0') + '/' + d.getFullYear() return "'" + datestring; } function write_submit(studentEmail, mentorEmail, lessonNumber, time, fileURL) { var studentSheet; if (mentorEmail == null) { studentSheet = SpreadsheetApp.openById(dataFileId).getSheetByName('studentWithoutMentor') // let i = studentSheet.getRange(1, 1, null, 4).getValues().length + 1; //current row+1 let newRowNumber = studentSheet.getDataRange().getValues().length; Logger.log(newRowNumber); studentSheet.getRange(newRowNumber, 2).setValue(lessonNumber);// Bài nghe studentSheet.getRange(newRowNumber, 3).setValue(time);// Time studentSheet.getRange(newRowNumber, 4).setValue(fileURL);// Link studentSheet.getRange(newRowNumber, 5).setValue(studentEmail);// student email not have mentor } else { let mentorFile = targetFolder.getFilesByName(mentorEmail).next(); let mentorFileId = mentorFile.getId(); studentSheet = SpreadsheetApp.openById(mentorFileId).getSheetByName(studentEmail); let newRowNumber = getLastRowToWrite(studentSheet); // return; studentSheet.getRange(newRowNumber, 2).setValue(lessonNumber);// Bài nghe studentSheet.getRange(newRowNumber, 3).setValue(time);// Time studentSheet.getRange(newRowNumber, 4).setValue(fileURL);// Link } } function getLastRowToWrite(activeSheet) { let allData = activeSheet.getDataRange().getValues(); for (var rowNumber=0;rowNumber<allData.length;rowNumber++){ if (allData[rowNumber][2]==''){ Logger.log('result %d',rowNumber) return rowNumber+1; } } } /* appsscript.json Project Setting -> Show "appsscript.json" manifest file in editor "oauthScopes": [ "https://www.googleapis.com/auth/forms", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive" ], */