Health_Declaration_Tracker.md

Track health declaration using Google Form. Function extension with Google Apps Script.

• Create spreadsheet for each user.
• Send notification to users.
• Send notification to leader when user submit.

function onOpen() {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu('Tracker')
    .addItem('Create shared sheet', 'createSheets')
    .addItem('Send user notify', 'notifyUser')
    .addToUi();
}

function createSheets() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let staffSheet = ss.getSheetByName('Staff');
  let staffData = staffSheet.getDataRange().getValues();
  let folder = getParentFolder(ss.getId());
  let urlArray = [];

  ss.toast('Đang tạo sheets, thời gian ước tính khoảng: ' + staffData.length * 3 + ' giây.');

  for (i=1; i<staffData.length; i++) {
    let staffId = staffData[i][0]; // Staff id in the first column
    let staffEmail = staffData[i][3]; // Email in the 4 column
    let file = SpreadsheetApp.create(staffId);
    let fileId = file.getId();
    DriveApp.getFileById(fileId).moveTo(folder);
    file.addViewer(staffEmail);
    let formula = `=QUERY(IMPORTRANGE("1N0L2OicYrAOaYZWdxB3fJMUdojvfKkOjliT_41DBNDI", "Responses!A:L"),
    "SELECT * WHERE Col3 = '`+ staffId +`' ORDER BY Col1", 1)`
    file.getSheets()[0].getRange(1, 1).setFormula(formula);
    urlArray.push([file.getUrl()]);
    Logger.log(file.getName());
  }
  staffSheet.getRange(2, staffSheet.getLastColumn()+1, staffData.length-1)
    .setValues(urlArray);
  //Logger.log(urlArray);
}

function notifyUser() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let staffSheet = ss.getSheetByName('Staff');
  let staffData = staffSheet.getDataRange().getValues();
  let emails = staffData.map(row => {return row[3]}); // Email in the 4 column
  let links = staffData.map(row => {return row[4]}); // Url link in the last column
  for (i=1; i< emails.length; i++) {
    GmailApp.createDraft(
      emails[i], 
      'Khai báo y tế', 
      'Xem dữ liệu khai báo y tế của bạn tại đường link này: ' + links[i]
    );
  }
}

function onFormSubmit(e) {
  let bossMails = 'manager@gmail.com, leader@gmail.com, admin@gmail';
  let userMail = e.values[1];
  let activity = e.values[3];
  GmailApp.createDraft(
    bossMails, 
    'Hoạt động khai báo mới', 
    'Nhân viên ' + userMail + ' vừa khai báo hoạt động: ' + activity
  )
}
function getParentFolder(id) {
  let file = DriveApp.getFileById(id);
  let folders = file.getParents();
  return folders.next();
}