Google Sheet Form – Thêm mới – Tìm kiếm – Cập nhật – Xóa, sử dụng App Script
Trong bài này, giaoan.link chia sẻ đến các bạn cách tạo một Data entry Google sheet form với các chức năng: tạo mới, tìm kiếm, cập nhật và xóa dữ liệu.
Chúng ta sẽ thiết kế 1 form nhập liệu trên sheet google và sử dụng app script để chèn mã thực thi. Dữ liệu sẽ được ghi vào một sheet riêng.
Code trong file code.gs
/*Source code: thedatalabs.org
Custom code: Youtube.com/NetmediaCCTV
Website: http://giaoan.link
*/
///////// Function to submit the data to Database sheet//////////////
function submitData() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
// close the dialog by clicking the close button in its title bar.
var response = ui.alert("Submit", 'Bạn muốn ghi dữ liệu này?',ui.ButtonSet.YES_NO);
// Checking the user response and proceed with clearing the form if user selects Yes
if (response == ui.Button.NO)
{return;//exit from this function
}
//Validating the entry. If validation is true then proceed with transferring the data to Database sheet
if (validateEntry()==true) {
var blankRow=datasheet.getLastRow()+1; //identify the next blank row
datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("F8").getValue()); // Name
datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("F10").getValue()); //Gender
datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("F12").getValue()); // Email ID
datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("F14").getValue()); //Department
datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("F16").getValue());// Address
// date function to update the current date and time as submittted on
datasheet.getRange(blankRow, 6).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On
//get the email address of the person running the script and update as Submitted By
datasheet.getRange(blankRow, 7).setValue(Session.getActiveUser().getEmail()); //Submitted By
ui.alert(' "Dữ liệu mới được tạo cho - Nhân viên: ' + shUserForm.getRange("F8").getValue() +' "');
//Clearnign the data from the Data Entry Form
shUserForm.getRange("C8").clear();
shUserForm.getRange("C10").clear();
shUserForm.getRange("C12").clear();
shUserForm.getRange("C14").clear();
shUserForm.getRange("C16").clear();
}
}
//////////Function to edit the record /////////////////////////////
function editRecord() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
// close the dialog by clicking the close button in its title bar.
var response = ui.alert("Thông báo", 'Bạn muốn chỉnh sửa dữ liệu này?',ui.ButtonSet.YES_NO);
// Checking the user response and proceed with clearing the form if user selects Yes
if (response == ui.Button.NO)
{return;//exit from this function
}
var str = shUserForm.getRange("F4").getValue();
var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable
var valuesFound=false; //variable to store boolean value to validate whether values found or not
for (var i = 0; i < values.length; i++)
{
var rowValue = values[i]; //declaraing a variable and storing the value
//checking the first value of the record is equal to search item
if (rowValue[0] == str) {
var iRow = i+1; //identify the row number
datasheet.getRange(iRow, 1).setValue(shUserForm.getRange("F8").getValue()); //Employee Name
datasheet.getRange(iRow, 2).setValue(shUserForm.getRange("F10").getValue()); //Gender
datasheet.getRange(iRow, 3).setValue(shUserForm.getRange("F12").getValue()); // Email ID
datasheet.getRange(iRow, 4).setValue(shUserForm.getRange("F14").getValue()); //Department
datasheet.getRange(iRow, 5).setValue(shUserForm.getRange("F16").getValue());// Address
// date function to update the current date and time as submittted on
datasheet.getRange(iRow, 6).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On
//get the email address of the person running the script and update as Submitted By
datasheet.getRange(iRow, 7).setValue(Session.getActiveUser().getEmail()); //Submitted By
ui.alert(' "Dữ liệu được cập nhật cho - Nhân viên: ' + shUserForm.getRange("F8").getValue() +' "');
//Clearnign the data from the Data Entry Form
shUserForm.getRange("C4").clear();
shUserForm.getRange("C8").clear();
shUserForm.getRange("C10").clear();
shUserForm.getRange("C12").clear();
shUserForm.getRange("C14").clear();
shUserForm.getRange("C16").clear();
valuesFound=true;
return; //come out from the search function
}
}
if(valuesFound==false){
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
ui.alert("No record found!");
}
}
///////////Function to delete the record/////////////////////
function deleteRow() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
// close the dialog by clicking the close button in its title bar.
var response = ui.alert("Thông báo", 'Bạn có chắc muốn xóa dữ liệu này?',ui.ButtonSet.YES_NO);
// Checking the user response and proceed with clearing the form if user selects Yes
if (response == ui.Button.NO)
{return;//exit from this function
}
var str = shUserForm.getRange("F4").getValue();
var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable
var valuesFound=false; //variable to store boolean value to validate whether values found or not
for (var i = 0; i < values.length; i++)
{
var rowValue = values[i]; //declaraing a variable and storing the value
//checking the first value of the record is equal to search item
if (rowValue[0] == str) {
var iRow = i+1; //identify the row number
datasheet.deleteRow(iRow) ; //deleting the row
//message to confirm the action
ui.alert(' "Dữ liệu của Nhân viên:' + shUserForm.getRange("F4").getValue() +' đã được xóa"');
//Clearing the user form
shUserForm.getRange("C4").clear() ;
shUserForm.getRange("C8").clear() ;
shUserForm.getRange("C10").clear() ;
shUserForm.getRange("C12").clear() ;
shUserForm.getRange("C14").clear() ;
shUserForm.getRange("C16").clear() ;
valuesFound=true;
return; //come out from the search function
}
}
if(valuesFound==false){
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
ui.alert("No record found!");
}
}
///////Function to Clear the User Form////////
function clearForm()
{
var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm = myGoogleSheet.getSheetByName("User Form"); //declare a variable and set with the User Form worksheet
//to create the instance of the user-interface environment to use the alert features
var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
// close the dialog by clicking the close button in its title bar.
var response = ui.alert("Thông báo", 'Bạn có muốn xóa các trường của form?',ui.ButtonSet.YES_NO);
// Checking the user response and proceed with clearing the form if user selects Yes
if (response == ui.Button.YES)
{
shUserForm.getRange("C4").clear(); //Search Field
shUserForm.getRange("C8").clear(); // Employee Name
shUserForm.getRange("C10").clear(); // Gender
shUserForm.getRange("C12").clear(); // Email ID
shUserForm.getRange("C14").clear(); //Department
shUserForm.getRange("C16").clear();//Address
//Assigning white as default background color
shUserForm.getRange("C4").setBackground('#FFFFFF');
shUserForm.getRange("C8").setBackground('#FFFFFF');
shUserForm.getRange("C10").setBackground('#FFFFFF');
shUserForm.getRange("C12").setBackground('#FFFFFF');
shUserForm.getRange("C14").setBackground('#FFFFFF');
shUserForm.getRange("C16").setBackground('#FFFFFF');
return true ;
}
}
//////////Function to Search the record /////////////////////
function searchRecord() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet
var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet
var str = shUserForm.getRange("F4").getValue();
var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable
var valuesFound=false; //variable to store boolean value
for (var i = 0; i < values.length; i++)
{
var rowValue = values[i]; //declaraing a variable and storing the value
//checking the first value of the record is equal to search item
if (rowValue[0] == str) {
shUserForm.getRange("C8").setValue(rowValue[0]);
shUserForm.getRange("C10").setValue(rowValue[1]);
shUserForm.getRange("C12").setValue(rowValue[2]);
shUserForm.getRange("C14").setValue(rowValue[3]);
shUserForm.getRange("C16").setValue(rowValue[4]);
return; //come out from the search function
}
}
if(valuesFound==false){
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
ui.alert("Không tìm thấy dữ liệu!");
}
}
/////Declare a function to validate the entry made by user in UserForm//////
function validateEntry(){
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm = myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet
//to create the instance of the user-interface environment to use the messagebox features
var ui = SpreadsheetApp.getUi();
//Assigning white as default background color
shUserForm.getRange("C8").setBackground('#FFFFFF');
shUserForm.getRange("C10").setBackground('#FFFFFF');
shUserForm.getRange("C12").setBackground('#FFFFFF');
shUserForm.getRange("C14").setBackground('#FFFFFF');
shUserForm.getRange("C16").setBackground('#FFFFFF');
//Validating Employee Name
if(shUserForm.getRange("C8").isBlank()==true){
ui.alert("Vui lòng điền tên nhân viên.");
shUserForm.getRange("C8").activate();
shUserForm.getRange("C8").setBackground('#FF0000');
return false;
}
//Validating Gender
else if(shUserForm.getRange("C10").isBlank()==true){
ui.alert("Vui lòng chọn giới tính!");
shUserForm.getRange("C10").activate();
shUserForm.getRange("C10").setBackground('#FF0000');
return false;
}
//Validating Email ID
else if(shUserForm.getRange("C12").isBlank()==true){
ui.alert("Vui lòng điền địa chỉ mail!");
shUserForm.getRange("C12").activate();
shUserForm.getRange("C12").setBackground('#FF0000');
return false;
}
//Validating Department
else if(shUserForm.getRange("C14").isBlank()==true){
ui.alert("Vui lòng chọn Phòng ban từ danh sách thả xuống!");
shUserForm.getRange("C14").activate();
shUserForm.getRange("C14").setBackground('#FF0000');
return false;
}
//Validating Address
else if(shUserForm.getRange("C16").isBlank()==true){
ui.alert("Vui lòng điền địa chỉ");
shUserForm.getRange("C16").activate();
shUserForm.getRange("C16").setBackground('#FF0000');
return false;
}
return true;
}