【Google教學】試算表的query函數中matches與contains的差異

最近使用query函數想要做出類似sql語法的like或是in的功能，用chatgpt找到2個解法。

使用場景建議：

如果您的篩選條件較為簡單，例如只需要檢查某列是否包含一段文字，建議使用 CONTAINS。

如果您需要更精確或複雜的匹配條件（如多個關鍵字、特定格式），建議使用 MATCHES。

我用之前的英文題庫做給大家看。

=QUERY(A:C,"select * where C contains  'act' ")

=QUERY(A:C,"select * where  A MATCHES '(2|3)' ")

=QUERY(A:C,"select * where C MATCHES  '(act|aim)' ")

00:00 1.說明

01:01 2.MATCHES功能

01:55 3.contains功能

02:26 4.兩者差異

02:59 5.contains實作

03:51 6.MATCHES實作

05:09 7.MATCHES跟join配合

英文單字測驗

https://docs.google.com/spreadsheets/d/1KMUUBzi-4sbW2Qrzeoc75PetNbNogJXRoSurz2ChvZM/copy

GoogleAI整理後的大綱

1. 開頭 (0:00 - 0:09)

   • 自我介紹 (彰化一整天的Blog站長)

   • 主題說明：介紹 Google Sheets QUERY 函數中 MATCHES 和 CONTAINS 的不同。

2. 動機與背景 (0:09 - 0:20)

   • 近期使用 QUERY 時，想實現類似 SQL 語法中的 LIKE (模糊比對) 或 IN (多重條件) 的功能。

   • 透過 ChatGPT 找到兩種解法：CONTAINS 和 MATCHES。

3. 使用場景建議 (ChatGPT 建議) (0:20 - 0:41)

   • CONTAINS：

     • 適用時機：篩選條件較簡單，例如只需檢查某欄位是否 包含 一段文字。

     • 範例：QUERY(A:C,"select * where C contains 'act'", )

   • MATCHES：

     • 適用時機：需要更精確或複雜的匹配條件，例如使用多個關鍵字 (OR 條件)、特定格式、正則表達式。

     • 範例：QUERY(A:C,"select * where A MATCHES '(2|3)'", ) (數字匹配)

     • 範例：QUERY(A:C,"select * where C MATCHES '(act|aim)'", ) (文字 OR 匹配)

4. 理論說明 (參考 ChatGPT) (0:44 - 2:28)

   •  詳解：

     • 作用：使用「正則表達式 (Regular Expression)」進行匹配。

     • 功能：可處理更複雜的模式，匹配多個字母、數字、特定格式。

     • 特點：

       • 支援正則表達式語法。

       • 可用 | (管道符號) 實現 OR 條件 (例如 act|aim)。

       • 適合處理更靈活和高級的篩選需求。

     • 範例說明 (act|aim, act.*)

   •  詳解：

     • 作用：用作簡單的「子字串」匹配。

     • 功能：檢查某列儲存格是否 包含 指定的子字串。

     • 特點：

       • 僅檢查是否存在特定文字。

       • 不支援正則表達式。

       • 更直觀，適合處理簡單的篩選條件。

     • 範例說明 (act)

   • 主要差異總結 (表格呈現)：比較特性、靈活性、用途。

