Cách Tạo Web App Từ Google Sheets Bằng Apps Script [Hướng Dẫn 2026]

Web App Từ Google Sheets - Ứng Dụng Web Miễn Phí Cho Mọi Người

Bạn có biết rằng Google Sheets không chỉ là bảng tính? Với Google Apps Script, bạn có thể biến bất kỳ spreadsheet nào thành một ứng dụng web hoàn chỉnh - có giao diện HTML đẹp mắt, xử lý form, hiển thị dữ liệu, thậm chí xây dựng cả hệ thống CRUD (Create, Read, Update, Delete) - và tất cả đều miễn phí hosting, miễn phí domain, miễn phí SSL.

Apps Script Web App là giải pháp tuyệt vời cho các tình huống:

• Form thu thập dữ liệu đẹp hơn Google Forms, tuỳ chỉnh 100% giao diện
• Dashboard nội bộ cho team tra cứu thông tin từ Sheets
• Portal khách hàng để theo dõi đơn hàng, tra cứu bảo hành
• Hệ thống chấm điểm, đánh giá với giao diện thân thiện
• Landing page đơn giản với form liên hệ lưu vào Sheets
• Internal tools cho doanh nghiệp mà không cần thuê developer

Phan 1: Cau Truc Du An Web App

Mot du an Web App trong Apps Script gom 3 thanh phan chinh:

Apps Script Project/
  |-- Code.gs          (Server-side: doGet, doPost, xu ly data)
  |-- Page.html        (Client-side: giao dien nguoi dung)
  |-- Stylesheet.html  (CSS styles)
  |-- JavaScript.html  (Client-side JavaScript)
  |-- appsscript.json  (Manifest - cau hinh project)

Code.gs la file server-side, viet bang JavaScript (chay tren server Google). Day la noi ban dinh nghia cac ham xu ly du lieu, doc/ghi Google Sheets, va tra ve HTML cho nguoi dung.

HTML files la giao dien nguoi dung. Ban co the tao nhieu file .html cho cac trang khac nhau. CSS va JavaScript cua client cung duoc viet trong file .html rieng (vi Apps Script khong ho tro file .css hay .js rieng).

appsscript.json la file cau hinh, dinh nghia quyen truy cap (scopes), runtime version, va cac thiet lap khac.

Phan 2: Setup Project - Buoc Dau Tien

Buoc 1: Mo Google Sheets va tao Apps Script project

1. Mo Google Sheets bat ky (hoac tao moi tai sheets.new)
2. Vao menu Extensions > Apps Script
3. Cua so Script Editor se mo ra voi file Code.gs mac dinh
4. Doi ten project (click vao "Untitled project" o goc trai tren)

Buoc 2: Tao file HTML

1. Trong Script Editor, click dau + ben canh "Files"
2. Chon HTML
3. Dat ten file (VD: "Page", "Index", "Dashboard"...)
4. File .html se duoc tao voi template HTML5 co ban

Phan 3: Code.gs - Ham doGet va doPost

doGet() va doPost() la 2 ham dac biet trong Apps Script. Khi ban deploy Web App:

• doGet(e) duoc goi khi nguoi dung truy cap URL bang trinh duyet (HTTP GET)
• doPost(e) duoc goi khi co POST request (VD: submit form, webhook)

3.1 doGet - Hien thi giao dien

