Power Automate Excel Data Processing: Tự Động Hoá Xử Lý Dữ Liệu

Power Automate + Excel = tự động hoá tasks lặp đi lặp lại: đọc data từ Excel → xử lý → ghi kết quả → gửi email. Thay vì manual copy-paste hàng tuần, build 1 flow chạy tự động. Dùng Standard connector (Excel Online Business) ← free với Microsoft 365, không cần Premium license. Bài viết hướng dẫn các flow patterns phổ biến, xử lý lỗi, và optimize performance cho doanh nghiệp VN.

Excel Prerequisites

Table Format Required

⚠️ Power Automate yêu cầu data trong TABLE format:

Cách tạo Table trong Excel:
  1. Select data range (bao gồm headers)
  2. Insert → Table
  3. ✅ "My table has headers"
  4. Table Design → Table Name: "DonHang"

Yêu cầu:
  ✅ File trên OneDrive hoặc SharePoint (not local)
  ✅ Data trong named Table (not plain range)
  ✅ Unique column names (no duplicate headers)
  ✅ No merged cells
  ✅ No blank rows giữa data
  ❌ NOT: file trên desktop/local drive
  ❌ NOT: data trên sheet không có Table

Supported Operations

Flow Pattern 1: Daily Report Email

Tự Động Gửi Báo Cáo Hàng Ngày

Scenario: Mỗi sáng 8:00 → gửi email tổng hợp đơn hàng hôm qua

Flow:
  Trigger: Recurrence (Daily, 8:00 AM, UTC+7)
  
  Action 1: List rows (Excel)
    → Location: OneDrive/SharePoint
    → Document: "DonHang_2026.xlsx"
    → Table: "DonHang"
    → Filter: NgayDat eq 'yesterday-date'
  
  Action 2: Initialize variable "TongTien"
    → Type: Float, Value: 0
  
  Action 3: Apply to each (rows)
    → Increment variable "TongTien" by ThanhTien
  
  Action 4: Send email (V2)
    → To: manager@company.com
    → Subject: "Báo cáo đơn hàng @{formatDateTime(utcNow(),'dd/MM/yyyy')}"
    → Body:
      "Tổng đơn hôm qua: @{length(body('List_rows')['value'])}
       Tổng tiền: @{variables('TongTien')} VND
       Chi tiết: [link to Excel file]"

Flow Pattern 2: Data Entry Notification

Thông Báo Khi Có Row Mới

Scenario: Sales thêm row trong Excel → notify manager + post Teams

Flow:
  Trigger: When a row is added (Excel Online)
    → Location: SharePoint site
    → Document: "Pipeline.xlsx"
    → Table: "Deals"
  
  Action 1: Post message in Teams
    → Channel: Sales-Notifications
    → Message:
      "🆕 Deal mới: @{triggerOutputs()?['body/TenKH']}
       Giá trị: @{triggerOutputs()?['body/GiaTri']} VND
       Sales: @{triggerOutputs()?['body/NhanVien']}"
  
  Action 2: Condition (GiaTri > 100,000,000)
    If Yes:
      → Send email to CEO
      → Subject: "Deal lớn > 100M: @{triggerOutputs()?['body/TenKH']}"
    If No:
      → Do nothing

Flow Pattern 3: Excel → SharePoint List Sync

Đồng Bộ Data

Scenario: HR update Excel → auto-sync to SharePoint List

Flow:
  Trigger: Recurrence (Every 1 hour)
  
  Action 1: List rows (Excel)
    → File: "NhanVien.xlsx"
    → Table: "DSNV"
  
  Action 2: Apply to each:
    Condition: Check if employee exists in SharePoint
    → Get items (SharePoint) filter: MaNV eq 'currentRow'
    
    If exists:
      → Update item (SharePoint)
    If not:
      → Create item (SharePoint)
  
  Action 3: Error handling:
    → Configure run after → "has failed"
    → Send email: "Sync failed: @{items('Apply_to_each')?['MaNV']}"

⚠️ Performance: > 500 rows → use batch processing
→ Chunking: process 100 rows at a time
→ Concurrency: Apply to each → Settings → Degree = 20

