從試算表到網頁：用 Google Apps Script 呈現動態數據表格

By徐培鈞
LinkedIn

更新日期： 2024 年 4 月 11 日

現在的時代，能夠把數據清楚地展示出來是很重要的。

不論是工作上的報表、學業上的研究，還是日常生活中的各種數據，如果能夠把這些數據用一種直觀的方式呈現，那麼理解和分享這些信息就會容易得多。

在這篇文章中，我們就要學習如何把試算表（ Google Sheets ）中的數據，以網頁形式展示出來。

以下為參考試算表資料：

「網頁數據展示」參考試算表連結

我們會用到 Google Apps Script，它是一種基於 JavaScript 的雲端程式語言，可以讓我們執行在 Google 雲端平台上做各類軟體服務。

本文會從頭到尾指導你，如何在 Google Sheets 裡準備數據，到寫 Google Apps Script 和 HTML 代碼，最後把它們放到一個網頁上。

讓我們開始吧！

事前準備

在我們開始之前，有幾件事情需要準備好。這個步驟會幫助我們確保後續的工作能夠順利進行。

需要的工具和技術

首先，讓我們確認一下需要用到的工具和技術：

  1. Google Sheets：我們的數據來源，你會需要一個 Google 帳號來創建和存取 Google Sheets。
  2. Google Apps Script：用於從 Google Sheets 讀取數據並與網頁互動的工具。它是基於 JavaScript，但是運行在 Google 的雲端。
  3. 基本的 HTML 和 JavaScript 知識：用於創建和控制網頁內容。

設定 Google Sheets

首先，我們需要一個包含數據的 Google Sheets 試算表。這裡有一個簡單的步驟指導：

  1. 登入你的 Google 帳號，打開 Google Sheets。
  2. 創建一個新的試算表，或者選擇一個已經存在的試算表，確保它包含了你想要在網頁上展示的數據。
  3. 為了方便起見，給你的試算表取一個容易記住的名字，比如「網頁數據展示」。

這樣我們就有了數據的來源。接下來，我們會在 Google Apps Script 中使用這些數據。

開始使用 Google Apps Script

現在來設定 Google Apps Script 的環境：

  1. 在你的 Google Sheets 試算表界面，點擊「擴充功能」菜單，選擇「Apps Script」。
  2. 這會打開一個新的標籤，裡面有一個空白的腳本文件。你可以先簡單熟悉一下界面。
  3. 為這個腳本文件取一個名字，比如「SheetsToWeb」。

完成這些步驟後，我們就準備好開始寫代碼，將試算表的數據帶到網頁上了。

編寫 Apps Script 程式碼讀取試算表數據

接下來的重點是利用 Google Apps Script 從我們的 Google Sheets 試算表中讀取數據。我們將編寫一個函數來完成這項任務，並確保我們可以獲取並處理所需的數據。

步驟 1：訪問試算表

首先，我們需要獲取到試算表的參考。這可以通過以下方式實現：

function getSheetData() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let data = sheet.getDataRange().getValues();
  return data;
}

這段代碼做了以下幾件事情：

  1. 使用 SpreadsheetApp.getActiveSpreadsheet() 獲取當前活動的試算表。
  2. 使用 .getActiveSheet() 獲得該試算表的活動工作表（也就是你目前正在查看的工作表）。
  3. .getDataRange() 獲得工作表中所有「有數據」（從左上角 A1 作為起點）的區域，.getValues() 則將這些區域的數據以「二維陣列」的形式返回。

getValues 方法是 Google Apps Script 中的一個功能，用於從 Google Sheets 的一個範圍（Range）中獲取其包含的數據。

這個方法的主要用途，是從一個指定的單元格範圍中提取數據，讓這些數據可以在腳本中進一步處理。

舉個例子，假設你有一個範圍涵蓋了試算表的 A1:C3 區域，這個區域如下所示：

| A     | B     | C     |
|-------|-------|-------|
| 標題一 | 標題二 | 標題三 |
| 4     | 5     | 6     |
| 7     | 8     | 9     |

當你對這個範圍使用 getValues 方法時，會得到以下的二維數組：