// Code.gs - Ham doGet co ban
function doGet(e) {
  // Tra ve file HTML lam giao dien
  return HtmlService.createHtmlOutputFromFile('Page')
    .setTitle('Ung Dung Quan Ly')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

// Phien ban nang cao voi Template (cho phep truyen du lieu vao HTML)
function doGet(e) {
  var template = HtmlService.createTemplateFromFile('Page');

  // Truyen du lieu tu server vao HTML template
  template.pageTitle = 'Dashboard Quan Ly Don Hang';
  template.userName = Session.getActiveUser().getEmail();
  template.currentDate = Utilities.formatDate(new Date(), 'Asia/Ho_Chi_Minh', 'dd/MM/yyyy HH:mm');

  return template.evaluate()
    .setTitle('Dashboard')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

// Xu ly tham so URL (VD: ?page=dashboard&id=123)
function doGet(e) {
  var page = e.parameter.page || 'home';
  var template;

  if (page === 'dashboard') {
    template = HtmlService.createTemplateFromFile('Dashboard');
  } else if (page === 'form') {
    template = HtmlService.createTemplateFromFile('Form');
  } else {
    template = HtmlService.createTemplateFromFile('Home');
  }

  return template.evaluate()
    .setTitle('My App - ' + page)
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

3.2 doPost - Nhan du lieu tu form hoac webhook

// Code.gs - Ham doPost xu ly form submit
function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Data');

    // Luu du lieu vao sheet
    sheet.appendRow([
      new Date(),
      data.name || '',
      data.email || '',
      data.phone || '',
      data.message || ''
    ]);

    return ContentService.createTextOutput(JSON.stringify({
      status: 'success',
      message: 'Du lieu da duoc luu thanh cong!'
    })).setMimeType(ContentService.MimeType.JSON);

  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({
      status: 'error',
      message: error.toString()
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

3.3 HtmlService - Tao giao dien

HtmlService cung cap 3 cach tao HTML:

// Cach 1: Tu file HTML
HtmlService.createHtmlOutputFromFile('Page')

// Cach 2: Tu string (cho HTML don gian)
HtmlService.createHtmlOutput('<h1>Xin chao!</h1>')

// Cach 3: Tu Template (KHUYEN DUNG - cho phep truyen du lieu)
var template = HtmlService.createTemplateFromFile('Page');
template.data = getSheetData();
template.evaluate()

Template syntax cho phep nhung code Apps Script vao HTML:

<!-- Page.html -->
<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
    .card { border: 1px solid #ddd; border-radius: 8px; padding: 16px; margin: 8px 0; }
    .btn { background: #1a73e8; color: white; border: none; padding: 10px 20px; border-radius: 6px; cursor: pointer; }
    .btn:hover { background: #1557b0; }
  </style>
</head>
<body>
  <h1>Dashboard</h1>
  <p>Xin chao! Hom nay la: <?= currentDate ?></p>

  <!-- Vong lap hien thi du lieu tu Sheets -->
  <? var data = getData(); ?>
  <? for (var i = 0; i < data.length; i++) { ?>
    <div class="card">
      <h3><?= data[i][0] ?></h3>
      <p>Email: <?= data[i][1] ?></p>
      <p>Trang thai: <?= data[i][2] ?></p>
    </div>
  <? } ?>

</body>
</html>

3.4 Include Pattern - Tach CSS va JS rieng

De code sach se, tach CSS va JavaScript ra file rieng roi include vao:

// Code.gs - Ham include file
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

// Page.html - Su dung include
// <!DOCTYPE html>
// <html>
// <head>
//   <base target="_top">
//   <?!= include('Stylesheet') ?>
// </head>
// <body>
//   <h1>My App</h1>
//   <div id="content"></div>
//   <?!= include('JavaScript') ?>
// </body>
// </html>

<!-- Stylesheet.html -->
<style>
  * { box-sizing: border-box; margin: 0; padding: 0; }
  body {
    font-family: 'Segoe UI', Arial, sans-serif;
    background: #f5f5f5;
    color: #333;
  }
  .container { max-width: 960px; margin: 0 auto; padding: 20px; }
  .card {
    background: white;
    border-radius: 12px;
    box-shadow: 0 2px 8px rgba(0,0,0,0.1);
    padding: 20px;
    margin-bottom: 16px;
  }
  .btn-primary {
    background: #1a73e8;
    color: white;
    border: none;
    padding: 12px 24px;
    border-radius: 8px;
    font-size: 16px;
    cursor: pointer;
    transition: background 0.2s;
  }
  .btn-primary:hover { background: #1557b0; }
  .form-group { margin-bottom: 16px; }
  .form-group label { display: block; font-weight: 600; margin-bottom: 4px; }
  .form-group input, .form-group textarea, .form-group select {
    width: 100%;
    padding: 10px 12px;
    border: 1px solid #ddd;
    border-radius: 6px;
    font-size: 14px;
  }
  .table { width: 100%; border-collapse: collapse; }
  .table th, .table td { border: 1px solid #ddd; padding: 10px; text-align: left; }
  .table th { background: #f0f0f0; font-weight: 600; }
  .table tr:hover { background: #f9f9f9; }
  .badge { padding: 4px 10px; border-radius: 20px; font-size: 12px; font-weight: 600; }
  .badge-success { background: #d4edda; color: #155724; }
  .badge-warning { background: #fff3cd; color: #856404; }
  .badge-danger { background: #f8d7da; color: #721c24; }
</style>

Phan 4: CRUD Operations - Doc/Ghi/Sua/Xoa Du Lieu

Day la phan quan trong nhat. CRUD (Create, Read, Update, Delete) la 4 thao tac co ban de tuong tac voi du lieu trong Google Sheets tu Web App.

4.1 google.script.run - Cau noi Client va Server

Trong Web App, client-side JavaScript giao tiep voi server-side Apps Script thong qua google.script.run:

// Client-side (trong file HTML)
// Goi ham server-side va xu ly ket qua
google.script.run
  .withSuccessHandler(function(result) {
    console.log('Thanh cong:', result);
    // Xu ly ket qua o day
  })
  .withFailureHandler(function(error) {
    console.error('Loi:', error);
    alert('Co loi xay ra: ' + error.message);
  })
  .getDataFromSheet(); // Ten ham trong Code.gs

4.2 CREATE - Them du lieu moi

// ====== SERVER-SIDE (Code.gs) ======
function addNewRecord(formData) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');

  // Tao ID tu dong
  var lastRow = sheet.getLastRow();
  var newId = lastRow > 0 ? 'ID-' + (lastRow + 1).toString().padStart(4, '0') : 'ID-0001';

  // Them dong moi
  sheet.appendRow([
    newId,
    formData.name,
    formData.email,
    formData.phone,
    formData.address,
    new Date(),  // Ngay tao
    'Active'     // Status mac dinh
  ]);

  return {
    success: true,
    id: newId,
    message: 'Da them thanh cong: ' + formData.name
  };
}

// ====== CLIENT-SIDE (JavaScript.html) ======
// <script>
function submitForm() {
  var formData = {
    name: document.getElementById('name').value,
    email: document.getElementById('email').value,
    phone: document.getElementById('phone').value,
    address: document.getElementById('address').value
  };

  // Validate
  if (!formData.name || !formData.email) {
    alert('Vui long dien ten va email!');
    return;
  }

  // Hien loading
  document.getElementById('submitBtn').disabled = true;
  document.getElementById('submitBtn').textContent = 'Dang xu ly...';

  google.script.run
    .withSuccessHandler(function(result) {
      if (result.success) {
        alert(result.message);
        document.getElementById('myForm').reset();
        loadData(); // Refresh bang du lieu
      }
      document.getElementById('submitBtn').disabled = false;
      document.getElementById('submitBtn').textContent = 'Them moi';
    })
    .withFailureHandler(function(error) {
      alert('Loi: ' + error.message);
      document.getElementById('submitBtn').disabled = false;
      document.getElementById('submitBtn').textContent = 'Them moi';
    })
    .addNewRecord(formData);
}
// </script>

4.3 READ - Doc va hien thi du lieu

// ====== SERVER-SIDE (Code.gs) ======
function getAllRecords() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');
  var data = sheet.getDataRange().getValues();

  if (data.length <= 1) return []; // Chi co header

  var records = [];
  var headers = data[0];

  for (var i = 1; i < data.length; i++) {
    var record = {};
    for (var j = 0; j < headers.length; j++) {
      record[headers[j]] = data[i][j];
    }
    records.push(record);
  }

  return records;
}

// Tim kiem theo dieu kien
function searchRecords(keyword) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');
  var data = sheet.getDataRange().getValues();
  var results = [];

  var keywordLower = keyword.toLowerCase();
  for (var i = 1; i < data.length; i++) {
    var rowStr = data[i].join(' ').toLowerCase();
    if (rowStr.indexOf(keywordLower) !== -1) {
      results.push({
        id: data[i][0],
        name: data[i][1],
        email: data[i][2],
        phone: data[i][3],
        status: data[i][6]
      });
    }
  }

  return results;
}

// Lay 1 record theo ID
function getRecordById(id) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');
  var data = sheet.getDataRange().getValues();

  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === id) {
      return {
        id: data[i][0],
        name: data[i][1],
        email: data[i][2],
        phone: data[i][3],
        address: data[i][4],
        createdAt: data[i][5],
        status: data[i][6]
      };
    }
  }
  return null;
}

// ====== CLIENT-SIDE (JavaScript.html) ======
// <script>
function loadData() {
  document.getElementById('tableBody').innerHTML = '<tr><td colspan="6">Dang tai...</td></tr>';

  google.script.run
    .withSuccessHandler(function(records) {
      var html = '';
      if (records.length === 0) {
        html = '<tr><td colspan="6">Chua co du lieu</td></tr>';
      } else {
        for (var i = 0; i < records.length; i++) {
          var r = records[i];
          var badgeClass = r.status === 'Active' ? 'badge-success' : 'badge-danger';
          html += '<tr>'
            + '<td>' + r.id + '</td>'
            + '<td>' + r.name + '</td>'
            + '<td>' + r.email + '</td>'
            + '<td>' + r.phone + '</td>'
            + '<td><span class="badge ' + badgeClass + '">' + r.status + '</span></td>'
            + '<td>'
            + '<button onclick="editRecord(\'+ "'" + r.id + "'" + '\)" class="btn-sm">Sua</button> '
            + '<button onclick="deleteRecord(\'+ "'" + r.id + "'" + '\)" class="btn-sm btn-danger">Xoa</button>'
            + '</td>'
            + '</tr>';
        }
      }
      document.getElementById('tableBody').innerHTML = html;
      document.getElementById('recordCount').textContent = 'Tong: ' + records.length + ' ban ghi';
    })
    .withFailureHandler(function(error) {
      document.getElementById('tableBody').innerHTML = '<tr><td colspan="6">Loi: ' + error.message + '</td></tr>';
    })
    .getAllRecords();
}

// Goi khi trang tai xong
window.onload = loadData;
// </script>

4.4 UPDATE - Cap nhat du lieu

// ====== SERVER-SIDE (Code.gs) ======
function updateRecord(id, updatedData) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');
  var data = sheet.getDataRange().getValues();

  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === id) {
      var rowNumber = i + 1; // Sheets rows bat dau tu 1
      sheet.getRange(rowNumber, 2).setValue(updatedData.name);
      sheet.getRange(rowNumber, 3).setValue(updatedData.email);
      sheet.getRange(rowNumber, 4).setValue(updatedData.phone);
      sheet.getRange(rowNumber, 5).setValue(updatedData.address);

      return { success: true, message: 'Da cap nhat: ' + id };
    }
  }

  return { success: false, message: 'Khong tim thay ID: ' + id };
}

4.5 DELETE - Xoa du lieu

// ====== SERVER-SIDE (Code.gs) ======
function deleteRecord(id) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');
  var data = sheet.getDataRange().getValues();

  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === id) {
      sheet.deleteRow(i + 1);
      return { success: true, message: 'Da xoa: ' + id };
    }
  }

  return { success: false, message: 'Khong tim thay ID: ' + id };
}

// Soft delete (danh dau da xoa thay vi xoa han)
function softDeleteRecord(id) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');
  var data = sheet.getDataRange().getValues();

  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === id) {
      sheet.getRange(i + 1, 7).setValue('Deleted');
      sheet.getRange(i + 1, 8).setValue(new Date());
      return { success: true, message: 'Da xoa (soft): ' + id };
    }
  }

  return { success: false, message: 'Khong tim thay ID: ' + id };
}

