Thông tin khai báo y tế được thu thập bằng Google Forms, lưu trữ dữ liệu bằng Google Sheets, mở rộng chức năng bằng Google Apps Script, gửi thông báo bằng Gmail.

Theo tình hình hiện nay, thành phố đã ban hành quy định cấm tụ tập quá 20 người tại nơi công cộng và công ty cũng vừa quy định tất cả mọi người phải khai báo thông tin khi đi đến nơi đông người nên mình đã nghĩ ngay đến Google Form để có thể áp dụng liền vào công ty trong sáng đầu tuần. Sau khi đã thiết kế form với một vài chức năng mở rộng nho nhỏ, mình nghĩ có thể công ty khác cũng có quy định tương tự nên viết vài dòng chia sẻ với anh em, hy vọng nó hữu ích với một ai đó.

Yêu cầu

Công ty quy định phải khai báo y tế bằng văn bản đối với trường hợp có triệu chứng nghi ngờ hoặc có yếu tố dịch tễ liên quan đến các vùng có dịch. Trường hợp này cần giấy trắng mực đen nên mình không viết form cho trường hợp này.

Công ty yêu cầu các trường hợp di chuyển bằng phương tiện công cộng (máy bay, tàu thuyền, taxi...) phải thực hiện khai báo, chỉ cần khai báo bằng tin nhắn cho quản lý trực tiếp cũng được chứ không cần phải ký văn bản. Nhưng nếu chỉ dùng tin nhắn thì thông tin rất dễ bị trôi và khó tìm lại nên mình mới nghĩ đến Google Forms và Google Sheets để quản lý dữ liệu một cách có hệ thống và thuận tiện cho mọi người nhập thông tin. Tương tự, khi đi đến các nơi đông người như siêu thị, bệnh viện, hội nghị... cũng phải thực hiện khai báo.

• Thông tin di chuyển và thông tin đi mua sắm cần những trường dữ liệu khác nhau dùng chức năng section của Google Forms để phân chia các phần thông tin khác nhau (section Di chuyển và section Địa điểm).
• Ở phần đầu thì người dùng điền thông tin hành chính rồi chọn một loại hoạt động dùng tính năng go to section để di chuyển đến đúng phần thông tin theo hoạt động của người dùng.
• Sau khi người dùng đã khai báo xong thì gửi email về hộp thư của họ sử dụng chức năng receipts đã được hỗ trợ sẵn của Google Forms (Setting / Collect emails/ Response receipts).
• Mỗi khi người dùng submit form thì gửi thông báo cho leader Google Sheets có sẵn chức năng Notification rules nhưng rất tiếc là chỉ có thể gửi cho admin mà không thể thêm địa chỉ email của leader. Chúng ta cần viết code Apps Script để mở rộng chức năng này.
• Để tiện cho người dùng xem lại các thông tin mà họ đã khai báo chúng ta sẽ chia sẻ file Google Sheet cho họ tạo mỗi người một file rồi dùng hàm importRange để trích xuất dữ liệu của người đó. Sử dụng Apps Script để tạo file cho nhanh vì các file này giống nhau.

Thực hiện

Thiết kế form

Việc thiết kế bằng Google Form cũng không có gì phức tạp. Ở section 1 chúng ta sẽ đặt câu hỏi về loại hoạt động của người dùng. Ở đây chúng ta thấy hoạt động Di chuyển sẽ có đặc điểm các trường dữ liệu khác với các hoạt động còn lại nên chúng ta sẽ tạo section cho Di chuyển và section Tập trung đông người cho các hoạt động còn lại. Ở câu hỏi loại hoạt động này, chúng ta sẽ nhấn vào dấu ba chấm (...) để chọn tùy chọn Go to section based on answer rồi chọn section tương ứng.

Cuối section 1 thì chọn Continue to next section, cuối các section khác thì chọn Submit form. View live form here.

Cuối cùng sau khi tạo form xong thì chúng ta tạo sheet để lưu thông tin (giả sử đặt tên sheet là Responses). Chúng ta tạo thêm 1 sheet có tên Staff để lưu một vài thông tin nhân viên.

Chức năng tạo file cho mỗi user

Theo yêu cầu ở trên, chúng ta sẽ tạo cho mỗi người dùng (thông tin ở sheet Staff) 1 file Google Sheet để họ dễ dàng xem lại các thông tin đã khai báo. Trong mỗi file thì chúng ta sẽ dùng hàm importRange để cập nhật dữ liệu của họ.

=QUERY(IMPORTRANGE("1N0L2OicYrAOaYZWdxB3fJMUdojvfKkOjliT_41DBNDI", "Responses!A:L"), "SELECT * WHERE Col3 = 'T1235M' ORDER BY Col1", 1)

Giả sử chúng ta có khoảng vài chục đến khoảng 100 nhân viên nên chúng ta sẽ dùng Apps Script để tạo file cho thuận tiện.

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);
}

Chức năng gửi thông báo cho user

Khi share file cho user bằng code thì không có tùy chọn thông báo cho người dùng (giao diện người dùng có tùy chọn này) nên chúng ta viết chức năng gửi thông báo bằng email để user biết.

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.sendEmail(
      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]
    );
  }
}

Tham khảo thêm sendEmail method

Chức năng thông báo cho leader

Chúng ta sẽ viết function onSubmitForm dưới dạng installable trigger để thực hiện gửi thông báo cho leader bằng email khi có người khai báo. Bạn có thể thay địa chỉ email của leader vào chuỗi bossMails bên dưới, các địa chỉ email cách nhau bằng dấu phẩy.

function onFormSubmit(e) {
  let bossMails = '[email protected], [email protected], [email protected]';
  let userMail = e.values[1];
  let activity = e.values[3];
  GmailApp.sendMail(
    bossMails, 
    'Hoạt động khai báo mới', 
    'Nhân viên ' + userMail + ' vừa khai báo hoạt động: ' + activity
  )
}

Tham khảo thêm How to get onFormsubmit to trigger automatically.

Kết luận

Như vậy chúng ta đã có được hệ thống thu thập thông tin khai báo y tế để áp dụng ngay cho công ty quy mô (vài chục người) vào đầu tuần. Ưu điểm của việc sử dụng Google Form là triển khai nhanh, mở rộng chức năng dễ dàng. Nhược điểm là admin vẫn phải mở từng file và nhấn cho phép truy cập thì các file của user mới lấy dữ liệu được (lỗi #REF!) nên nãy giờ bấm muốn mỏi tay rồi, đồng thời file của user sẽ vẫn nhìn thấy công thức chỉ có điều là chỉ được cấp quyền view nên không chỉnh sửa được. User muốn lọc hay sắp xếp dữ liệu ở file chia sẻ thì có thể sử dụng chức năng Filter view.

Một nhược điểm khác của cách thiết kế này là không ngăn được những người tốt bụng "khai báo dùm" đồng nghiệp, id nhân viên cần nhập chính xác để hiển thị dữ liệu đúng (định nhập sẵn cho user chọn nhưng như vậy thì khả năng chọn sai cũng cao nên thôi).

Sheet

Form

Gist