Apps Script Từ A Đến Z - Bài 2: SpreadsheetApp - Đọc, Ghi và Xử Lý Dữ Liệu

Trong bài 2 của series Apps Script Từ A-Z, chúng ta sẽ đi sâu vào SpreadsheetApp — service quan trọng nhất khi làm việc với Google Sheets. Sau bài này, bạn sẽ thành thạo đọc ghi dữ liệu, xử lý Range, và viết code hiệu quả không bị timeout.

Hierarchy: Spreadsheet → Sheet → Range → Cell

Để làm việc hiệu quả với SpreadsheetApp, cần hiểu rõ cấu trúc phân cấp:

SpreadsheetApp          // Service chính
  └── Spreadsheet       // File .gsheet (workbook)
        ├── Sheet       // Từng tab trong file
        │     └── Range // Vùng ô (A1, A1:C10, v.v.)
        │           └── Cell // Từng ô đơn lẻ
        ├── NamedRange  // Dải ô được đặt tên
        └── Protection  // Bảo vệ sheet/range

Mở Spreadsheet: 4 Cách Khác Nhau

// Cách 1: Active Spreadsheet (container-bound only)
// Dùng khi script gắn với file Sheets
const ss1 = SpreadsheetApp.getActiveSpreadsheet();

// Cách 2: Mở bằng ID
// ID lấy từ URL: docs.google.com/spreadsheets/d/[ID]/edit
const ss2 = SpreadsheetApp.openById('1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms');

// Cách 3: Mở bằng URL đầy đủ
const ss3 = SpreadsheetApp.openByUrl(
  'https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/edit'
);

// Cách 4: Tạo Spreadsheet mới
const ss4 = SpreadsheetApp.create('Báo Cáo Tháng 3/2027');
SpreadsheetApp.create('Báo Cáo', 50, 20); // 50 rows, 20 columns

// Lấy ID và URL của file
Logger.log(ss1.getId());    // ID
Logger.log(ss1.getUrl());   // Full URL
Logger.log(ss1.getName());  // Tên file

Sheet Operations

function sheetOperations() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Lấy sheet theo tên
  const sheet = ss.getSheetByName('Data');
  
  // Lấy sheet đang active
  const activeSheet = ss.getActiveSheet();
  
  // Lấy tất cả sheets
  const allSheets = ss.getSheets();
  allSheets.forEach(s => Logger.log(s.getName()));
  
  // Tạo sheet mới
  const newSheet = ss.insertSheet('Reports');
  ss.insertSheet('Archive', 2); // chèn vào vị trí 2
  
  // Đổi tên sheet
  sheet.setName('Sales Data');
  
  // Ẩn/hiện sheet
  sheet.hideSheet();
  sheet.showSheet();
  
  // Sao chép sheet
  const copy = sheet.copyTo(ss); // copy trong cùng file
  copy.setName('Data Backup');
  
  // Di chuyển sheet
  ss.setActiveSheet(sheet);
  ss.moveActiveSheet(1); // di chuyển về vị trí đầu
  
  // Xóa sheet (cẩn thận!)
  // ss.deleteSheet(newSheet);
  
  // Màu tab
  sheet.setTabColor('#ff0000');    // Tab màu đỏ
  sheet.setTabColorObject(null);   // Reset màu
  
  // Freeze rows/columns
  sheet.setFrozenRows(1);          // Đóng băng hàng đầu
  sheet.setFrozenColumns(2);       // Đóng băng 2 cột đầu
  
  Logger.log('Tổng số sheets: ' + ss.getNumSheets());
}

Range Operations: Trái Tim Của SpreadsheetApp

Các cách lấy Range

function getRangeExamples() {
  const sheet = SpreadsheetApp.getActiveSheet();

  // Theo địa chỉ A1 notation
  const r1 = sheet.getRange('A1');
  const r2 = sheet.getRange('A1:C10');
  const r3 = sheet.getRange('A:A');       // Cả cột A
  const r4 = sheet.getRange('1:1');       // Cả hàng 1
  const r5 = sheet.getRange('Sheet2!B2'); // Sheet khác

  // Theo row, column (1-indexed)
  const r6 = sheet.getRange(1, 1);        // A1
  const r7 = sheet.getRange(1, 1, 10, 3); // A1:C10 (row, col, numRows, numCols)
  const r8 = sheet.getRange(2, 1, sheet.getLastRow() - 1, 5); // Dynamic range

  // DataRange: vùng có dữ liệu
  const dataRange = sheet.getDataRange(); // Tất cả ô có data

  // Last row/column có dữ liệu
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  Logger.log('Data range: A1:' + lastRow + ',' + lastCol);

  // Active range (ô/vùng người dùng đang chọn)
  const selected = sheet.getActiveRange();
  
  // Named Range
  const namedRange = SpreadsheetApp.getActiveSpreadsheet()
    .getRangeByName('SalesData'); // Phải tạo trước trong Sheets
}