Phan 5: Deploy Web App

Sau khi viet code xong, ban can deploy de Web App hoat dong. Day la huong dan chi tiet:

Buoc 1: Deploy lan dau

1. Trong Script Editor, click Deploy > New deployment
2. Click icon banh rang (gear) > chon Web app
3. Dien thong tin:
   • Description: Mo ta phien ban (VD: "v1.0 - Initial release")
   • Execute as: Chon "Me" (chay bang tai khoan cua ban)
   • Who has access: Chon quyen truy cap (xem bang duoi)
4. Click Deploy
5. Copy Web app URL - day la link truy cap ung dung

Buoc 2: Cap nhat phien ban

Khi ban sua code va muon cap nhat Web App:

1. Click Deploy > Manage deployments
2. Click icon but chi (edit) cua deployment hien tai
3. O Version, chon New version
4. Them mo ta phien ban moi
5. Click Deploy

Phan 6: 3 Vi Du Thuc Te Kem Code Mau

Vi du 1: Form Dang Ky Su Kien

Tao form dang ky su kien dep hon Google Forms, tu dong gui email xac nhan, hien thi so nguoi da dang ky, va tu dong dong form khi het slot.

// ====== Code.gs ======
var SHEET_NAME = 'Registrations';
var MAX_SLOTS = 50;
var EVENT_NAME = 'Workshop Google Sheets Nang Cao';
var EVENT_DATE = '15/03/2026 - 9:00 AM';

