2025年3月30日 星期日

【Google教學】使用正規表示法抓取網頁含有下拉選項預設值的表格(使用chatgpt)



這是網友在discord中發問的問題,因為表格的資料含有下拉選項,沒有辦法用複製貼上的方式,將資料複製到試算表中,所以我就將這個問題問三個AI的軟體(chatgpt、coplit、aistudio),第一次的解答只有抓到下拉選項的預設值,貼上原始碼的HTML跟它說要抓取的結構時,回傳的程式碼就會是接近想要的答案。


問題說明

詢問AI

實作

修正除錯


瞭解正規表示法


貼結果圖請AI整理


使用正規表示法抓取網頁下拉選項預設值

https://docs.google.com/spreadsheets/d/1EGdQTj5uJk5IKfbWfzA_0qX_OE9I9vDKku1mfbjejYM/copy


AI整理後

影片重點整理:

  1. 主題: 使用正規表示法,抓取含下拉選單預設值的表格。

  2. 背景: 源於 Discord 上的網友提問,因無法複製貼上含下拉選單表格資料。

  3. 實驗:

    • 使用三個 AI 工具 (ChatGPT、Copilot、AI Studio) 協助。

    • 將資料匯入試算表。

  4. 要點:

    • 貼上原始碼 HTML 有助於 AI 抓取結構。

    • 程式碼名稱不能與預設相同,否則會發生錯誤。

    • 每次使用請檢查是否要授權。

    • 注意 Sheet 名稱設定。

  5. 結論:

    • 若提問方式錯誤,結果也會不正確。

    • 需要與 AI 工具溝通清楚。

此外,影片有程式碼的操作演示,詳細內容請參考影片。

2025年3月23日 星期日

【Google教學】如何申請LineBot並查詢群組id跟送訊息



之前寫的LineBot查詢群組id,經網友回報我產生的跟它自己用程式產生的id是不相同的,所以要使用Line群組id就要自己寫查詢id的程式才能使用,若是個人要通報用可以到line官網查到個人lineid即可,每個人在不同人群組的lineid都是不相同的,只有該群組的line機器人可以對該群組產生的lineid送訊息,若群組有5人,傳送一次就是5則訊息,免費每個月可以傳送200則,剩餘則數可以上官網查詢(非即時)。


PS:一個群組只能有一個LineBot機器人


--個人--

如何申請lineBot機器人

取得LineToke

取得LineId

--群組--

試算表發佈網頁應用程式/取得網址

啟用webhook/貼上網址


--如何查看錯誤--


LineBot查群組ID跟送訊息

https://docs.google.com/spreadsheets/d/1U0BeBLyZMpTuv26iWo_UrYDl3Xz1DlnBCptncSEThGk/copy




2025年3月15日 星期六

【Google教學】如何用GoogleAppsScript爬取網頁看youtube的所有留言(使用chatgpt的作法)

 


有些人會在youtube影片的留言來抽獎的作法,那我們怎麼去把某一篇影片的留言都抓到google試算表,我們把這個問題拿去問AI(chatgpt),它提供我們程式碼及作法。


1.申請YouTube Data API 來獲取留言

2.查看要查詢的youtube影片ID

3.修改程式碼並執行



程式碼:

function getYouTubeComments() {

  var apiKey = "YOUR_API_KEY";  // 請填入你的 YouTube API Key

  var videoId = "YOUR_VIDEO_ID"; // 影片 ID

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  sheet.clear(); // 清空試算表

  sheet.appendRow(["作者", "留言", "發佈時間 (台灣)", "喜歡數"]); // 更新標題


  var nextPageToken = "";

  var maxResults = 100;


  do {

    var url = "https://www.googleapis.com/youtube/v3/commentThreads?part=snippet&videoId=" 

              + videoId + "&key=" + apiKey + "&maxResults=" + maxResults + "&pageToken=" + nextPageToken;

    

    var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});

    var json = JSON.parse(response.getContentText());


    if (json.items) {

      json.items.forEach(function(item) {

        var snippet = item.snippet.topLevelComment.snippet;

        var author = snippet.authorDisplayName;

        var comment = snippet.textDisplay;

        var publishedAt = snippet.publishedAt; // 原始 UTC 時間

        var likeCount = snippet.likeCount;


        // 轉換 UTC 時間為台灣時間 (UTC+8)

        var taiwanTime = convertToTaiwanTime(publishedAt);


        sheet.appendRow([author, comment, taiwanTime, likeCount]);

      });

    }


    nextPageToken = json.nextPageToken || "";

  } while (nextPageToken);

}


// 轉換 UTC 時間到台灣時間 (UTC+8)

function convertToTaiwanTime(utcTime) {

  var date = new Date(utcTime);  // 解析 ISO 時間字串

  date.setHours(date.getHours() + 8); // 加 8 小時變成台灣時間

  return date.toISOString().replace("T", " ").split(".")[0]; // 格式化輸出 YYYY-MM-DD HH:mm:ss

}



2025年3月8日 星期六

【excel教學】如何使用PowerQuery將一筆合併後欄位還原成多筆(使用chatgpt的作法)