[
  [標題一, 標題二, 標題三],
  [4, 5, 6],
  [7, 8, 9]
]

步驟 2：處理數據

現在我們已經可以從試算表獲取數據了，下一步是進行一些基本的數據處理。

假設我們的數據第一行是「標題行」，我們希望跳過它，並針對時間欄位進行格式調整：


function getProcessedData() {
    // 調用 getSheetData 函數獲取原始數據，並將結果存儲在變量 rawData 中。
    let rawData = getSheetData();

    // 使用 slice 方法創建一個新陣列，這個新陣列將去除原數據中的第一行（標題行）。
    let processedData = rawData.slice(1);
  
    // 使用 map 迴圈方法處理 processedData 中的每一行（每個 row 陣列元素）。
    processedData = processedData.map(function(row){
      // 檢查當前行的第一個元素（row[0]）是否為 Date 類型的對象。
      if(row[0] instanceof Date){
        // 如果是，則使用 Utilities.formatDate 函數將日期格式化為 "yyyy-MM-dd" 的格式。
        row[0] = Utilities.formatDate(row[0], "GMT+8", "yyyy-MM-dd")
      }
      // 將處理（可能已經格式化）後的行返回到新數組中。
      return row;
    })
  
    // 返回處理後的數據。
    return processedData;
}

這段代碼首先調用步驟一創建好的函數：getSheetData 獲取原始數據，然後使用 .slice(1) 方法去除陣列的第一個元素（標題行），同時修正時間欄位的格式，只保留包含實際數據的部分。

補充：App Script 中的「時間」格式

由於 Google Apps Script 使用 JavaScript 的 Date 物件來處理日期和時間

因此將試算表數據導入 App Script 之前，需要確保試算表中時間數據格式與 App Script 兼容。

Google Apps Script 提供了 Utilities 服務，其中包括了一個 formatDate 函數，可以用來將 Date 對象格式化為特定的字符串格式。例如：

let formattedDate = Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss");

步驟 3：測試和驗證

為了確保一切按預期運行，我們可以簡單地測試一下我們的函數：

function testGetData() {
  let data = getProcessedData();
  Logger.log(data);
}

執行 testGetData 函數，然後在 Apps Script 下方彈出的「日誌」面板中檢查輸出，以確保我們獲取的數據是正確的。

現在，我們已經成功從 Google Sheets 獲取了所需的數據，並且對其進行了基本處理。

當然可以，這裡是「設計網頁前端界面」部分的內容：

設計網頁前端界面

現在我們已經從 Google Sheets 獲取了所需的數據，接下來的步驟是創建一個網頁前端界面，用以展示這些數據。

我們將使用 HTML 和一些基本的 CSS 來設計這個界面。

創建 HTML 結構

首先，我們需要創建一個名為「網頁」的HTML 文件，來定義網頁的結構。

這裡是一個基本的範例：

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta charset="8">
    <title>試算表數據展示</title>
    <style>
        /* 這裡將增添 CSS 代碼 */
    </style>
  </head>
  <body>
    <h1>試算表數據展示</h1>
    <table>
        <thead>
            <tr>
                <th>時間</th>
                <th>開盤價</th>
                <th>最高價</th>
                <th>最低價</th>
                <th>收盤價</th>
                <th>調整收盤價</th>
                <th>交易量</th>
            </tr>
        </thead>
        <tbody id="table-body">
            <!-- 數據將被動態插入這裡 -->
        </tbody>
    </table>
    <script>
        // 這裡將增添 JavaScript 代碼
    </script>
  </body>
</html>

這個 HTML 文件定義了一個包含表頭的表格。數據將被動態插入到 <tbody> 標籤內。

添加 CSS 樣式

為了使我們的表格看起來更加整潔和專業，我們在 <head> 標籤內的 <style> 部分添加了一些基本的 CSS 樣式。這些樣式包括設定表格的寬度、邊框、背景顏色等。

table {
  width: 100%;
  border-collapse: collapse;
}
th, td {
  border: 1px solid black;
  padding: 8px;
  text-align: left;
}
th {
  background-color: #f2f2f2;
}

準備動態添加數據的 JavaScript