Đọc Dữ Liệu

function readData() {
  const sheet = SpreadsheetApp.getActiveSheet();

  // Đọc một ô
  const singleValue = sheet.getRange('A1').getValue(); // Giá trị hiển thị
  const rawValue = sheet.getRange('A1').getValue();    // Số/Text/Date/Boolean

  // Đọc nhiều ô - trả về 2D array [[row1col1, row1col2], [row2col1, row2col2]]
  const values2D = sheet.getRange('A1:C5').getValues();
  
  // Duyệt qua 2D array
  values2D.forEach((row, rowIndex) => {
    row.forEach((cell, colIndex) => {
      Logger.log('Row %d, Col %d: %s', rowIndex + 1, colIndex + 1, cell);
    });
  });

  // Lấy display values (text đã format, ví dụ: "1,000,000đ" thay vì 1000000)
  const displayValues = sheet.getRange('A1:C5').getDisplayValues();

  // Lấy formulas
  const formula = sheet.getRange('D1').getFormula();
  const formulas = sheet.getRange('D1:D10').getFormulas();

  // Lấy notes (comments)
  const note = sheet.getRange('A1').getNote();
  
  // Lấy số hàng/cột của Range
  const range = sheet.getRange('B2:E6');
  Logger.log('Rows: %d, Cols: %d', range.getNumRows(), range.getNumColumns());
  Logger.log('Start row: %d, Start col: %d', range.getRow(), range.getColumn());
}

Ghi Dữ Liệu

function writeData() {
  const sheet = SpreadsheetApp.getActiveSheet();

  // Ghi một giá trị
  sheet.getRange('A1').setValue('Hello SheetStore');
  sheet.getRange(1, 1).setValue(new Date()); // Ghi date

  // Ghi array 2D vào range
  const data = [
    ['Sản phẩm', 'Số lượng', 'Đơn giá', 'Thành tiền'],
    ['Áo', 10, 150000, '=B2*C2'],
    ['Quần', 5, 250000, '=B3*C3'],
    ['Giày', 3, 450000, '=B4*C4'],
  ];
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

  // Thêm hàng cuối (append)
  sheet.appendRow(['Mũ', 8, 120000, '=B5*C5']);

  // Ghi formula
  sheet.getRange('E1').setFormula('=SUM(D2:D100)');
  
  // Ghi nhiều formula
  const formulas = [['=A1*0.1'], ['=A2*0.1'], ['=A3*0.1']];
  sheet.getRange('B1:B3').setFormulas(formulas);

  // Ghi note
  sheet.getRange('A1').setNote('Cập nhật: ' + new Date());
  
  Logger.log('Đã ghi dữ liệu thành công');
}

Cell Formatting

function formatCells() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const headerRange = sheet.getRange('A1:E1');
  const dataRange = sheet.getRange('A2:E100');

  // Font style
  headerRange
    .setFontWeight('bold')
    .setFontSize(12)
    .setFontFamily('Arial')
    .setFontColor('#FFFFFF')
    .setFontStyle('italic'); // 'italic' hoặc 'normal'

  // Background color
  headerRange.setBackground('#1a73e8');    // Hex color
  dataRange.setBackground('#f8f9fa');

  // Alternating row colors
  for (let i = 2; i <= 100; i++) {
    const color = (i % 2 === 0) ? '#f8f9fa' : '#ffffff';
    sheet.getRange(i, 1, 1, 5).setBackground(color);
  }

  // Number format
  sheet.getRange('C2:C100').setNumberFormat('#,##0'); // Số có dấu phẩy
  sheet.getRange('D2:D100').setNumberFormat('#,##0 "đ"'); // Tiền VND
  sheet.getRange('E2:E100').setNumberFormat('0.00%'); // Phần trăm
  sheet.getRange('F2:F100').setNumberFormat('dd/MM/yyyy'); // Ngày tháng

  // Alignment
  headerRange.setHorizontalAlignment('center');
  dataRange.setVerticalAlignment('middle');

  // Borders
  headerRange.setBorder(true, true, true, true, false, false, '#1a73e8',
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  // Merge cells
  sheet.getRange('A1:B1').merge();
  sheet.getRange('A1:B1').unmerge(); // Hủy merge

  // Wrap text
  dataRange.setWrap(true);
  dataRange.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
}