Flow Pattern 4: Monthly Report Generator

Báo Cáo Tháng Tự Động

Scenario: Cuối tháng → aggregate data → create summary sheet

Flow:
  Trigger: Recurrence (Monthly, Day 1, 7:00 AM)
  
  Action 1: List rows (Excel)
    → File: "Revenue_2026.xlsx"
    → Table: "MonthlyData"
    → Filter: Thang eq '@{month(addDays(utcNow(),-1))}'
  
  Action 2: Compose (calculations)
    → TongDoanhThu: sum of DoanhThu column
    → TBDoanhThu: TongDoanhThu / length(rows)
    → SoKhachMoi: count where LoaiKH = "Mới"
  
  Action 3: Add row to summary Excel
    → File: "Summary_2026.xlsx"
    → Table: "BaoCaoThang"
    → Row: Thang, TongDoanhThu, TBDoanhThu, SoKhachMoi
  
  Action 4: Send email with summary
    → Attach: Summary_2026.xlsx
    → To: management-team@company.com

Flow Pattern 5: Data Validation & Cleanup

Kiểm Tra & Làm Sạch Data

Scenario: Validate data khi nhập, flag errors

Flow:
  Trigger: When row added (Excel)
  
  Action 1: Condition checks:
    → Email valid? IsMatch with regex
    → Phone starts with 0? StartsWith
    → Amount > 0? Greater than
    → Required fields filled? Not blank
  
  Action 2: If invalid:
    → Update row: set "TrangThai" = "Lỗi"
    → Update row: set "GhiChu" = "Email không hợp lệ"
    → Send email to data entry person
  
  Action 3: If valid:
    → Update row: set "TrangThai" = "OK"
    → Copy to master table (another Excel/SharePoint)

Error Handling

Common Errors & Fixes

Retry Configuration

Per-action retry policy:
  → Action → Settings → Retry Policy
  → Type: Fixed interval
  → Count: 3
  → Interval: PT30S (30 seconds)
  
Scope for error handling:
  → Add "Scope" action → put actions inside
  → Add parallel "Scope" for failure
  → Configure run after: "has failed"
  → Send alert email on failure

Performance Optimization

Tips Cho Large Datasets

1. Limit rows returned:
   → List rows → Top Count: 100
   → Use $filter to narrow results
   → Avoid listing ALL rows if only need subset

2. Parallel processing:
   → Apply to each → Settings
   → Degree of Parallelism: 20 (max 50)
   → ⚠️ Order not guaranteed with parallel

3. Batch operations:
   → Instead of 1 API call per row
   → Use "Create table" → batch insert
   → Or use Office Scripts for bulk operations

4. Scheduling:
   → Run during off-hours (2 AM)
   → Avoid peak hours (9-11 AM)
   → Stagger multiple flows (not all at same time)

5. File size:
   → Keep Excel < 25 MB for best performance
   → Archive old data to separate files
   → Split large tables into sheets by month/year

Checklist Triển Khai Excel Automation

• Convert data ranges → Excel Tables (named)
• Move Excel files → OneDrive/SharePoint
• Identify repetitive tasks → candidate flows
• Build flow: start simple → add complexity
• Add error handling (retry, notifications)
• Test with sample data (10 rows first)
• Configure concurrency settings
• Schedule flows (recurrence + timezone UTC+7)
• Set up failure alerts → email/Teams
• Document flows cho team handover
• Monitor qua Power Platform admin center → Analytics

FAQ

1) Power Automate Excel connector có cần Premium license không?

Không — Excel Online (Business) = Standard connector, free với Microsoft 365 Business Basic trở lên. Tất cả operations (list rows, add row, update, delete) đều Standard. Tuy nhiên file PHẢI ở trên OneDrive for Business hoặc SharePoint (không phải personal OneDrive consumer). Giới hạn: 256 columns per table, ~500K rows (performance degrades). Nếu cần xử lý Excel trên local drive → dùng Power Automate Desktop (RPA) với riêng license. Admin verify licenses qua Microsoft 365 admin center. Power Platform admin center → DLP policies control nào connector nào cho phép.