function doGet() {
  var template = HtmlService.createTemplateFromFile('EventForm');
  template.eventName = EVENT_NAME;
  template.eventDate = EVENT_DATE;
  template.slotsRemaining = getRemainingSlots();

  return template.evaluate()
    .setTitle('Dang Ky: ' + EVENT_NAME)
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function getRemainingSlots() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  var registeredCount = Math.max(0, sheet.getLastRow() - 1); // Tru header
  return MAX_SLOTS - registeredCount;
}

function registerForEvent(formData) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  var remaining = getRemainingSlots();

  if (remaining <= 0) {
    return { success: false, message: 'Rat tiec, su kien da het slot dang ky!' };
  }

  // Kiem tra trung email
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][2] === formData.email) {
      return { success: false, message: 'Email nay da dang ky roi!' };
    }
  }

  // Luu dang ky
  var registrationId = 'REG-' + new Date().getTime();
  sheet.appendRow([
    registrationId,
    formData.name,
    formData.email,
    formData.phone,
    formData.company || '',
    new Date(),
    'Confirmed'
  ]);

  // Gui email xac nhan
  try {
    MailApp.sendEmail({
      to: formData.email,
      subject: 'Xac nhan dang ky: ' + EVENT_NAME,
      htmlBody: '<div style="font-family: Arial; max-width: 600px; margin: 0 auto;">'
        + '<h2 style="color: #1a73e8;">Dang ky thanh cong!</h2>'
        + '<p>Xin chao <strong>' + formData.name + '</strong>,</p>'
        + '<p>Ban da dang ky thanh cong su kien <strong>' + EVENT_NAME + '</strong>.</p>'
        + '<p>Thoi gian: ' + EVENT_DATE + '</p>'
        + '<p>Ma dang ky: <strong>' + registrationId + '</strong></p>'
        + '<p>Vui long giu email nay de check-in tai su kien.</p>'
        + '<hr><p style="color: #666;">Gui tu SheetStore - sheet.com.vn</p>'
        + '</div>'
    });
  } catch(e) {
    Logger.log('Khong gui duoc email: ' + e.toString());
  }

  return {
    success: true,
    message: 'Dang ky thanh cong! Ma: ' + registrationId,
    remaining: remaining - 1
  };
}

