nord vpnnord vpn
Ad

form trigger

mail@pastecode.io avatar
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"
  ],
 */

nord vpnnord vpn
Ad