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:

Google sheet apps script | Scan QR code – Filter and get data table display on webapp
Google sheet apps script Filter to get data to display on webapp, fill background color for data row
Google sheet Apps script Webapp | Project Quản lý đơn hàng – Cập nhật sản phẩm – In phiếu kiểm soát
Google sheet Apps script Webapp | Tạo QR Code động – Tự động load mã QR mới khi nội dung mã hóa đổi
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

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