File EventForm.html se chua giao dien form voi cac truong: Ho ten, Email, SDT, Cong ty. Hien thi so slot con lai va tu dong disable form khi het slot. Su dung CSS de tao giao dien dep voi progress bar hien thi ty le dang ky.

Vi du 2: Dashboard Tra Cuu Don Hang

Tao dashboard de khach hang hoac nhan vien tra cuu trang thai don hang chi bang ma don.

// ====== Code.gs ======
function doGet(e) {
  var template = HtmlService.createTemplateFromFile('OrderDashboard');
  return template.evaluate()
    .setTitle('Tra Cuu Don Hang - SheetStore')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function lookupOrder(orderNumber) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DonHang');
  var data = sheet.getDataRange().getValues();

  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === orderNumber) {
      return {
        found: true,
        order: {
          orderNumber: data[i][0],
          customerName: data[i][1],
          orderDate: Utilities.formatDate(new Date(data[i][2]), 'Asia/Ho_Chi_Minh', 'dd/MM/yyyy HH:mm'),
          items: data[i][3],
          totalAmount: data[i][4],
          status: data[i][5],
          shippingAddress: data[i][6],
          trackingNumber: data[i][7] || 'Chua co',
          estimatedDelivery: data[i][8] ? Utilities.formatDate(new Date(data[i][8]), 'Asia/Ho_Chi_Minh', 'dd/MM/yyyy') : 'Dang cap nhat'
        }
      };
    }
  }

  return { found: false };
}

