Học Ms excelKỹ năng vi tính

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ể






giaoanppt

Giaoan.link trang chia sẽ giáo án điện tử, bài giảng powerpoint, template powerpoint, nguyên liệu làm bài giảng, tài liệu, biểu mẫu miễn phí!