Clear Operations

function clearOperations() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('A1:E100');

  // Xóa tất cả (data + format + notes)
  range.clear();

  // Chỉ xóa data, giữ format
  range.clearContent();

  // Chỉ xóa format, giữ data
  range.clearFormat();

  // Chỉ xóa notes
  range.clearNote();

  // Xóa cả sheet
  sheet.clear();
  sheet.clearContents();
  
  // Xóa hàng/cột
  sheet.deleteRow(5);        // Xóa hàng 5
  sheet.deleteRows(5, 3);    // Xóa 3 hàng từ hàng 5
  sheet.deleteColumn(3);     // Xóa cột C
  sheet.deleteColumns(3, 2); // Xóa 2 cột từ cột C
  
  // Chèn hàng/cột
  sheet.insertRowBefore(3);      // Chèn 1 hàng trước hàng 3
  sheet.insertRowsAfter(5, 2);   // Chèn 2 hàng sau hàng 5
  sheet.insertColumnAfter(2);    // Chèn cột sau cột B
}

Performance: Batch vs Single Cell

// Benchmark: xử lý 1000 ô
// BAD: ~10-30 giây, dễ timeout
function slowMethod() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const start = new Date();
  
  for (let i = 1; i <= 1000; i++) {
    const val = sheet.getRange(i, 1).getValue(); // 1000 reads
    sheet.getRange(i, 2).setValue(val * 1.1);    // 1000 writes
  }
  
  Logger.log('Slow: ' + (new Date() - start) + 'ms');
}

// GOOD: ~0.5-2 giây
function fastMethod() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const start = new Date();
  
  const values = sheet.getRange('A1:A1000').getValues(); // 1 read
  const newValues = values.map(row => [row[0] * 1.1]);
  sheet.getRange('B1:B1000').setValues(newValues); // 1 write
  
  Logger.log('Fast: ' + (new Date() - start) + 'ms');
}

// BEST PRACTICE: SpreadsheetApp.flush() khi cần
function withFlush() {
  const sheet = SpreadsheetApp.getActiveSheet();
  
  // Ghi batch 1
  sheet.getRange('A1:A500').setValues(generateData(500));
  
  // Force sync ngay (để xem kết quả trước khi tiếp tục)
  SpreadsheetApp.flush();
  
  // Ghi batch 2
  sheet.getRange('B1:B500').setValues(generateData(500));
}

function generateData(count) {
  return Array.from({ length: count }, (_, i) => [i + 1]);
}

Named Ranges

function namedRanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Tạo Named Range
  ss.setNamedRange('SalesData', ss.getSheetByName('Sales').getRange('A2:E1000'));
  ss.setNamedRange('Settings', ss.getSheetByName('Config').getRange('B1:B20'));

  // Lấy Named Range
  const salesRange = ss.getRangeByName('SalesData');
  const values = salesRange.getValues();
  Logger.log('Sales rows: ' + values.length);

  // Liệt kê tất cả Named Ranges
  const namedRanges = ss.getNamedRanges();
  namedRanges.forEach(nr => {
    Logger.log('%s: %s', nr.getName(), nr.getRange().getA1Notation());
  });

  // Xóa Named Range
  namedRanges.forEach(nr => {
    if (nr.getName() === 'OldRange') nr.remove();
  });
}

Ví Dụ Thực Tế 1: Import CSV Data

