https://medium.com/@TechandEco
Bài toán: Ứng dụng CNTT App Scripts của Google để nâng cao hiệu quả update Danh bạ trong thời buổi Covid19 đang hoành hành.
- Có một file exel thông tin CBVC trong đơn vị phân loại theo Phòng Ban, Đơn vị bộ môn ... có các trường thông tin, trong đó cần cập nhật lại SĐT, Email, Vị trí làm việc ...
- Yêu cầu ứng với mỗi đơn vị tạo một file exel riêng chỉ chứa thông tin của Đơn vị đó, rồi phân quyền chỉ Trưởng đơn vị mới có quyền sửa nội dung, nhưng bị giới hạn 3 cột: Điện thoại, Vị trí làm việc, Trạng thái update.
- Gửi email yêu cầu các đơn vị trực tiếp hoặc download về :D, nếu tự sửa thêm Button chỉ có tác dụng với Trưởng đơn vị, khi click sẽ gửi email đến tttt@nuce.edu.vn đồng thời xoá quyền chính sửa User.
- Cập nhật xủ lý hậu kỳ ... bla bla bla
Các bước thực hiện:
- Bước 1: Tạo các sheet có tên file là tên từng đơn vị => Tống vào Folder Bộ môn hoặc Phòng, Ban.
- Bước 2: Lọc data theo trường "Tên đơn vị" trong sheet Data gốc, dùng hàm query và import data
- Bước 3: Mục đích cần Value only trong cell để khi nhỡ edit vào range thì ko bị lỗi Import data => Thủ thuật...
- Bước 4: Làm đẹp các sheet đơn vị: width, border, background, align, center ....
- Bước 5: Phân quyền: chú ý thứ tự các bước => luôn là thứ mất thời gian.
- Bước 6: Tổng hợp hậu kỳ.
App Script:
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetsCount = ss.getNumSheets();
var sheets = ss.getSheets();
var ui = SpreadsheetApp.getUi();
function onOpen() {
// Try New Google Sheets method
ui.createMenu('Phòng TT&TT')
.addItem('CBVC - Tạo hàng loạt Bộ môn', 'taoFileGgSheets')
.addSeparator()
.addItem('CBVC - Tạo hàng loạt Phòng Ban', 'taoFileGgSheetsPhongBan')
.addSeparator()
.addItem('CBVC - Giới hạn quyền ghi cột Điện thoại, Email', 'setQuyenHangLoat')
.addSeparator()
.addItem('CBVC - Gửi email đến các Trưởng đơn vị', 'guiEmail')
//.addItem('Copy Sheets', 'copySheets')
//.addSeparator()
//.addItem('Giới thiệu 4T', 'aboutFile')
.addToUi();
}
// Tạo mới file từ danh sách các bộ môn ở Sheet Danh sách bộ môn và cho vào trong idFolder
function getIdFrom(url) {
try {
var id = "";
var parts = url.split(/^(([^:\/?#]+):)?(\/\/([^\/?#]*))?([^?#]*)(\?([^#]*))?(#(.*))?/);
if (url.indexOf('?id=') >= 0) {
id = (parts[6].split("=")[1]).replace("&usp", "");
return id;
} else {
id = parts[5].split("/");
//Using sort to get the id as it is the longest element.
var sortArr = id.sort(function(a, b) {
return b.length - a.length });
id = sortArr[0];
return id;
}
} catch (err) {
ui.alert('Xảy ra lỗi, hãy kiểm tra lại URL của bạn !' + err);
}
}
function queryGetData(tenDonVi) {
// Nhồi Data vào biến tạm, clipboad, cache => Ko biết bộ nhớ tạm của Google gọi là gì ?
var idSpr = '1aALcJTbOiKrEJpbiUocMjMrWS3IcWdCYhKw1EpkD-Rc'; // Id file chứa data
var rangeData = "Data!A:S"; // Sheet trường chứa dữ liệu
var importRan = 'IMPORTRANGE(\"' + idSpr + '\",\"' + rangeData + '\")'; // Hàm IMPORTRANGE
// Lọc dữ liệu cột 08 là cột chứa tên Bộ môn
var colFilter = 'Col8';
var select = '\"SELECT * WHERE ' + colFilter + '=\'' + tenDonVi + '\'\"';
// Trả về dữ liệu
return '\=QUERY(' + importRan + ',' + select + ',1)'; // Số 1 là bỏ qua một Row header
/*
Dữ liệu mong muốn trả về: =QUERY(IMPORTRANGE("1aALcJTbOiKrEJpbiUocMjMrWS3IcWdCYhKw1EpkD-Rc","Data!$A$1:$S$945"),"SELECT * WHERE Col8='BM Cảng - Đường thuỷ'",1)
Tham khảo thêm Document tại: https://developers.google.com/chart/interactive/docs/querylanguage
*/
}
function taoFileGgSheets() {
var url = Browser.inputBox("Dán URL thư mục muốn tạo các file vào đây !\\n .\\n");
var idThuMuc = getIdFrom(url);
var tenSheetDsFile = Browser.inputBox("Gõ tên SHEET chứa TÊN các file cần tạo \\n (ví dụ: Sheet1 hoặc Danh sách...) \\n .\\n");
var folder = DriveApp.getFolderById(idThuMuc);
var sheetDS = ss.getSheetByName(tenSheetDsFile);
var range = sheetDS.getDataRange();
var values = range.getValues();
var lastRow = range.getLastRow();
for (var k = 1; k < lastRow; k++) {
try {
// Lấy tên file cần tạo ở cột A
var tenFile = values[k][0];
sheetDS.getRange(k + 1, 1).setBackground('#b6d7a8'); //Set màu sau khi lấy
ss = SpreadsheetApp.create(tenFile);
// Ghi Id file mới tạo vào Cột B
var id = ss.getId();
range.getCell(k + 1, 2).setValue(id);
// Thêm file mới tạo vào Folder có link là đầu vào
var file = DriveApp.getFileById(id);
folder.addFile(file);
// Gán link file cột D
var ganLinkTenFile = '=HYPERLINK("' + file.getUrl() + '","' + tenFile + '")';
range.getCell(k + 1, 1).setFormula(ganLinkTenFile);
// Lấy hàm query get dữ liệu
//var query = range.getCell(k + 1, ).getValue(); //Lấy hàm query ở cột 3
// XỬ LÝ FILE MỚI TẠO
var ssBomon = SpreadsheetApp.openById(id);
var sheetBomon = ssBomon.getSheetByName('Sheet1');
var rangeBomon = sheetBomon.getDataRange();
// Get data với bộ lọc Filter Bộ môn
var query = queryGetData(tenFile);
rangeBomon.getCell(1, 1).setValue(query);
// Trình bày file mới tạo
var kichThuoc = trinhBaySheet(id);
range.getCell(k + 1, 3).setValue(kichThuoc);
// Ko lấy ra được vì hàm query chưa điền nội dung
//var soCanbo = rangeBomon.getLastRow();
//var soTruongThongtin =rangeBomon.getLastColumn();
//range.getCell(soCanbo, soTruongThongtin).setBorder(true, true, true, true, true, true);
} catch (err) {
ui.alert('Xảy ra lỗi, hãy kiểm tra lại \\n' + err);
}
}
var thongBao = 'Đã tạo xong: ' + (k - 1) + ' file trong thư mục: ' + folder.getName();
ui.alert(thongBao);
}
function chayThu() {
//var idSheet = '1SwfknqGvcqn5s2NZzMI5za403ru8z60PEjoyegfy5MA';
//trinhBaySheet(idSheet);
//setQuyenSheet(idSheet);
//var str = queryGetData('Phòng TT&TT');
//Logger.log(str);
var linkFile = 'https://docs.google.com/spreadsheets/d/1dEN1iL2VfxmiSzJSLpAbKf_wDFq3ZRpkF98jw5pr464/edit#gid=0';
var tenFile = 'BM Cảng - Đường thuỷ';
setQuyenSheet('1Tr8AbyHqsWd6CMnfdUZmqROFUXIIJ4u75df-cYVDn_s');
}
function setQuyenHangLoat() {
var tenSheetDsFile = Browser.inputBox("Gõ tên SHEET chứa chứa ID nằm ở cột B, \\n (ví dụ: Sheet1 hoặc DS...) \\n ");
var sheetDS = ss.getSheetByName(tenSheetDsFile);
//var sheetDS = ss.getSheetByName('DS');
var range = sheetDS.getDataRange();
var lastRow = range.getLastRow();
for (var k = 2; k <= lastRow; k++) {
try {
// Lấy tên file cần tạo ở cột A
var tenFile = sheetDS.getRange(k, 1).getValue();
var idSheet = sheetDS.getRange(k, 2).getValue();
var kichThuoc = sheetDS.getRange(k, 3).getValue();
var email = sheetDS.getRange(k, 4).getValue();
//Logger.log(idSheet + '-' + email);
setQuyenSheet(idSheet, email);
} catch (err) {
ui.alert('Xảy ra lỗi, hãy kiểm tra lại \\n' + err);
}
}
ui.alert('Đã giới hạn xong quyền edit cho: ' + (lastRow - 1) + ' file');
}
function setQuyenSheet(idSheet, email) {
var fileGgSheetBomon = DriveApp.getFileById(idSheet);
fileGgSheetBomon.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
//Logger.log(fileGgSheetBomon.getUrl());
// Protect the active sheet except colored cells, then remove all other users from the list of editors.
var ssBomon = SpreadsheetApp.openById(idSheet);
var sheetBomon = ssBomon.getSheetByName('Sheet1');
var rangeBomon = sheetBomon.getDataRange();
// Xóa tất cả các range đã bảo vệ
var protections = sheetBomon.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
var protection = protections[i];
if (protection.canEdit()) {
protection.remove();
}
}
var range = sheetBomon.getRange(1, 1, 900, 12); //A-L
var range1 = sheetBomon.getRange(1, 15, 900, 4); //O-R
var rangeDienThoai = sheetBomon.getRange(1, 13, 900, 2); //M-N
var rangeTrangThai = sheetBomon.getRange(1, 19, 900, 1); //S
var date = Utilities.formatDate(new Date(), "GMT+7", "HH:mm:ss', 'dd/MM/yyyy ");
var protection = range.protect().setDescription('Protect cột A - cột L: (' + date + ')');
var protection1 = range1.protect().setDescription('Protect cột O - cột R : (' + date + ')');
var protectionDienThoai = rangeDienThoai.protect().setDescription('Protect cột M - cột N: (' + date + ')');
var protectionTrangThai = rangeTrangThai.protect().setDescription('Protect cột S : (' + date + ')');
var me = Session.getEffectiveUser();
fileGgSheetBomon.setOwner(me);
protection.addEditor(me);
protection1.addEditor(me);
// Chỉ cho email sửa cột Điện thoại, Phòng làm việc, Trạng thái update
protectionDienThoai.addEditor(email);
protectionTrangThai.addEditor(email);
//fileGgSheetBomon.addEditor(email);
//fileGgSheetBomon.addViewer(email);
fileGgSheetBomon.addEditor(email); //CHÚ Ý VỊ TRÍ ĐẶT HÀM
}
function trinhBaySheet(id) {
var idCuaSheet = id;
//var idCuaSheet = '1t6_xb8FY5dvb7q1a13YWwCfAi9JZW9bvZETbC2tuRLU';
var ssBomon = SpreadsheetApp.openById(idCuaSheet);
var sheetBomon = ssBomon.getSheetByName('Sheet1');
var rangeBomon = sheetBomon.getDataRange();
var soHang = rangeBomon.getLastRow();
var soCot = rangeBomon.getLastColumn();
// Copy dữ liệu sang range mới để xóa hàm Query tại A1
var rangeMoi = sheetBomon.getRange(soHang + 1, 1, soHang, soCot);
sheetBomon.getRange(1, 1, soHang, soCot).copyTo(rangeMoi, { contentsOnly: true })
// Xóa hàng
sheetBomon.deleteRows(1, soHang);
// SET MÀU HÀNG ĐẦU, CÂN GIỮA, WRAP, BORDER, CANH MIDDLE
sheetBomon.getRange(1, 1, soHang, soCot).setHorizontalAlignment('left'); //Set cân bên trái tất cả về Default
sheetBomon.autoResizeRows(1, soHang); //Auto độ rộng all hàng
sheetBomon.getRange(1, 1, 1, soCot).setBackground('#FECB8D'); //Set màu nền Vàng cam cho hàng Tiêu đề.
sheetBomon.getRange(1, 1, 1, soCot).setFontWeight("bold"); //Set Font chữ in đậm hàng đầu tiên
sheetBomon.getRange(1, 1, 1, soCot).setWrap(true); //Set hàng đầu tiên Wrap
sheetBomon.getRange(1, 1, 1, soCot).setHorizontalAlignment('center'); //Set cân giữa cho hàng đầu tiên
sheetBomon.getRange(1, 1, soHang, soCot).setBorder(true, true, true, true, true, true); //Set Border cho tất cả các cell
sheetBomon.getRange(1, 1, soHang, soCot).setVerticalAlignment('middle'); //Canh middle cho tất cả các cell
sheetBomon.getRange(2, 13, soHang - 1, 2).setBackground('#b6d7a8');
sheetBomon.getRange(2, 19, soHang - 1, 1).setBackground('#b6d7a8');
// SET ĐỘ RỘNG BẰNG TAY CHO CÁC CỘT ĐỂ NHÌN HỢP LÝ
sheetBomon.setColumnWidth(1, 50); // Mã số CB
sheetBomon.setColumnWidth(2, 70); // Học hàm học vị
sheetBomon.setColumnWidth(3, 100); // Họ đệm
sheetBomon.setColumnWidth(4, 60); // Tên
sheetBomon.setColumnWidth(5, 80); // Năm sinh
sheetBomon.setColumnWidth(6, 150); // Quê quán
sheetBomon.setColumnWidth(7, 50); // Giới tính
sheetBomon.autoResizeColumns(8, 9); // Auto Bộ môn, Đơn vị, Email, Quang check, User ID, Điện thoại
sheetBomon.setColumnWidth(10, 140); // Email
sheetBomon.autoResizeColumns(11, 13); // Auto Quang check, User ID, Điện thoại
sheetBomon.setColumnWidth(14, 70); // Phòng làm việc
sheetBomon.setColumnWidths(15, 4, 90); // Set cột 15 đến 18
sheetBomon.setColumnWidths(19, 1, 150); // Set cột 19
sheetBomon.getRange(1, 1, soHang, 2).setHorizontalAlignment('center'); //Set cân giữa hai cột đầu tiên
// Set date format
var colNamsinh = sheetBomon.getRange(5, 5, 100); // 100 thay cho soHang ?
colNamsinh.setNumberFormat('dd/mm/yyyy');
// ẨN CÁC CỘT KHÔNG CẦN NHÌN
var col9 = sheetBomon.getRange(9, 9, soHang); // Cột Đơn vị
sheetBomon.hideColumns(6, 2); // Ẩn cột 6, 7: Quê quán, Giới tính
sheetBomon.hideColumn(col9); // Ẩn Cột Đơn vị
sheetBomon.hideColumns(11, 2); // Ẩn cột 11, 12: Cột trùng email; UserID
sheetBomon.hideColumns(17, 2); // Ẩn cột 17, 18: GV SDH, Học lên
return soHang + 'x' + soCot;
}
// alert if no sheets matched the user input
function noMatchAlert() {
var ui = SpreadsheetApp.getUi();
var result = ui.alert(
'Trường dữ liệu bạn gõ vào ko đúng \\n',
"Kiểm tra lại cho chính xác nhé ok ?",
ui.ButtonSet.OK);
}
// alert after succesful action (only used in copy)
function successAlert(action) {
var ui = SpreadsheetApp.getUi();
var result = ui.alert(
'Thành công !\\n',
"You're sheets were " + action + " successfully.",
ui.ButtonSet.OK);
}
function guiEmail() {
var googleLogoUrl = "http://nuce.edu.vn//sites/default/files/pictures/tin/2020/T3/congvan_updatedb.jpg";
var danhBaLogoUrl = "http://www.nuce.edu.vn/sites/default/files/pictures/tin/2020/T3/danhbacu_0.jpg";
var googleLogoBlob = UrlFetchApp
.fetch(googleLogoUrl)
.getBlob()
.setName("googleLogoBlob");
var danhBaLogoBlob = UrlFetchApp
.fetch(danhBaLogoUrl)
.getBlob()
.setName("danhBaLogoBlob");
var tenSheetDsFile = Browser.inputBox("Gõ tên SHEET chứa chứa ID nằm ở cột B, \\n (ví dụ: Sheet1 hoặc DS...) \\n ");
var sheetDS = ss.getSheetByName(tenSheetDsFile);
//var sheetDS = ss.getSheetByName('DS');
var range = sheetDS.getDataRange();
var lastRow = range.getLastRow();
for (var k = 2; k <= lastRow; k++) {
try {
// Lấy tên file cần tạo ở cột A
var tenFile = sheetDS.getRange(k, 1).getValue();
var idSheet = sheetDS.getRange(k, 2).getValue();
//var kichThuoc = sheetDS.getRange(k, 3).getValue();
var email = sheetDS.getRange(k, 4).getValue();
var linkFile = DriveApp.getFileById(idSheet).getUrl();
var lanhDaoDv = sheetDS.getRange(k, 5).getValue();
var chucVu = sheetDS.getRange(k, 6).getValue();
var tieuDe = '[TT&TT] - Cập nhật lại danh bạ CBVC: ' + tenFile;
var date = Utilities.formatDate(new Date(), "GMT+7", "HH:mm:ss', 'dd/MM/yyyy ");
var noiDungEmail = "Kính gửi thầy cô: " + lanhDaoDv + "
" +
"Chức vụ: " + chucVu + "
" +
"Đơn vị : " + tenFile + "
" +
"Phòng Thông tin và Truyền thông đang tiến hành cập nhập lại Danh bạ số điện thoại CBVC của Trường (nội bộ)
" +
"Phòng xin gửi danh sách các thành viên trong " + tenFile + " có Thông tin chi tiết cập nhật đến thời điểm hiện tại ở link Google sheet sau:
" + linkFile + "
" +
"Kính mong các thầy cô download và cập nhật (thêm, bớt, sửa, xoá) thông tin mới nhất các thành viên có trong đơn vị mình
" +
"Xin gửi lại Phòng TT&TT trước ngày 3/4/2020 .
" +
"Trân trọng cảm ơn.
" +
"Thông báo
";
MailApp.sendEmail({
to: email,
subject: tieuDe,
htmlBody: noiDungEmail,
inlineImages: {
googleLogo: googleLogoBlob,
danhBaLogo: danhBaLogoBlob
}
});
range.getCell(k, 7).setValue('Đã gửi lúc: ' + date);
} catch (err) {
ui.alert('Xảy ra lỗi, hãy kiểm tra lại \\n' + err);
}
}
ui.alert('Đã gửi xong ' + (lastRow - 1) + ' email');
}