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

Xem clip hướng dẫn cụ thể