2) Flow "List rows" lấy tối đa bao nhiêu rows?

Default 256 rows, max ~100,000 rows (với pagination). Settings: action → Settings → Pagination → On → Threshold: 100,000. Nhưng: > 5,000 rows sẽ chậm (nhiều API calls). Best practice: dùng $filter OData query để narrow results. Ví dụ: Status eq 'Active' chỉ lấy rows active. Kết hợp $top: limit rows returned. Nếu cần process 50,000+ rows regularly → consider Dataverse hoặc SQL Server. Flow run time limit: 30 ngày (cloud flow), nhưng API calls timeout sau 2 phút mỗi call.

3) Data trong Excel bị format sai (date, number) khi Power Automate đọc?

Common issue — Excel serial dates. Excel lưu dates dạng serial number (45000 = ngày nào đó). Power Automate đọc raw value → cần convert. Formula: addDays('1899-12-30', int(triggerOutputs()?['body/NgayDat']), 'dd/MM/yyyy'). Numbers: Excel "General" format có thể add decimals → dùng int() hoặc float() trong expressions. Currency: format VND trước khi hiện → formatNumber(amount, 'N0') (no decimals). Best practice: define column types rõ ràng trong Excel Table → Power Automate đọc đúng type. Admin training cho data entry team về format conventions.

4) Nhiều flows cùng đọc/ghi 1 file Excel có conflict không?

Có thể — cần quản lý concurrency. Excel Online hỗ trợ co-authoring nhưng Power Automate ghi data = API calls → concurrent writes có thể conflict. Mitigation: (1) Stagger flows (không chạy cùng lúc). (2) Dùng queue pattern: 1 flow ghi, others chờ. (3) Lock file: flow đầu tạo flag → flow khác check flag. (4) Best: dùng SharePoint List thay Excel cho data warehouse → better concurrency. Nếu > 3 flows ghi cùng 1 Excel → migrate to Dataverse hoặc SharePoint List cho reliability. Monitor conflicts qua Power Platform admin center → Flow run history.

5) Power Automate có thể format Excel (bold, color, merge cells) không?

Có — dùng Office Scripts. Standard Power Automate actions chỉ read/write data (không format). Để format: (1) Office Scripts (TypeScript): viết script format → Power Automate gọi "Run script" action. (2) Graph API: advanced formatting via HTTP connector (Premium). Office Scripts ví dụ: bold header row, color cells based on value, auto-width columns, add borders. Script lưu trong OneDrive → reusable across flows. Office Scripts cần Microsoft 365 Business Standard trở lên (not Business Basic). Admin manage Office Scripts qua Microsoft 365 admin center → Settings → Office Scripts.

Bài Liên Quan Nên Đọc

• Power Automate Triggers Actions Connectors
• Power Automate SharePoint Document Automation
• Power Apps SharePoint List Forms Customization
• Power Apps Licensing Plans Per App Per User
• Microsoft 365 Security Best Practices
• Power BI Dashboards Reports Differences

Kết Luận

Power Automate + Excel là combo tự động hoá đơn giản nhất cho doanh nghiệp VN — Standard connector miễn phí, không cần Premium license. Flow patterns: daily report email, new row notifications, data sync Excel ↔ SharePoint, monthly aggregation, data validation. Yêu cầu: data trong Excel Table format + file trên OneDrive/SharePoint. Error handling: retry policies + failure alerts. Performance: pagination, parallel processing, $filter queries. Admin quản lý qua Power Platform admin center (DLP, analytics), Microsoft 365 admin center (licenses, Office Scripts). Start: pick 1 repetitive Excel task → build flow → measure time saved → expand.

Liên Hệ PUPAM

Cần tự động hoá Excel workflows?

• ✅ Flow design & development
• ✅ Data migration Excel → SharePoint/Dataverse
• ✅ Office Scripts development
• ✅ Training team build flows
• ✅ Performance optimization

📧 Email: hello@pupam.com 🌐 Website: pupam.com