function getDashboardStats() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DonHang');
  var data = sheet.getDataRange().getValues();

  var stats = {
    total: data.length - 1,
    pending: 0,
    processing: 0,
    shipped: 0,
    delivered: 0,
    todayOrders: 0,
    todayRevenue: 0
  };

  var today = Utilities.formatDate(new Date(), 'Asia/Ho_Chi_Minh', 'yyyy-MM-dd');

  for (var i = 1; i < data.length; i++) {
    var status = data[i][5];
    if (status === 'Pending') stats.pending++;
    else if (status === 'Processing') stats.processing++;
    else if (status === 'Shipped') stats.shipped++;
    else if (status === 'Delivered') stats.delivered++;

    var orderDate = Utilities.formatDate(new Date(data[i][2]), 'Asia/Ho_Chi_Minh', 'yyyy-MM-dd');
    if (orderDate === today) {
      stats.todayOrders++;
      stats.todayRevenue += Number(data[i][4]) || 0;
    }
  }

  return stats;
}

Dashboard hien thi: o tim kiem nhap ma don, ket qua chi tiet voi timeline trang thai (Pending → Processing → Shipped → Delivered), thong tin van chuyen, va cac card thong ke tong quan. Giao dien responsive tren mobile.

Vi du 3: He Thong Danh Gia (Rating System)

Tao he thong danh gia san pham/dich vu voi star rating, cho phep nguoi dung danh gia va xem tong hop ket qua.

// ====== Code.gs ======
function doGet(e) {
  var template = HtmlService.createTemplateFromFile('RatingPage');
  template.items = getItemsWithRatings();
  return template.evaluate()
    .setTitle('Danh Gia San Pham')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function getItemsWithRatings() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var itemSheet = ss.getSheetByName('Items');
  var ratingSheet = ss.getSheetByName('Ratings');

  var items = itemSheet.getDataRange().getValues();
  var ratings = ratingSheet.getDataRange().getValues();

  var result = [];
  for (var i = 1; i < items.length; i++) {
    var itemId = items[i][0];
    var itemRatings = [];
    var totalScore = 0;

    for (var j = 1; j < ratings.length; j++) {
      if (ratings[j][1] === itemId) {
        itemRatings.push({
          userName: ratings[j][2],
          score: ratings[j][3],
          comment: ratings[j][4],
          date: ratings[j][5]
        });
        totalScore += Number(ratings[j][3]);
      }
    }

    var avgRating = itemRatings.length > 0 ? (totalScore / itemRatings.length).toFixed(1) : 0;

    result.push({
      id: itemId,
      name: items[i][1],
      description: items[i][2],
      image: items[i][3] || '',
      avgRating: avgRating,
      totalRatings: itemRatings.length,
      recentReviews: itemRatings.slice(-5).reverse() // 5 review moi nhat
    });
  }

  return result;
}

function submitRating(ratingData) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Ratings');

  // Validate
  if (!ratingData.itemId || !ratingData.score || !ratingData.userName) {
    return { success: false, message: 'Vui long dien day du thong tin!' };
  }

  if (ratingData.score < 1 || ratingData.score > 5) {
    return { success: false, message: 'Diem danh gia phai tu 1 den 5!' };
  }

  sheet.appendRow([
    'R-' + new Date().getTime(),
    ratingData.itemId,
    ratingData.userName,
    ratingData.score,
    ratingData.comment || '',
    new Date()
  ]);

  // Tinh lai trung binh
  var newAvg = calculateAverageRating(ratingData.itemId);

  return {
    success: true,
    message: 'Cam on ban da danh gia!',
    newAverage: newAvg
  };
}