最後，我們將使用 JavaScript 來動態地將從 Google Sheets 獲取的數據添加到表格中。這部分的代碼將在後續「動態呈現數據」部分進行詳細說明。

目前為止，我們已經設計了一個基本的網頁前端界面，它將作為我們展示從 Google Sheets 獲取的數據的基礎。

編寫 JavaScript 以動態生成表格

在我們的網頁界面中，現在需要一段 JavaScript 代碼來動態地填充表格，使其顯示從 Google Sheets 獲取的數據。我們會編寫一個函數，當頁面加載時自動執行，從而將數據插入到 HTML 表格中。

功能函數：generateTable

首先，我們需要一個函數來處理數據並生成表格行。在你的 HTML 文件的 <body> 標籤最後，添加以下 JavaScript 代碼：


function generateTable(dataArray){
    // 獲取上述 HTML 文件中 ID 為 "table-body" 的元素：<tbody> 標籤
    let tablebody = document.getElementById("table-body");

    // 對試算表中的每一行資料（rowData）進行迴圈運算
    dataArray.forEach(function(rowData) {
        // 為每一行數據創建一個表格行元素 <tr>
        let row = document.createElement("tr");

        // 對當資料行中的每個儲存格數據（cellData）進行迴圈運算
        rowData.forEach(function(cellData) {
            // 為每個儲存格數據，創建一個表格單元格元素 <td>
            let cell = document.createElement("td");
            // 設置儲存格數據的資料內容放到 <td>　元素中
            cell.textContent = cellData;
            // 將創建的單元格 <td> 添加到當前的表格行 <tr> 中
            row.appendChild(cell);
        });

        // 將創建的表格行 <tr> 添加到表格主體 <tbody> 中
        tablebody.appendChild(row);
    });
}

這段代碼做了以下幾件事情：

  1. 獲取 HTML 表格中的 <tbody> 元素。
  2. 遍歷傳入的 dataArray，對於每一行數據，創建一個 <tr> 元素。
  3. 在每一行內，再遍歷每個單元格的數據，創建 <td> 元素並將數據添加進去。
  4. 最後將每個新創建的行添加到表格的 tbody 中。

補充：appendChild() 方法

appendChild 是 JavaScript 中的一個非常重要的 DOM（Document Object Model）操作方法。

它的主要用途是將一個節點，添加到指定父節點的子節點列表的末尾。

這個方法是用於在 DOM 樹中構建或修改元素結構的常用工具。

當然可以。以下是「從 Apps Script 獲取數據」段落的完整內容：

從 Apps Script 獲取數據

現在我們有了生成表格的函數，接下來需要確定一種從 Google Apps Script 獲取數據的方法。

為此，我們利用 DOM（Document Object Model）中的 DOMContentLoaded 事件。

這個事件確保了我們的 JavaScript 代碼，在整個頁面結構完全加載並解析完畢後才執行，從而保證了所有的 HTML 元素都已經可用。

我們的 JavaScript 代碼如下所示：

document.addEventListener("DOMContentLoaded", function () {
  // 調用 Apps Script 函數並在成功時執行 generateTable
  google.script.run.withSuccessHandler(generateTable).getProcessedData();
});

這段代碼的工作原理是在網頁加載完成後，通過 google.script.run 方法調用 Apps Script 中的 getProcessedData 函數。

此函數從 Google 試算表中獲取數據並返回。一旦數據獲取成功，withSuccessHandler 方法確保 generateTable 函數被調用並且將獲取的數據作為參數傳遞給它。

這種方法的優點在於我們可以動態地將從試算表中提取的數據展示在網頁上。用戶在訪問網頁時，總是能看到最新的數據，這使得我們的網頁更加動態和互動。

整合 Google Apps Script 與 HTML/JavaScript

在前面的步驟中，我們已經設計了網頁前端界面，並編寫了 JavaScript 代碼來動態生成表格。

現在，我們將這些與 Google Apps Script 進行整合，從而實現從試算表到網頁的完整數據流。

創建 Apps Script 函數回傳數據

在你的 Google Apps Script 項目中，我們需要創建專門的函數，來處理 HTTP 請求，並將試算表的數據返回給前端。