早期沒有PowerQuery時,我有寫一個快速分割相同資料合併後的欄位的excel工具,現

在excel 2016後(含)可以使用PowerQuery就可以做到這個功能這是我用圖片問Chatgpt

要如何解決,它給我2個解答,我今天就來操作如何使用PowerQuery來做操作。



方法 1:使用 Power Query(適合無程式背景的使用者)

步驟

選取資料(A1:C9)。

點擊「資料」→「從表格/範圍」(會自動建立表格)。

拆分「計數」欄位:

選取「計數」欄位,點擊「轉換」→「拆分欄位」→「以分隔符號」。

選擇 逗號(,) 為分隔符號,然後按「確定」。

轉換成縱向表格:

選取 所有拆分後的欄位(計數.1、計數.2...)。

點擊「轉換」→「取消透視欄」。

重新命名欄位:

「屬性」欄改為「訂單編號」。

「值」欄改為「訂單號」。

關閉並載入:

點擊「關閉並載入」將結果匯入 Excel。


【Excel工具教學】在Excel中快速分割相同資料合併後的欄位

https://www.youtube.com/watch?v=JSVY6FqIeKM


範例檔案下載:

https://drive.google.com/file/d/167hIW3W6fB4Y25AO4nuB-icSgs6EmSut/view

PowerQuery合併相同欄位.xlsx


GoogleAI整理後的大綱



I. 開場與主題介紹 (0:00 - 0:16)

* 講者自我介紹:彰化一整天的 Blog 站長。

* 說明今日主題:使用 Excel 的 Power Query 功能,將單一欄位中以分隔符號合併的多筆資料,拆分成多列資料。

* 提及此方法是參考 ChatGPT 提供的解決方案。


II. 背景說明 (0:17 - 0:31)

* 早期方法:在沒有 Power Query 時,講者曾寫過 Excel 工具 (VBA) 來處理類似問題。

* 現況:Excel 2016 及之後版本內建 Power Query,可以直接達成此功能。


III. ChatGPT 解決方案來源 (0:32 - 1:18)

* 問題提出:講者使用圖片向 ChatGPT 詢問如何將左邊(合併欄位)的表格轉換成右邊(拆分欄位成多列)的格式。

* ChatGPT 回覆:提供了兩種方法:

1. Power Query (適合無程式背景使用者)。

2. VBA (自動化轉換)。

* 本次教學重點:著重於 Power Query 的操作方法。


IV. Power Query 操作示範 (1:18 - 5:34)

* 準備資料 (1:18 - 1:48)

* 從範例連結複製資料。

* 貼到新的 Excel 工作表中。

* 載入 Power Query (1:48 - 2:36)

* 選取資料範圍 (A1:C9)。

* 點選 資料 -> 從表格/範圍。

* 建立表格對話框:勾選 我的表格有標題,按 確定。

* 進入 Power Query 編輯器。

* 分割欄位 (2:36 - 3:14)

* 選取要分割的 計數 欄位。

* 點選 常用 -> 分割資料行 -> 依分隔符號。

* 確認分隔符號為 逗號,按 確定。 (欄位被分割成 計數.1, 計數.2, 計數.3...)

* 取消樞紐分析 (Unpivot) (3:14 - 4:04)

* 選取所有分割後產生的新欄位 (計數.1, 計數.2, 計數.3...)。

* 點選 轉換 -> 取消樞紐分析資料行。 (產生 屬性 和 值 兩個新欄位)

* 重新命名與整理欄位 (4:04 - 4:58)

* 移除 屬性 欄位 (此範例中不需要)。

* 將 值 欄位重新命名為 訂單編號。

* 關閉並載入 (4:58 - 5:34)

* 點選 常用 -> 關閉並載入。

* 結果會載入到新的 Excel 工作表中,呈現拆分後的格式。


V. 提及其他方法 (5:34 - 8:18)

* VBA 方法 (5:40 - 6:23)

* 簡述 ChatGPT 提供的 VBA 步驟 (開啟 VBA 編輯器、插入模組、貼上程式碼、執行)。

* 說明 Power Query 較直覺,VBA 較方便自動化。

* 講者舊工具 (6:24 - 8:16)

* 提及講者早期使用 VBA 製作的 Excel 工具,並提供影片連結參考。

* 展示舊工具的操作方式(選取欄位、指定分隔符號、指定分割欄位)。


2025年1月11日 星期六

【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|aimact.*)

    •  詳解:

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

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

      • 特點:

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

        • 不支援正則表達式。

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

      • 範例說明 (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)

    • 總結本次教學內容。

    • 感謝觀看。

2025年1月4日 星期六

【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 產生的程式碼細節每次可能略有不同,但基本架構與原理相似。

【Google教學】使用正規表示法抓取網頁含有下拉選項預設值的表格(使用chatgpt)

這是網友在discord中發問的問題,因為表格的資料含有下拉選項,沒有辦法用複製貼上的方式,將資料複製到試算表中,所以我就將這個問題問三個AI的軟體(chatgpt、coplit、aistudio),第一次的解答只有抓到下拉選項的預設值,貼上原始碼的HTML跟它說要抓取的結構時,...