5. 實作演示 (Google Sheets) (2:52 - 6:46)

   •  實作 (3:26)

     • 公式：=QUERY(A:C,"select * where C contains 'act'", 0)

     • 結果分析：會找出所有 包含 "act" 的字串 (如 act, active, actor, actress)。

   •  實作 (數字 OR) (4:01)

     • 公式：=QUERY(A:C,"select * where A MATCHES '(2|3)'", 1) (影片中僅說明，未直接演示此數字範例)

     • 結果分析：找出 A 欄完全等於 2 或 3 的資料。

   •  實作 (文字 OR) (4:27)

     • 公式：=QUERY(A:C,"select * where C MATCHES '(act|aim)'", 0)

     • 結果分析：只會找出 完全符合 "act" 或 "aim" 的資料。

     • 強調 MATCHES 預設是完全匹配。

   •  進階應用 (搭配  (5:07)

     • 目的：讓篩選條件可以來自儲存格範圍，方便修改。

     • 方法：

       • 使用 JOIN("|", G1:G2) 將儲存格內容以 | 符號串接起來 (例如產生 allow|aim)。

       • 將串接結果用 & 符號嵌入 MATCHES 的條件字串中。

     • 公式：=QUERY(A:C,"select * where C MATCHES '(" & JOIN("|",G1:G2) & ")'", 0)

     • 優點：可動態更改 G1, G2 儲存格內容來改變篩選條件。

6. 結語 (6:46 - 6:49)

   • 總結本次教學內容。

   • 感謝觀看。

【Google教學】如何使用chatgpt自動產生簽到表程式

有網友問到，他要用表單產生簽到表，與會人員有分校內及校外，選擇校外時要填寫[服務單位名稱][職稱][簽到人員姓名]，選校內只要出現[簽到人員姓名]，使用表單時要用區段來解決，問說可不可以在同一頁中顯示，這時候就只能自己寫程式來解決，若您不會寫程式可以透過AI來幫您自動產生程式碼，底下就來說明要如何使用。

1. HTML（前端介面）

2. Google Apps Script（後端處理）

3. 部署

4. 試算表格式

用javascript寫一個簽到表，欄位為[與會人員]下拉可以選擇[校外人員]及[校內人員],點[校外人員],會出現[服務單位名稱][職稱][簽到人員姓名],點[校內人員]只要出現[簽到人員姓名],送出後會將資料寫到試算表.

簽到表範例(請建立複本再使用)

https://docs.google.com/spreadsheets/d/18A1BTslBdW0qR-apBlGtVo3lsP8p-c4wNOktIAahXk4/copy

chatgtp網址

https://chatgpt.com/

1. HTML（前端介面）

2. Google Apps Script（後端處理）

3. 部署

1. 在 Apps Script 中將專案部署為網頁應用程式（Deploy > New deployment > Web app）。
2. 設定授權，確保應用程式可以讀寫 Google 試算表。
3. 獲取部署的 URL，將其分享給使用者。

4. 試算表格式

建立一個名稱為 簽到表 的試算表，欄位為：

簽到時間	與會人員類型	服務單位名稱	職稱	簽到人員姓名

功能解說

1. 動態顯示欄位：根據下拉選單選擇「校外人員」或「校內人員」，動態顯示對應的輸入框。
2. 資料保存：提交後，使用 Apps Script 將資料寫入 Google 試算表。
3. 即時反應：表單透過 JavaScript 進行檢查和操作。

GoogleAI整理後大綱

1. 問題描述與需求分析

   • 背景： 網友詢問如何製作簽到表單，需區分「校內人員」與「校外人員」。

   • 條件式欄位需求：

     • 選擇「校外人員」：需額外填寫「服務單位名稱」、「職稱」。

     • 選擇「校內人員」：只需填寫「簽到人員姓名」。

     • 兩者皆需填寫「簽到人員姓名」。

   • 挑戰： 希望在同一頁面根據選擇動態顯示/隱藏欄位，避免使用 Google 表單「區段」功能導致的分頁問題。

2. 解決方案：使用 ChatGPT 產生自訂程式碼

   • 標準 Google 表單無法在同頁面動態顯示欄位，需撰寫程式。

   • 若不熟悉程式，可透過 AI (ChatGPT) 協助產生。

   • 整體架構包含：

     • HTML (前端介面)

     • JavaScript (前端動態邏輯)

     • Google Apps Script (後端處理)

     • Google Sheets (資料儲存)

3. 實作步驟

   • A. 向 ChatGPT 提出需求 (Prompt)

     • 明確指令：用 javascript 寫一個簽到表

     • 定義欄位與類型：欄位為 [與會人員] 下拉可以選擇 [校外人員] 及 [校內人員]

     • 描述條件邏輯：

       • 點[校外人員]會出現[服務單位名稱][職稱][簽到人員姓名]

       • 點[校內人員]只要出現[簽到人員姓名]

     • 指定資料流向：送出後會將資料寫到試算表

     • 重點： 提示語需盡量清晰、完整描述需求與條件。

   • B. 設置 Google Apps Script

     • 建立試算表： 建立一個新的 Google Sheet 作為資料儲存目的地。

     • 設定工作表與標頭：

       • 將第一個工作表命名為「簽到表」(或 ChatGPT 程式碼中指定的名稱)。

       • 在第一列設定欄位標頭 (例如：簽到時間、與會人員類型、服務單位名稱、職稱、簽到人員姓名)。注意欄位順序需與 Apps Script 程式碼寫入的順序一致。

     • 開啟指令碼編輯器： 在試算表中，點選「擴充功能」>「Apps Script」。

     • 貼上後端程式碼 (.gs)：

       • 將 ChatGPT 產生的 Google Apps Script (後端處理) 程式碼，複製並貼到預設的 程式碼.gs 檔案中。

       • 此程式碼通常包含 doGet() (用於載入 HTML 頁面) 和 saveAttendance() (或其他類似名稱，用於接收資料並寫入試算表) 兩個主要函數。

       • 確認 getSheetByName("簽到表") 中的工作表名稱與實際名稱相符。

     • 建立並貼上前端程式碼 (Index.html)：

       • 在 Apps Script 編輯器中，點選「+」>「HTML」，檔案名稱輸入 Index (需與 doGet 函數中 HtmlService.createHtmlOutputFromFile('Index') 指定的名稱一致)。

       • 將 ChatGPT 產生的 HTML (包含 JavaScript) 程式碼，複製並貼到 Index.html 檔案中。

     • 儲存： 儲存 .gs 和 .html 檔案。可為專案命名 (例如：20250104簽到表)。

   • C. 部署為網頁應用程式 (Web App)

     • 首次部署：

       • 點擊右上角「部署」>「新增部署」。

       • 選擇類型 (齒輪圖示)：點選「網頁應用程式」。

       • 設定：

         • 說明 (選填)。

         • 執行身分：選擇「我」。

         • 誰可以存取：選擇「任何人」(非常重要，否則他人無法使用)。

       • 點擊「部署」。

     • 授權：

       • 點擊「授予存取權」。

       • 選擇自己的 Google 帳戶。

       • 點擊「進階」>「前往『專案名稱』(不安全)」。

       • 點擊「允許」。

     • 取得網址： 複製產生的「網頁應用程式網址」。此網址即為簽到表單的連結。

   • D. 更新前端程式碼中的 URL

     • 回到 Apps Script 編輯器中的 Index.html 檔案。

     • 找到 JavaScript 中 fetch() 函數的部分 (通常會標註 YOUR_GOOGLE_APPS_SCRIPT_WEB_APP_URL 或類似提示)。

     • 將步驟 C 複製的「網頁應用程式網址」貼到 fetch() 的引號內，取代預留位置。

     • 儲存 。

   • E. 重新部署 (程式碼修改後必要步驟)

     • 重要： 只要修改過任何程式碼 (.gs 或 .html)，都必須重新部署才能讓公開的網頁應用程式生效。

     • 點擊「部署」>「管理部署」。

     • 選擇要更新的部署項目，點擊編輯圖示 (鉛筆)。

     • 版本：選擇「新版本」。

     • 點擊「部署」。

4. 測試與驗證

   • 開啟部署後的「網頁應用程式網址」。

   • 測試下拉選單：

     • 選擇「校內人員」，確認只顯示「簽到人員姓名」欄位。

     • 選擇「校外人員」，確認顯示「服務單位名稱」、「職稱」、「簽到人員姓名」欄位。

   • 填寫資料並送出。

   • 檢查 Google 試算表「簽到表」工作表，確認資料已正確寫入對應欄位。

5. 補充說明

   • 每次修改程式碼後，務必「儲存」並「重新部署」(選擇新版本)。

   • 前端介面 (HTML/CSS) 可以自行美化。

   • ChatGPT 產生的程式碼細節每次可能略有不同，但基本架構與原理相似。