[BigQuery] 更快速的反應BigQuery中取出GA4資料呈現於GoogleSheet的報表

快速導覽

前言

在先前的文章中，我們介紹了如何在 Google Sheets 中使用 BigQuery 連接器，透過 SQL 查詢所需的資料。本篇將進一步探討更靈活的方法，並推薦一個實用的網站 GA4SQL，它可以幫助使用者快速查詢 GA4 的不同維度與指標，並自動產生對應的 SQL 語法，提升查詢效率。

然而，透過 Google Sheets 的 BigQuery 連接器執行查詢，最快的更新頻率僅能達到 1 小時，這對於需要即時更新的報表而言可能不夠即時。因此，我們將改用 Google Apps Script（GAS）來直接對接 BigQuery，實現更即時的資料查詢與報表更新。

使用 Apps Script 來查詢 BigQuery 需要掌握以下幾個核心技巧：

1. BigQuery 服務的授權與設定：透過 Apps Script 內建的 BigQuery.Jobs.query() 方法來執行 SQL 查詢。
2. 處理查詢結果：解析 BigQuery 回傳的 JSON 格式資料，並將其整理成可用的結構，方便寫入 Google Sheets。
3. 定時自動執行（Triggers）：設定 Apps Script 的時間觸發器，讓報表能夠根據需求定期更新，而不是受限於 1 小時的最小間隔。
4. 錯誤處理與日誌記錄：確保當查詢失敗時能適當處理錯誤，並記錄執行結果以利除錯與維護。

本篇將從環境設定開始，逐步說明如何使用 Apps Script 連接 BigQuery 查詢 GA4 資料，並實作一個可快速更新的報表，讓 GA4 數據能夠更即時地呈現在 Google Sheets 中。

App Script

Google Apps Script是一種基於JavaScript的雲端腳本語言，專門用於擴展和自動化 Google 工作區（Google Workspace）應用程式，如Google Sheets、Docs、Drive、Gmail 等。它由Google提供，能讓使用者透過自訂腳本完成複雜的操作，而無需額外安裝軟體。

此次我們將先在google sheet上新增一個App Script，請在上方選單上找到「擴充功能 > Apps Script」

接著先重新命名這個App Script的名稱，將其命名為「DemoData」(點擊紅框就能重新命名)

再來到程式撰寫區，首先到github上將這次會用到的code給複製回來，這段code中會需要修改「PROJECT_ID、DATASET_ID、TABLE_ID」這三者是屬於BigQuery的資料；然後還有「sheetId、sheetName」這個則是目前正在使用的google sheet資料。

點擊複製即可把段code給複製下來，記得再依上述修改資料

將code給複製到App Script中後再修改上述的變數，完成後會是長成下圖的樣子

接者我們剄要添加「BigQuery」服務到這個App Script中

點擊「+」去新增服務。

緊接著在服務欄中新增「BigQuery」此服務

點擊「新增」。

確定一切都沒問題之後，就可以按下執行

在開姞執行後可以看一下下方的狀態log指示

如果沒有錯誤訊息的話，就應該會看到此訊息：「執行完畢」。

此時就可以回到google sheet上名為「DemoData」的google sheet

所以有資料就可以在google sheet上被更新了。

即時更新(每分鐘)

前面有提到，如果我們有需要更為即時的資料，例如更新時間為每分鐘，那我們要如何作？首先一樣要先回到App Script的編輯介面中，在左列選單中有一個「觸發條件」，請選擇他。

接著在觸發條件介面中的右下角選擇「新增觸發條件」

然後「選取活動來源」選擇「時間驅動」。

點選「選取時間型觸發條件類型」選擇「分鐘」

那麼就可以最快可到每分鐘更新唷！

結論

透過 Google Apps Script 直接查詢 BigQuery 並搭配時間觸發器，我們成功實現了 GA4 資料的即時更新。相較於使用 BigQuery 連接器受限於 1 小時的最小更新間隔，Apps Script 的方式讓我們能夠將更新頻率提升至 每分鐘，確保報表數據更加即時。

這種方式的優勢在於：

1. 更快的數據更新：可根據需求自訂更新頻率，不再受限於 BigQuery 連接器的時程限制。
2. 自動化運行：透過 Apps Script 內建的時間驅動觸發器，確保查詢能定期執行，無需手動操作。
3. 靈活的數據處理：除了查詢 GA4 資料外，還可以透過 Apps Script 進一步處理數據，例如格式化結果、篩選特定維度，甚至串接其他 API 進行進一步分析。

透過這個方法，我們能夠在 Google Sheets 中打造一個 幾乎即時同步 GA4 數據 的報表，讓決策者能夠根據最新數據做出更快、更精確的決策。

結論與注意事項

透過 Google Apps Script 直接查詢 BigQuery 並搭配時間觸發器，我們成功實現了 GA4 資料的即時更新。相較於使用 BigQuery 連接器受限於 1 小時的最小更新間隔，Apps Script 的方式讓我們能夠將更新頻率提升至 每分鐘，確保報表數據更加即時；然而，在實作此方法時，還有幾點需要特別注意：

1. BigQuery 查詢成本：
   BigQuery 的查詢是依據處理的資料量計費，查詢頻率越高、查詢的資料量越大，成本也會隨之增加。因此，建議：

   • 僅查詢必要的欄位與範圍，減少不必要的資料處理量。
   • 使用資料快取（如將查詢結果存入 Google Sheets，再從 Sheets 讀取資料，而非每次都重新查詢 BigQuery）。
   • 調整查詢頻率，評估是否真的需要每分鐘更新，或可降低至 5 分鐘、10 分鐘以減少成本。

2. Apps Script 執行時間限制：

   • Apps Script 的單次執行時間上限為 6 分鐘，如果查詢的數據量過大，可能會導致執行超時。
   • 若查詢執行時間較長，可考慮：
     • 限制返回的數據筆數（如 LIMIT）。
     • 使用分區表（Partitioned Tables），避免查詢整個 GA4 數據庫。
     • 改用排程批次查詢，將結果存入 BigQuery 的快取表，再從快取表讀取數據。

3. Apps Script 觸發器配額：

   • Apps Script 的時間驅動觸發器（Triggers）有每日執行次數的限制：
     • 個人帳戶：每天最多 90 次（約每 16 分鐘一次）。
     • Google Workspace 商業帳戶：每天最多 20,000 次（允許每分鐘一次）。
   • 如果遇到觸發器達到配額，可以：
     • 調整查詢頻率（如改為每 5 分鐘更新）。
     • 將不同查詢拆分到不同的 Apps Script 專案，以避免單一專案超過限制。

透過這些最佳實踐，我們可以在保持高效數據更新的同時，確保成本可控、查詢穩定執行，並充分發揮 Google Sheets + BigQuery 的強大分析能力。

---