Google sheet Apps script | QR code Scaner – Quét thông tin mã QR và lưu thông tin trên google sheet

16/05/2024 giaoanppt 841 Lượt xem

Giaoan.link chia sẻ tiếp đến các bạn về Google sheet Apps script “QR code Scaner – Quét thông tin mã QR và lưu thông tin trên google sheet”. Project này sẽ xây dựng dạng webapp có thể truy cập trên máy tính bảng và điện thoại. Webapp này có thể dùng camera để quét mã QR hoặc load một ảnh QR có trên máy để lấy thông tin và điền thêm thông tin sau đó gửi và lưu lại trên sheet của google.

Bạn có thể xem video hướng dẫn theo link bên dưới và code appscript để bạn tham khảo.

Các bài học về appscript khác:

CLICK CHUYỂN HƯỚNG XEM VIDEO TRÊN YOUTUBE

Mã file “code.gs”

/**
 * This web app uses the html5-qrcode library from GitHub: https://github.com/mebjas/html5-qrcode.
 * The html5-qrcode library is used to scan QR codes from the user's webcam.
 */

//Constants
const DATASHEET = "Data";


function doGet() {
  let template = HtmlService.createTemplateFromFile('Index');
  let html = template.evaluate().setTitle('QR Code Scanner');

  html.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  html.addMetaTag('viewport', 'width=device-width, initial-scale=1');

  return html;
}


function processForm(formObject) {
  const ss = SpreadsheetApp.getActive();
  const dataSheet = ss.getSheetByName(DATASHEET);
  
  dataSheet.appendRow([
    new Date().toLocaleString(),
    formObject.product_name,
    formObject.product_category,
    formObject.quantity,
    formObject.price,
    formObject.productCode
  ]);
}

/**
 * INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES
 */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

Mã file “Index.html”

<!doctype html>
<html lang="en">
<head>
  <!-- Required meta tags -->
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <?!= include('CSS'); ?>
  <!-- See CSS.html file -->
</head>

<body class="bg-black text-light">
  <nav class="navbar sticky-top navbar-dark bg-primary">
    <div class="container-fluid">
      <a class="navbar-brand mx-auto" href="#"><span><i class="bi bi-qr-code-scan"></i></span> Quét mã QR code </a>
    </div>
  </nav>
    <div class="container" style="border: solid 2px #aafe7d; border-radius: 10px" >
    <?!= include('Form'); ?>
    <!-- See Form.html file -->
  </div>
  <?!= include('JavaScript'); ?>
  <!-- See JavaScript.html file -->
  <?!= include('QrReaderJS'); ?>
  <!-- See QrReaderJS.html file -->
</body>

</html>

Mã file “CSS.html”

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.9.1/font/bootstrap-icons.css">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet"
  integrity="sha384-KK94CHFLLe+nY2dmCWGMq91rCGa5gtU4mk92HdvYe+M/SXH301p5ILy+dN9+nJOZ" crossorigin="anonymous">

<style>
  #qr-reader {
    /* Default width for all devices */
    width: 100%;
    box-sizing: border-box;
    /* Include padding and border in width calculation */
  }

  @media only screen and (max-width: 600px) {
    #qr-reader {
      width: 100%;
    }
  }
</style>

Mã file “Form.html”

<div id="qr-reader" class="mt-4" style="position: relative; padding: 0px; border: none;"></div>
<div id="qr-reader-results"></div>
<br>
<form id="InventoryForm" onsubmit="handleFormSubmit(this)">
  <div class="mb-3 input-group">
    <span class="input-group-text bg-primary text-white"><i class="bi bi-link"></i></span>
    <input type="text" class="form-control" id="productCode" name="productCode" placeholder="Scan QR/Bar Code" readonly>
  </div>

  <div class="mb-3 input-group">
    <span class="input-group-text bg-primary text-white"><i class="bi bi-box-fill"></i></span>
    <input type="text" class="form-control" id="productName" name="product_name" placeholder="Tên sản phẩm">
  </div>
  <div class="mb-3 input-group">
    <!-- <label for="productCategory" class="form-label">Product Category:</label> -->
    <span class="input-group-text bg-primary text-white"><i class="bi bi-tags-fill"></i></span>
    <select class="form-select" id="productCategory" name="product_category">
          <option disabled selected>Chọn Danh Mục</option>
          <option value="electronics">Đồ điện tử</option>
          <option value="clothing">Thời trang</option>
          <option value="books">Sách</option>
          <option value="other">Loại khác</option>
        </select>
  </div>
  <div class="mb-3 input-group">
    <span class="input-group-text bg-primary text-white"><i class="bi bi-123"></i></span>
    <input type="number" class="form-control" id="quantity" name="quantity" placeholder="Số lượng">
  </div>
  <div class="mb-3 input-group">
    <span class="input-group-text bg-primary text-white"><i class="bi bi-currency-dollar"></i></span>
    <input type="number" step="0.01" class="form-control" id="price" name="price" placeholder="Giá thành">
  </div>
  <button type="submit" class="btn btn-warning mb-3 w-100 fw-bold">Submit</button>
</form>

Mã file “JavaScript.html”

<script>
  window.addEventListener("load", functionInit, true); 

  //Initialize functions onload
  function functionInit(){
    preventFormSubmit();
  };

  // Prevent forms default behaviour (Prevent reloading the page)
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i++) {
      forms[i].addEventListener('submit', function(event) {
      event.preventDefault();
      });
    }
  } 

  /**
   * The handleFormSubmit() function passes the formObject to the processForm 
   * function in the Code.gs file
   */
  function handleFormSubmit(formObject) {
    // Get the QR data from the div
    google.script.run.processForm(formObject);
    const resultContainer = document.getElementById('qr-reader-results');
    resultContainer.innerHTML = '';
    document.getElementById("InventoryForm").reset();
  }
</script>

Mã file “QrReaderJS.html”

<!--
  This code uses the html5-qrcode library from GitHub: https://github.com/mebjas/html5-qrcode
  Example Link: https://gist.github.com/mebjas/729c5397506a879ec704075c8a5284e8
 -->

<script src="https://unpkg.com/html5-qrcode@2.3.8/html5-qrcode.min.js" type="text/javascript"></script>

<script>
  function docReady(fn) {
    // see if DOM is already available
    if (document.readyState === "complete" || document.readyState === "interactive") {
        // call on next available tick
        setTimeout(fn, 1);
    } else {
        document.addEventListener("DOMContentLoaded", fn);
    }
  } 

  docReady(function() {
      var resultContainer = document.getElementById('qr-reader-results');
      var lastResult, countResults = 0;
      
      var html5QrcodeScanner = new Html5QrcodeScanner(
          "qr-reader", { fps: 10, qrbox: 250 });
      
      function onScanSuccess(decodedText, decodedResult) {
          if (decodedText !== lastResult) {
              ++countResults;
              lastResult = decodedText;
              console.log(`Scan result = ${decodedText}`, decodedResult);
  
              resultContainer.innerHTML += `<div class="p-3 mb-2 bg-success text-white">[${countResults}] - ${decodedText}</div>`;
              
              //Assign the scanned code to productCode field
              const productCode = document.getElementById("productCode");
              productCode.value = decodedText;
          }
      }
      
      // Optional callback for error, can be ignored.
      function onScanError(qrCodeError) {
          // This callback would be called in case of qr code scan error or setup error.
          // You can avoid this callback completely, as it can be very verbose in nature.
      }
      
      html5QrcodeScanner.render(onScanSuccess, onScanError);
  });
</script>