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

giaoanppt

1 năm trước

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"))

Mã file “Code.gs”

Mã file “UploadFile.html”

Mã file “config” dùng trong cấu hình – xem video để biết cách sử dụng

Video hướng dẫn