function calculateAverageRating(itemId) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Ratings');
  var data = sheet.getDataRange().getValues();

  var total = 0;
  var count = 0;
  for (var i = 1; i < data.length; i++) {
    if (data[i][1] === itemId) {
      total += Number(data[i][3]);
      count++;
    }
  }

  return count > 0 ? (total / count).toFixed(1) : '0';
}

Giao dien hien thi danh sach san pham dang card, moi card co: ten, mo ta, star rating (sao vang), so luong danh gia, va nut "Danh gia ngay". Khi click se hien modal voi 5 sao de chon va o nhap nhan xet. Ket qua cap nhat realtime sau khi submit.

Phan 7: Tips Bao Mat Cho Web App

Web App tu Google Sheets la cong cu manh, nhung can chu y bao mat de tranh bi lam dung. Duoi day la cac tips quan trong:

7.1 Validate Input Phia Server

KHONG BAO GIO chi validate phia client. Nguoi dung co the bypass JavaScript va gui du lieu truc tiep len server. Luon validate lai trong Code.gs:

function addRecord(formData) {
  // 1. Kiem tra du lieu bat buoc
  if (!formData.name || !formData.email) {
    throw new Error('Thieu thong tin bat buoc');
  }

  // 2. Validate email format
  var emailRegex = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$/;
  if (!emailRegex.test(formData.email)) {
    throw new Error('Email khong hop le');
  }

  // 3. Validate do dai
  if (formData.name.length > 100 || formData.email.length > 100) {
    throw new Error('Du lieu qua dai');
  }

  // 4. Lam sach du lieu (chong XSS)
  formData.name = formData.name.replace(/</g, '').replace(/>/g, '');
  formData.email = formData.email.trim().toLowerCase();

  // 5. Luu vao sheet
  // ...
}

7.2 Gioi Han Truy Cap

// Kiem tra quyen truy cap
function checkAccess() {
  var userEmail = Session.getActiveUser().getEmail();
  var allowedDomains = ['company.com', 'partner.com'];
  var allowedEmails = ['admin@gmail.com', 'manager@gmail.com'];

  // Kiem tra domain
  var userDomain = userEmail.split('@')[1];
  if (allowedDomains.indexOf(userDomain) !== -1) return true;

  // Kiem tra email cu the
  if (allowedEmails.indexOf(userEmail) !== -1) return true;

  return false;
}

function doGet(e) {
  if (!checkAccess()) {
    return HtmlService.createHtmlOutput(
      '<h1>Access Denied</h1><p>Ban khong co quyen truy cap ung dung nay.</p>'
    );
  }

  // Tiep tuc hien thi app...
  return HtmlService.createTemplateFromFile('Page').evaluate();
}

7.3 Rate Limiting

// Gioi han so request tu 1 nguoi trong 1 phut
function checkRateLimit(userEmail) {
  var cache = CacheService.getScriptCache();
  var key = 'rate_' + userEmail;
  var count = cache.get(key);

  if (count && Number(count) >= 30) { // Max 30 requests/phut
    throw new Error('Ban dang gui qua nhieu yeu cau. Vui long doi 1 phut.');
  }

  cache.put(key, count ? (Number(count) + 1).toString() : '1', 60); // TTL 60 giay
}

function addRecord(formData) {
  var userEmail = Session.getActiveUser().getEmail() || 'anonymous';
  checkRateLimit(userEmail);

  // Tiep tuc xu ly...
}

7.4 Logging va Audit Trail

// Ghi log moi thao tac
function logAction(action, details) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = ss.getSheetByName('AuditLog');
  if (!logSheet) {
    logSheet = ss.insertSheet('AuditLog');
    logSheet.appendRow(['Timestamp', 'User', 'Action', 'Details', 'IP']);
  }

  logSheet.appendRow([
    new Date(),
    Session.getActiveUser().getEmail() || 'anonymous',
    action,
    JSON.stringify(details),
    ''  // IP khong lay duoc trong Apps Script
  ]);
}

// Su dung
function deleteRecord(id) {
  logAction('DELETE', { recordId: id });
  // Thuc hien xoa...
}