這可以藉由 doGet 函數完成，它是 Apps Script 中處理 HTTP GET 請求的標準方法。

以下是我們的 doGet 函數的代碼：

function doGet(e) {
  const tmp = HtmlService.createTemplateFromFile("網頁");
  return tmp.evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

以下是這個過程的具體步驟：

函數定義

我們首先定義一個名為 doGet 的函數。在 Google Apps Script 中，doGet 是一個特殊的函數，用於處理來自客戶端（如瀏覽器）的 GET 請求。

加載 HTML 模板

接下來，我們使用 HtmlService.createTemplateFromFile 方法加載一個 HTML 模板文件。

在這個例子中，該模板文件被命名為「網頁」。這個方法允許我們將 HTML 文件作為一個「HTML 模板」加載，並在其中嵌入 Apps Script 代碼。

返回 HTML 內容

我們調用 evaluate 方法來處理「HTML 模板」並生成最終的 HTML 輸出。

evaluate 方法會解析模板中的 Apps Script 代碼和變量，並產生純 HTML。

設置 XFrame 選項

最後，我們使用 setXFrameOptionsMode 方法，並將其參數設置為 HtmlService.XFrameOptionsMode.ALLOWALL。

這個設置確保了生成的頁面可以被嵌入到其他網頁中（例如嵌入到一個 iframe 元素中）。

補充：setXFrameOptionsMode 方法

setXFrameOptionsMode 方法主要目的，是為了控制一個網頁是否可以被嵌入到其他網頁中。

比如在 <iframe> 元素中。簡單來說，它決定了你的網頁能否被放置在其他網站的框架（frame）或內嵌框架（iframe）中。

這個方法主要有兩種模式：

  1. HtmlService.XFrameOptionsMode.ALLOWALL：這個模式允許你的網頁在任何其他網頁的框架或內嵌框架中顯示。這對於那些需要被嵌入到其他網站中的網頁非常有用，比如在 WordPress 部落格中嵌入一個來自 Google Apps Script 的網頁。
  2. HtmlService.XFrameOptionsMode.DEFAULT：這個模式則限制了你的網頁被嵌入。基本上，它提供了一個安全功能，防止所謂的「clickjacking」攻擊，其中有人可能會在他們的網站中不當地使用你的網頁。

在 Google Apps Script 中設置 Web 應用程式

為了從 Apps Script 向我們的網頁發送數據，我們需要將 Apps Script 設置為 Web 應用。

這樣可以創建一個可以通過 HTTP 請求訪問的 URL，從而允許我們的 JavaScript 代碼從該 URL 獲取數據。

  1. 發布 Web 應用： 在你的 Google Apps Script 編輯器中，選擇「發佈」->「部署為網頁應用」。
  2. 設置權限： 選擇誰可以訪問這個 Web 應用。如果數據不敏感，可以設置為「任何人（匿名）」。
  3. 獲取 URL： 完成部署後，你會獲得一個 URL，這是你的 Web 應用程式的訪問地址。

使用 iframe 嵌入至網頁中

當你的 Google Apps Script 網頁已經準備好並且可以正常運作，最後一步就是將這個網頁嵌入到你的頁面中。

這可以通過 iframe 標籤輕鬆實現。以下是嵌入的具體步驟：

  1. 取得 Apps Script 網頁的 URL： 首先，確保你的 Apps Script 項目已經部署為網頁應用程式，並且設定為可以被任何人（包括匿名訪客）訪問。完成這些步驟後，你會得到一個類似 https://script.google.com/macros/s/AKfycb…/exec 的 URL。
  2. 撰寫 iframe 標籤： 使用獲得的 URL，你可以創建一個 iframe 標籤來嵌入這個網頁。例如：
<iframe src="https://script.google.com/macros/s/AKfycb.../exec" frameborder="0" width="100%" height="500px"></iframe>

在這個示例中，src 屬性設定為你的 Apps Script 網頁 URL，frameborder=”0″ 用於移除框架邊框，width 和 height 則設定 iframe 的尺寸。

  1. 將 iframe 嵌入到網頁文章中： 在 HTML 頁面適當位置粘貼剛才創建的 iframe 標籤。
  2. 預覽和調整： 發布或預覽你的頁面後，你應該能看到嵌入的表格。如果尺寸不合適，可以通過調整 width 和 height 屬性來進行微調。

綜合以上內文，以下內完整的 HTML（前端） 與 App Scripts 代碼（後端）：

// 此為 App Script 代碼

function getSheetData() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let data = sheet.getDataRange().getValues();
  return data;
}