function importCSVFromDrive() {
  // Giả sử có file CSV trên Drive
  const CSV_FILE_ID = 'your-csv-file-id-here';
  
  const file = DriveApp.getFileById(CSV_FILE_ID);
  const csvContent = file.getBlob().getDataAsString('UTF-8');
  
  // Parse CSV
  const rows = Utilities.parseCsv(csvContent);
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let importSheet = ss.getSheetByName('Import');
  
  if (!importSheet) {
    importSheet = ss.insertSheet('Import');
  } else {
    importSheet.clearContents(); // Clear cũ
  }
  
  // Ghi toàn bộ CSV vào sheet một lần
  if (rows.length > 0) {
    importSheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
  }
  
  // Format header
  importSheet.getRange(1, 1, 1, rows[0].length)
    .setBackground('#1a73e8')
    .setFontColor('#ffffff')
    .setFontWeight('bold');
  
  // Auto-resize columns
  for (let i = 1; i <= rows[0].length; i++) {
    importSheet.autoResizeColumn(i);
  }
  
  SpreadsheetApp.getUi().alert('Import thành công: ' + (rows.length - 1) + ' dòng dữ liệu');
}

Ví Dụ Thực Tế 2: Mass Update Formulas

function massUpdateFormulas() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  
  if (lastRow < 2) {
    Logger.log('Không có dữ liệu');
    return;
  }
  
  // Tạo mảng formulas cho cột E (Thành tiền = Số lượng * Đơn giá)
  const formulas = [];
  for (let i = 2; i <= lastRow; i++) {
    formulas.push(['=C' + i + '*D' + i]);
  }
  
  // Ghi tất cả formulas một lần
  sheet.getRange(2, 5, formulas.length, 1).setFormulas(formulas);
  
  // Cập nhật cột F: Thuế VAT 10%
  const vatFormulas = [];
  for (let i = 2; i <= lastRow; i++) {
    vatFormulas.push(['=E' + i + '*0.1']);
  }
  sheet.getRange(2, 6, vatFormulas.length, 1).setFormulas(vatFormulas);
  
  // Cột G: Tổng sau thuế
  const totalFormulas = [];
  for (let i = 2; i <= lastRow; i++) {
    totalFormulas.push(['=E' + i + '+F' + i]);
  }
  sheet.getRange(2, 7, totalFormulas.length, 1).setFormulas(totalFormulas);
  
  // Format tiền tệ cho E, F, G
  const moneyRange = sheet.getRange(2, 5, lastRow - 1, 3);
  moneyRange.setNumberFormat('#,##0 "đ"');
  
  Logger.log('Đã cập nhật formulas cho ' + (lastRow - 1) + ' dòng');
}

Ví Dụ Thực Tế 3: Data Cleaning Automation

function cleanData() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  
  const cleanedValues = values.map((row, rowIndex) => {
    // Bỏ qua header
    if (rowIndex === 0) return row;
    
    return row.map((cell, colIndex) => {
      if (typeof cell === 'string') {
        // Trim whitespace
        cell = cell.trim();
        
        // Chuẩn hóa khoảng trắng (nhiều space → 1 space)
        cell = cell.replace(/s+/g, ' ');
        
        // Capitalize tên riêng (cột 1)
        if (colIndex === 1) {
          cell = cell.split(' ')
            .map(w => w.charAt(0).toUpperCase() + w.slice(1).toLowerCase())
            .join(' ');
        }
        
        // Chuẩn hóa số điện thoại (cột 3)
        if (colIndex === 3) {
          cell = cell.replace(/[^0-9+]/g, ''); // Chỉ giữ số và +
          if (cell.startsWith('84')) cell = '0' + cell.slice(2);
        }
      }
      
      // Chuyển ngày dạng text sang Date object (cột 5)
      if (colIndex === 5 && typeof cell === 'string' && cell.includes('/')) {
        const parts = cell.split('/');
        if (parts.length === 3) {
          const d = parseInt(parts[0]), m = parseInt(parts[1]) - 1, y = parseInt(parts[2]);
          cell = new Date(y, m, d);
        }
      }
      
      return cell;
    });
  });
  
  // Ghi lại dữ liệu đã clean
  dataRange.setValues(cleanedValues);
  
  // Format lại cột ngày
  sheet.getRange(2, 6, values.length - 1, 1).setNumberFormat('dd/MM/yyyy');
  
  // Highlight hàng có dữ liệu trống
  for (let i = 1; i < cleanedValues.length; i++) {
    const hasEmpty = cleanedValues[i].some(cell => cell === '' || cell === null);
    if (hasEmpty) {
      sheet.getRange(i + 1, 1, 1, cleanedValues[i].length)
        .setBackground('#fff3cd'); // Vàng nhạt
    }
  }
  
  SpreadsheetApp.getUi().alert('Đã làm sạch ' + (values.length - 1) + ' dòng dữ liệu');
}

Tóm Tắt Cheatsheet SpreadsheetApp