Phan 8: Gioi Han va Limitations

Web App tu Google Sheets la cong cu tuyet voi cho nhieu tinh huong, nhung khong phai la giai phap cho moi thu. Hieu ro gioi han se giup ban chon cong nghe phu hop.

Phan 9: Tips Nang Cao De Web App Nhanh Hon

Mot so ky thuat giup Web App cua ban chay nhanh hon va trai nghiem nguoi dung tot hon:

9.1 Su dung CacheService

// Cache du lieu thuong truy cap (TTL 10 phut)
function getProductList() {
  var cache = CacheService.getScriptCache();
  var cachedData = cache.get('productList');

  if (cachedData) {
    return JSON.parse(cachedData);
  }

  // Neu khong co cache, doc tu sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Products');
  var data = sheet.getDataRange().getValues();

  var products = [];
  for (var i = 1; i < data.length; i++) {
    products.push({
      id: data[i][0],
      name: data[i][1],
      price: data[i][2],
      stock: data[i][3]
    });
  }

  // Luu cache 10 phut (600 giay)
  cache.put('productList', JSON.stringify(products), 600);
  return products;
}

// Xoa cache khi data thay doi
function clearProductCache() {
  CacheService.getScriptCache().remove('productList');
}

9.2 Pagination - Phan trang du lieu

// Thay vi load toan bo, chi load tung trang
function getRecordsPage(pageNumber, pageSize) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');
  var totalRows = sheet.getLastRow() - 1; // Tru header
  var totalPages = Math.ceil(totalRows / pageSize);

  var startRow = (pageNumber - 1) * pageSize + 2; // +2 vi co header va Sheets bat dau tu 1
  var numRows = Math.min(pageSize, totalRows - (pageNumber - 1) * pageSize);

  if (numRows <= 0) return { records: [], totalPages: totalPages, currentPage: pageNumber };

  var range = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  var data = range.getValues();

  var records = [];
  for (var i = 0; i < data.length; i++) {
    records.push({
      id: data[i][0],
      name: data[i][1],
      email: data[i][2],
      status: data[i][3]
    });
  }

  return {
    records: records,
    totalPages: totalPages,
    currentPage: pageNumber,
    totalRecords: totalRows
  };
}

9.3 Loading State va UX

// Client-side: Hien thi loading spinner
// <script>
function showLoading() {
  document.getElementById('loading').style.display = 'flex';
  document.getElementById('content').style.opacity = '0.5';
}

function hideLoading() {
  document.getElementById('loading').style.display = 'none';
  document.getElementById('content').style.opacity = '1';
}

function loadData() {
  showLoading();
  google.script.run
    .withSuccessHandler(function(result) {
      hideLoading();
      renderTable(result);
    })
    .withFailureHandler(function(error) {
      hideLoading();
      showError(error.message);
    })
    .getAllRecords();
}

function showError(message) {
  var errorDiv = document.getElementById('error');
  errorDiv.textContent = message;
  errorDiv.style.display = 'block';
  setTimeout(function() { errorDiv.style.display = 'none'; }, 5000);
}

function showSuccess(message) {
  var successDiv = document.getElementById('success');
  successDiv.textContent = message;
  successDiv.style.display = 'block';
  setTimeout(function() { successDiv.style.display = 'none'; }, 3000);
}
// </script>

Phan 10: Cau Hoi Thuong Gap (FAQ)

Tong Ket

Web App tu Google Sheets bang Apps Script la cong cu tuyet voi de xay dung ung dung web don gian ma khong mat dong nao cho hosting, domain, hay server. Voi kien thuc HTML/CSS/JavaScript co ban va hieu biet ve Apps Script, ban co the tao ra nhung tool noi bo manh me cho doanh nghiep chi trong vai ngay.

Hay bat dau voi vi du don gian nhat - mot form thu thap du lieu. Khi da quen voi workflow, ban se nhanh chong mo rong thanh dashboard, portal, va nhieu hon nua. Va khi nhu cau vuot qua kha nang cua Apps Script, ban co the chuyen sang cac giai phap chuyen nghiep hon ma van giu Google Sheets lam "database" quen thuoc.