function getProcessedData() {
  let rawData = getSheetData();
  let processedData = rawData.slice(1);

  processedData = processedData.map(function (row) {
    if (row[0] instanceof Date) {
      row[0] = Utilities.formatDate(row[0], "GMT+8", "yyyy-MM-dd")
    }
    return row;
  })

  return processedData;
}

function testGetData() {
  let data = getProcessedData();
  Logger.log(data);
}

function doGet(e) {
  const tmp = HtmlService.createTemplateFromFile("網頁");
  return tmp.evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
<!-- 此為 html 代碼 -->

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <meta charset="8">
  <title>試算表數據展示</title>
  <style>
    table {
      width: 100%;
      border-collapse: collapse;
    }

    th,
    td {
      border: 1px solid black;
      padding: 8px;
      text-align: left;
    }

    th {
      background-color: #f2f2f2;
    }
  </style>
</head>

<body>

  <h1>試算表數據展示</h1>
  <table>
    <thead>
      <tr>
        <th>時間</th>
        <th>開盤價</th>
        <th>最高價</th>
        <th>最低價</th>
        <th>收盤價</th>
        <th>調整收盤價</th>
        <th>交易量</th>
      </tr>
    </thead>
    <tbody id="table-body">
      <!-- 數據將被動態插入這裡 -->
    </tbody>
  </table>

  <script>
    document.addEventListener("DOMContentLoaded", function () {
            google.script.run.withSuccessHandler(generateTable).getProcessedData();
        });

        function generateTable(dataArray) {
            let tablebody = document.getElementById("table-body");
            console.log(tablebody);
            dataArray.forEach(function (rowData) {
                let row = document.createElement("tr");

                rowData.forEach(function (cellData) {
                    let cell = document.createElement("td");
                    cell.textContent = cellData;
                    row.appendChild(cell); // 添加單元格到行中
                });

                tablebody.appendChild(row); // 添加行到表格主體中
            });
        }

  </script>

</body>

</html>

總結

在本文中，我們詳細探討了如何利用 Google Apps Script 將試算表的數據動態呈現在網頁上。

我們開始於基礎設置，逐步介紹了如何使用 Google Apps Script 讀取 Google Sheets 中的數據，如何設計網頁前端界面，以及如何利用 JavaScript 動態生成表格。

這些步驟不僅涵蓋了基本的技術概念，還提供了實際操作的指南。

接著，我們講解了如何將 Google Apps Script 與 HTML 和 JavaScript 進行整合，以及如何進行測試和調試以確保應用的穩定性和可靠性。

希望這篇文章能啟發你探索 Google Apps Script 和網頁開發的無限可能，並幫助你在自己的專案中取得成功。

Similar Posts

快速入門：JavaScript匿名函數(Anonymous function)新手教學

By徐培鈞

JavaScript 是一種熱門的程式語言，「匿名函數」則是其中的一個重要特性。

但對剛入門學習寫程式的人來說，此特色可能會讓人難以理解。

本文希望能幫助你，清晰理解並能夠實際應用JavaScript的匿名函數。

我們將從基礎開始，逐步介紹其在程式中的應用，並透過實例來加深理解。

如何有效利用 onEdit 函數？Google Apps Script 新手完全指南

By徐培鈞

Google Apps Script 的 onEdit 函數，是一種特殊的「觸發器」函數。

當試算表（Google Sheets） 中的儲存格被編輯時，會自動啟動該函數。

本文將介紹 onEdit 函數的基礎知識，包括它是什麼、如何使用，以及一些實際的例子。

此外，我們也會討論一些使用 onEdit 函數時，可能會遇到的常見問題。

現在，讓我們開始深入了解 onEdit 函數吧！