Google sheet App Script | Multi Data entry Form – Thêm,Xóa,Cập nhật,Đính kèm file, Xuất pdf và Excel
Giáo án link (giaoan.link) chia sẻ đến các bạn một project mới về Google sheet App Script “Multi Data entry Form – Thêm,Xóa,Cập nhật,Đính kèm file, Xuất pdf và Excel“. Bạn có thê xem video hướng dẫn bên dưới cùng code apps script chia sẻ dưới đây.
Các bài học excel khác:
- Googlesheet Apps script Webapp | Tạo trang trắc nghiệm online như Quiz
- Google sheet Apps script | Trang trắc nghiệm Quiz – Cập nhật câu hỏi, trả lời, thời gian đếm ngược
- Google sheets | Number to text, Hàm đọc số thành chữ Ứng dụng taoh hóa đơn, phiếu chi.
- Googlesheet Apps script Webapp | Tạo mã QR Code từ nội dung nhập vào – QR Code Generator
- Google sheet apps script | Dropdown đơn giản lấy dữ liệu từ google sheet – Simple dropdown
- Apps script Webapp CSS – HTML – JS | Tạo ứng dụng Ghi chú trên nền tảng webapp – website
- Google sheet Apps script | Hàm setTimeout định thời gian xóa các trường Input khi click Button
- Apps script Webapp | Sử dụng CSS tạo hiệu ứng sóng nước – Trang trí đẹp mắt cho web và blog.
- Google sheet Apps script Tiện ích tạo mã vạch (barcode) trên webapp
- Google sheet, apps script, webapp | Load và Hiển thị biểu đồ theo năm chọn từ List box
Mã file “Code.gs”
// https://youtube.com/netmediacctv
// *********************
var strDuplicate = "NO" ;
var ssdata = SpreadsheetApp.openById("PASTE YOUR SPREADSHEET ID");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Form1"); //Form Sheet
//=====================================================================
//Search Emp. Number
var SPREADSHEET_NAME = "Data";
var SEARCH_COL_IDX = 0;
var RETURN_COL_IDX = 0;
function searchStr() {
var str = formSS.getRange("C3").getValue();
var values = ssdata.getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
formSS.getRange("I1").setValue("NO") ;
for (var i = 0; i < values.length; i++) {
var row = values[i];
if (row[SEARCH_COL_IDX] == str) {
formSS.getRange("C8").setValue(row[0]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C10").setValue(row[1]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C12").setValue(row[2]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F8").setValue(row[3]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F10").setValue(row[4]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C14").setValue(row[5]);
formSS.getRange("C14:G16").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C18").setValue(row[6]);
formSS.getRange("C18:G18").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
return row[RETURN_COL_IDX];
}
}
}
//======================================================================
// Search by Emp. Name
//--------------------
function searchStrName() {
var str = formSS.getRange("J1").getValue();
var values = ssdata.getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
formSS.getRange("I1").setValue("NO") ;
for (var i = 0; i < values.length; i++) {
var row = values[i];
if (row[SEARCH_COL_IDX] == str) {
formSS.getRange("C3").setValue(row[0]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null) ;
formSS.getRange("C8").setValue(row[0]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null) ;
formSS.getRange("C10").setValue(row[1]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C12").setValue(row[2]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F8").setValue(row[3]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F10").setValue(row[4]).setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C14").setValue(row[5]);
formSS.getRange("C14:G16").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C18").setValue(row[6]);
formSS.getRange("C18:G18").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
return row[RETURN_COL_IDX];
}
}
}
//=======================================================
//Convert to proper
function proper(string) {
return string
.toLowerCase()
.split(" ")
.map((val) => val[0].toUpperCase() + val.slice(1))
.join(" ");
}
//=======================================================
// Creat New Record
function clearCell() {
formSS.getRange("I1").setValue("YES") ;
formSS.getRange("C8").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C10").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C12").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F8").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F10").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C14").clear();
formSS.getRange("C14:G16").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C18").clear();
formSS.getRange("C18:G18").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
return true ;
}
//=================================================================
// Save New Data or Update Data
//------------------------------
function saveData1() {
var datasheet = ssdata.getSheetByName("Data"); //Data Sheet
// check add New or Update record
var strYN = formSS.getRange("I1").getValue() ;
var checkName = formSS.getRange("C10").getValue();
if (strYN == "YES") {
// Save New
//Input Values
if(checkName==""){
SpreadsheetApp.getUi().alert(' "Vui lòng nhập tên Nhân viên!"');
} else{
var values = [[formSS.getRange("C8").getValue(),
proper(formSS.getRange("C10").getValue()),
formSS.getRange("C12").getValue(),
formSS.getRange("F8").getValue(),
formSS.getRange("F10").getValue(),
formSS.getRange("C14").getValue(),
formSS.getRange("C18").getValue()]];
}
// Check Duplicate value
checkDuplicate() ;
if (strDuplicate == "YES") {
SpreadsheetApp.getUi().alert(' "Mã nhân viên đã tồn tại, vui lòng nhập mã mới!"');
} else {
datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 7).setValues(values);
SpreadsheetApp.getUi().alert(' "Dữ liệu mới được ghi cho #' + formSS.getRange("C8").getValue() +' "');
formSS.getRange("C8").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C10").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C12").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F8").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F10").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C14").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C14:G16").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C18").clear();
formSS.getRange("C18:G18").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
strNew1 = "NO" ;
formSS.getRange("I1").setValue("NO") ;
}
} else {
// Update data
var SEARCH_COL_IDX = 0;
var RETURN_COL_IDX = 0;
var datasheet = ssdata.getSheetByName("Data"); //Data Sheet
var str = formSS.getRange("C8").getValue();
var values = ssdata.getSheetByName("Data").getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values[i];
if (row[SEARCH_COL_IDX] == str) {
var INT_R = i+1
var values1 = [[formSS.getRange("C8").getValue(),
proper(formSS.getRange("C10").getValue()),
formSS.getRange("C12").getValue(),
formSS.getRange("F8").getValue(),
formSS.getRange("F10").getValue(),
formSS.getRange("C14").getValue(),
formSS.getRange("C18").getValue()]];
datasheet.getRange(INT_R, 1, 1, 7).setValues(values1);
SpreadsheetApp.getUi().alert('Dữ liệu đã được cập nhật!');
return row[RETURN_COL_IDX];
}
}
}
}
//-------------------------------------------
// Check Duplicate value
//------------------------
function checkDuplicate() {
var str = formSS.getRange("C8").getValue();
var values = ssdata.getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values[i];
if (row[SEARCH_COL_IDX] == str) {
strDuplicate = "YES" ;
return row[RETURN_COL_IDX];
}
}
}
//=====================================================================
// Delete Record
// --------------
function rowDelete() {
// var formSS = ss.getSheetByName("Form1"); //Form Sheet
var datasheet = ssdata.getSheetByName("Data"); //Data Sheet
var ui = SpreadsheetApp.getUi();
var response = ui.alert(
'Bạn chắc xóa?',
ui.ButtonSet.YES_NO);
// Process the user's response.
if (response == ui.Button.YES) {
var str = formSS.getRange("C8").getValue();
var values = ssdata.getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values[i];
if (row[SEARCH_COL_IDX] == str) {
var INT_R = i+1
datasheet.deleteRow(INT_R) ;
formSS.getRange("C8").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C10").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C12").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F8").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("F10").clear().setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C14").clear();
formSS.getRange("C14:G16").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
formSS.getRange("C18").clear();
formSS.getRange("C18:G18").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
return row[RETURN_COL_IDX];
}
}
}
}
//=============================================================================
// Create pdf
// ------------------------------
function generatePdf() {
SpreadsheetApp.flush();
var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Get active spreadsheet.
var sheetName = "Data" ;
var pdfName = sheetName + ".pdf"; // Set the output filename as SheetName.
var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents(); // Get folder containing spreadsheet to save pdf in.
if (parents.hasNext()) {
var folder = parents.next();
} else {
folder = DriveApp.getRootFolder();
}
var theBlob = createblobpdf(sheetName, pdfName);
var newFile = folder.createFile(theBlob);
var dl = newFile.getDownloadUrl() ;
var html=Utilities.formatString('<div style="text-align: center; font-size: 20px; color: red;"><a href="%s">TẢI FILE NÀY VỀ</a></div>',dl);
var ui=HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(ui, 'THÔNG TIN NHÂN VIÊN (PDF)');
var files = folder.getFilesByName(pdfName);
while (files.hasNext()) {
files.next().setTrashed(true);
}
}
//----------------------------------------------------------------------------
function createblobpdf(sheetName, pdfName) {
var sourceSpreadsheet = SpreadsheetApp.getActive();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId() + '/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
+ '&size=A4' // paper size legal / letter / A4
+ '&portrait=false' // orientation, false for landscape
+ '&fitw=true' // fit to page width, false for actual size
+ '&sheetnames=true&printtitle=false' // hide optional headers and footers
+ '&pagenum=RIGHT&gridlines=false' // hide page numbers and gridlines
+ '&fzr=true' // repeat row headers (frozen rows) on each page
+ '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
+ '&vertical_alignment=TOP' //TOP/MIDDLE/BOTTOM
+ '&gid=' + sourceSheet.getSheetId(); // the sheet's Id
var token = ScriptApp.getOAuthToken();
// request export url
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
var theBlob = response.getBlob().setName(pdfName);
return theBlob;
};
//====================================================================================
//Create Excel file to Export
//---------------------------
function generateexcel() {
SpreadsheetApp.flush();
var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Get active spreadsheet
var sheetName = "Data" ;
var excelName = sheetName + ".xlsx"; // Set the output filename as SheetName.
var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents(); // Get folder containing spreadsheet to save xlsx in.
if (parents.hasNext()) {
var folder = parents.next();
} else {
folder = DriveApp.getRootFolder();
}
var theBlob = createblobexcel(sheetName, excelName);
var newFile = folder.createFile(theBlob);
var dl = newFile.getDownloadUrl() ;
var html=Utilities.formatString('<a href="%s">Tải file này về</a>',dl);
var ui=HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(ui, 'THÔNG TIN NHÂN VIÊN (EXCEL)');
var files = folder.getFilesByName(excelName);
while (files.hasNext()) {
files.next().setTrashed(true);
}
}
//-------------------------------------------------------------
function createblobexcel(sheetName, excelName) {
var sourceSpreadsheet = SpreadsheetApp.getActive();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId() + '/export?exportFormat=xlsx&format=xlsx' // export as pdf / csv / xls / xlsx
+ '&size=A4' // paper size legal / letter / A4
+ '&portrait=false' // orientation, false for landscape
+ '&fitw=true' // fit to page width, false for actual size
+ '&sheetnames=true&printtitle=false' // hide optional headers and footers
+ '&pagenum=RIGHT&gridlines=false' // hide page numbers and gridlines
+ '&fzr=false' // do not repeat row headers (frozen rows) on each page
+ '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
+ '&vertical_alignment=TOP' //TOP/MIDDLE/BOTTOM
+ '&gid=' + sourceSheet.getSheetId(); // the sheet's Id
var token = ScriptApp.getOAuthToken();
// request export url
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
var theBlob = response.getBlob().setName(excelName);
return theBlob;
};
//=================================================================================
// Upload File
// ---------------------------------
function openAttachmentDialog1() {
var html = HtmlService.createHtmlOutputFromFile('UploadFile');
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
.showModalDialog(html, 'Upload File');
}
//-----------------------------------
function saveFile(obj) {
var blob = Utilities.newBlob(Utilities.base64Decode(obj.data), obj.mimeType, obj.fileName);
var file = DriveApp.getFolderById("PASTE FODER ID TO UPLOAD IMAGES").createFile(blob); // Shared folder id to upload file
formSS.getRange("C18").setValue(file.getUrl()) ;
formSS.getRange("C18:G18").setBackground("#cccccc").setBorder(true, true, true, true, false, false, "#f46524",null);
return file.getId();
}Mã file “UploadFile.html”
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<script>
function getFiles() {
document.getElementById("uploadButton").disabled = true;
const progressText = document.getElementById("progress");
const f = document.getElementById('files');
var uploadCompletedCount = 0;
progressText.innerHTML = "Uploading file " + (uploadCompletedCount + 1) + "/" + [...f.files].length + "";
[...f.files].forEach((file, i) => {
const fr = new FileReader();
fr.onload = (e) => {
const data = e.target.result.split(",");
const obj = {fileName: f.files[i].name, mimeType: data[0].match(/:(\w.+);/)[1], data: data[1]};
google.script.run.withSuccessHandler((id) => {
uploadCompletedCount++;
progressText.innerHTML = "Upload completed";
if (uploadCompletedCount >= [...f.files].length){
google.script.host.close();
}
else{
progressText.innerHTML = "Uploading file " + (uploadCompletedCount + 1) + "/" + [...f.files].length + "";
}
}).saveFile(obj);
}
fr.readAsDataURL(file);
});
}
</script>
<body>
<!-- <input type="file" name="upload" id="files" multiple/> -->
<input type="file" name="upload" id="files"/> <!-- I expect to up load only one file -->
<input type='button' id="uploadButton" value='Upload' onclick='getFiles()' class="action">
<br><br>
<div id="progress"> </div>
</body>
</html>Mã file “config” dùng trong cấu hình – xem video để biết cách sử dụng
//Sheet "List" HO VA TEN NHAN VIEN
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_AbFtmlZaZJawJV3SkI5oEnUEzPcorENmZbS8TkG3sA/edit?usp=sharing";"Data!B3:B10000")
---------------------------------------------------------------------------------
//Sheet "List" MA NHAN VIEN
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_AbFtmlZaZJawJV3SkI5oEnUEzPcorENmZbS8TkG3sA/edit?usp=sharing";"Data!A3:A10000")
-------------------------------------------------------------------
//Sheet "Form1" Cell "J1"
=VLOOKUP(Form1!C5;List!A2:B21;2;FALSE)
------------------------------------------
//Get link Image
=IFERROR(ARRAYFORMULA(CONCAT("https://drive.google.com/thumbnail?id=";(REGEXEXTRACT(C18;"([-\w]{25,})"))));)
----------------------------
//Show Image
=image(SUBSTITUTE(trim(K1);"open?id";"uc?export=download&id"))